Most properties in SQL Server Reporting Services can be controlled by expressions with the built-in expression language. For example, you can change the font color of a field based on the value. You’ll notice the “fx” symbol or the choice of “<expression>” as a property choice wherever you can use an expression in place of a hard-coded value.
I’m currently working on some reports where the user needs the ability to change the database or databases dynamically. Surprisingly, the Connection String property of the Data Source is one of those properties that can be configured with an expression (Figure 1).
Figure 1: You can use an expression to configure the connection string.
By using parameters with a list of databases for the user to choose from, the initial catalog property of the database can be set to the value of the parameter (Figure 2).
Figure 2: Configure the connection string with the parameter
The challenge with this solution is that while you are developing the report with a dynamically configured connection string you’ll get an error when you update the data source (Figure 3).
Figure 3: The error message
This problem prevents updates to the data set field list. To work around this, set up a Shared Data Source at the project level. Set up a second data source in the report that references the Shared Data Source. When modifying the data set, use the hard-coded data source. Once development is complete, switch to the dynamic data source and delete the hard-coded one (Figure 4).
Figure 4: Create a connection you will delete when the report is complete
SSRS is a flexible and powerful reporting solution, and once again, it was easy to use the built-in functionality to solve the problem at hand.