Configure Dynamic Data Sources in and SSRS Report

Configuring a dynamic data source, a data source you call at run-time, has many purposes. Say you would like to run a production report against one or multiple test environments or run performance reports against all SQL servers you manage; with dynamic data sources you could pick which server you want to run the report against at run-time without having to modify the report itself.

In our example below we are going to use a table that holds all of the SQL Server names we want to use in the dynamic data source. We'll query this table to generate a drop down list for our server selection, which then builds a dynamic connection string to a particular server. 

So the first step is to create an SSRS report. Create an embedded data source named SetupDataSource source and build your report from the this data source. You won't be switching to the dynamic data source until the very end. If you want to query a table to return a list of server names you will also want to use the SetupDataSource to query the table. For the report content, I would start with something simple, like querying your server wait stats.

Once you have built your report, create a new report parameter named "SQLServerName":

Once you have set the name go to the Available Values tab and click the radio button for "Get values from a query". We already created a data set to query the table with my server names. The table has a single column with server names only. If you don't want to select from a table you could instead manually enter your values under "specify value". In our example we've used "get values from a query", the data set is named SelectServer, the value field is ServerName, and the label field is ServerName:

Next we need to create the dynamic data source--this is an embedded data source. We are going to create a dynamic data source named DynamicDataSource with the type as Microsoft SQL Server:

Now we need to setup the connection string using an expression. Click the expression button to bring up the editor:

The data source is the SQL server name—we get this name from the parameter we created "SQLServerName". The initial catalog is the database, in this case master. Enter the following (pictured above) and the click OK.

="data source=" & Parameters!SQLServerName.Value & ";initial catalog=master"

Now that we have the dynamic data source setup, go back to each of your datasets (for example, the dataset you created for server wait stats) and change the dataset to use the embedded, dynamic data source and click OK. When you do this you will get an alert:

It can't update the field list because the data source is dynamic now. Just click OK to continue. Now go ahead and preview your report—you should see a drop down list with the SQL servers we setup in the SQLServerName report parameter. Select the server you want to run against and click Run Report. You should now see a list of wait stats for the selected server. 

Tip: I always query the SERVERPROPERTY to return the server name so that I know which server I'm connected to, it's an easy way to verify you are on the correct server.