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.