Dynamic Connection String in SQL Server Reporting Services 2005/2008

In this post we will see how to generate a report using dynamic connection string in SQL Server Reporting Services 2005/2008. The scenario is if we have different data with the same schemas, spread across different database and different SQL servers and people would like to see the data as per their preferences (like area wise), we can use a Dynamic Connection string.

So let’s create a report using SQL Server Business Intelligence Studio 2008 with the name ‘DynamicConnectionReports’ as shown below –

Now follow the wizard to create a simple Customer Records report from the database ‘Northwind’ with the query – ‘Select * from Customer’. I have already demonstrated creating report using wizard in my previous article ‘Designing Basic Report using SSRS 2008’. If you have not read it yet, I will highly recommend you to go through the steps.

Once your report is ready, let’s preview it and the output should look like below –

Now once your report is ready, go to the ‘Report Data’ window and add a parameter with the name ‘Databases’ as shown below –

Click on ‘OK’ button. Let’s create a Dataset which will fetch all ‘Databases’ from our SQL Server instance. To add a new dataset, right click on the data source (I have named it ‘CommonDS’) and click on ‘Add Dataset’. Now let’s configure the Dataset as shown below –

Make sure you will not use the existing Data Source. Create a new connection. Now let’s map the Parameter

‘Databases’ to take the data from ‘AddDatabases’ dataset. Right click on the ‘Databases’ parameter and configure the parameter as shown below –

Click on the ‘OK’ button. Now right click ‘CommonDS’ data source of the report and click on ‘Data Source Properties’ option. It will display the properties window. Remove the existing connection string and click on the ‘Expression’ button –

Now let’s define the connection string as shown below –

="Data Source=Localhost;Database=" & Parameters!Databases.Value

Click on the ‘OK’ button and now ‘Preview’ the report. You will see all the databases in a dropdown Listbox. Choose ‘Northwind’ database and click on the ‘View Report’ button. The report will look like the one shown below –

Now you can use this report from Web Form or Windows Form by passing the parameter value from the application. You can hide the parameter from the report by setting ‘Parameter visibility property’ ‘hidden’. Now the most important part is this dynamic connection string demo will not work with ‘Shared Data Source’.

Also make a note that in this demonstration, we are using one instance of SQL Server and fetching all the databases. You can even make your SQL Server parameter dynamic.

The reports will work fine as long as you are using the same schema for generating the report.