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 –

Report Server Project Wizard

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 –

Preview Report

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

Report Parameter Properties

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 –

Dataset Properties

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 –

Report Parameter Properties

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 – clip_image006

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

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

SSRS Connection String Dynamic

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 –

clip_image008

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.


5 comments:

Anonymous said...

I am not able to get this to work with 2008. Does the account need special permissions?

cold aire said...

got error on this

Ritesh said...

Ok . This will work fine when you have to choose only one database at a time.

What about if I have to chose more than 1 databases at a time. It won't work.
DO you have solution for that.

Thanks

Pravinkumar said...

Please tell me the detail requirement of the same so that I can help you out.

Pravin

Roger said...

It's helpful~

Thanks~ :)