Atomizer (Expression-based Connection Strings)


One of the new features of SQL Server 2005 Reporting Services is support for expression-based connection strings. Many customers have the same database schema in multiple database or servers. In SQL Server 2000 Reporting Services, it was hard to have a single report that would work against multiple data sources. Below is a sample RDL snippet for a data source with an expression-based connection string which takes the server and database names from report parameters.



<DataSources>
  <DataSource Name=”Northwind”>
    <ConnectionProperties>
      <DataProvider>SQL</DataProvider>
      <ConnectString>=”data source=” &amp; Parameters!ServerName.Value &amp; “;initial catalog=” &amp; Parameters!DBName.Value</ConnectString>
      <IntegratedSecurity>true</IntegratedSecurity>
    </ConnectionProperties>
  </DataSource>
</DataSources>

<ReportParameters>
  <ReportParameter Name=”ServerName”>
    <DataType>String</DataType>
    <Prompt>ServerName</Prompt>
  </ReportParameter>
  <ReportParameter Name=”DBName”>
    <DataType>String</DataType>
    <Prompt>DBName</Prompt>
  </ReportParameter>
</ReportParameters>


One limitation with expression-based connection strings is that you cannot manage them like you can with as literal strings in data sources. You can, however, override the expression in Report Manager or Management Studio.


Comments (55)

  1. Maojie says:

    It don’t work.

  2. Rui says:

    Doesn’t work for me either? Is any other way?

  3. Abhishk says:

    how to do that in reporting services 2000.

  4. Scott says:

    Do you have an example of using this? Or is there a sample site on the net that implements this? I have a number of E-Commerce sites with the same Schema and they use the same reports, but I need to be able to switch the reports on the fly, changing the database they should go against.

    Any Help would be greatly appreciated.

    Scott

    Measure Twice… Cut ONCE!!

  5. Ty Millwee says:

    Some additional information.

    http://msdn2.microsoft.com/en-us/library/ms156450.aspx

    Ty Millwee

  6. William Law says:

    I’ve tried and it works only in VS2005

  7. basem says:

    It works with sql statements but not with stored procedures

    i hope if any body could help

  8. sdoran says:

    I can’t get this to work if my command type should be a stored proc. it keeps reseting to text.  Is this a bug?

  9. Rashmi says:

    Expression based Connectionstring inside my RDL code didn’t work for me.. Please need your help

  10. williaps says:

    Has anyone gotten this to work connecting to Oracle?  I don’t know if this is my exact problem but when I use:

    ="Data Source=" & Parameters!DatabaseSID.Value

    I get the following error:

    Native compiler return value: ‘[BC32017] Comma, ‘)’, or a valid expression continuation expected.’.

  11. Gogi says:

    Emphysema is a chronic respiratory disease where there is over-inflation

  12. Thank you for your site. I have found here much useful information!Very cool design!Added to favorites!! My site: http://titan-tankless-waterheater.blogspot.com

  13. Linda says:

    I’ve just been letting everything pass me by lately. I’ve more or less been doing nothing. Not much going on lately. I can’t be bothered with anything recently.

  14. Mikle says:

    Good site! Well.. i like design!

  15. Besid,Besid says:

    Very good project! <a href= http://www.debtconsolidation.newov.info >debt consolidation</a> <a href= http://www.badcreditloan.newov.info >bad credit loan</a>

  16. Qest,Qest says:

    Very nice resources!<a href= http://www.mortgagerefinancing.newov.info  >mortgage refinancing</a> <a href= http://www.autoinsurancequote.newoz.info >auto insurance quote</a>

  17. Milas says:

    Well…good news, i like your site, Happy new year! )))

  18. Tonik says:

    Well, your site is very good, good news and design…! ))) Happy Christmas! SmilerMan.

  19. Wried says:

    Very nice! I have some sites with news, check this out:

    <a href= http://kreolikko.livejournal.com >Politics news</a>

    <a href= http://icefroggg.livejournal.com >Lastest news</a>

    <a href= http://icyiceman.livejournal.com >Lifestyle news</a>

  20. The Best Catalog.

    <a href=http://healthpiece.info/>Real”>http://healthpiece.info/>Real Catalog</a>[url=http://healthpiece.info/]The Real Catalog[/url]