You can have situations in which your report should be executed on multiple databases. The user should be able to select the desired database on which the report should run. In this blog post I will explain how you can do this.
To be able to select to different database you need to make use of a dynamic data source. A data source makes use of a connection string. It is possible to pass the connection string of a data source as an expression. By using an expression, you can make use of parameter values to pass the servername and database name to the connection string. There is only one restriction of a dynamic data source. The data source should be embedded within the report. It can not be implemented with a shared data source. In this blogpost I will use a second database in which I retrieve the available SQL server\databases on which my reports should be executed.
- Open your report
- Add 2 report parameters
- ServerName
- DatabaseName
- Add datasource named: DynamicDataSource. Use the a 'hard coded' connection string. For instance: Data Source=MySQLServer1;Initial Catalog=MyDatabase1.
- Add datasource to the database with all SQL Server\databases. In my example named: Synergy
- Add a embedded dataset to retrieve SQL Server and Database information.
- Configure the available values for the report parameters: ServerName and DatabaseName.
- Add all datasets and report items to your report.
- Test your report using the 'hard coded' connection string.
- If everything works fine, change the 'hard coded' connection string with next expression
="data source=" & Parameters!ServerName.Value & ";initial catalog=" & Parameters!DatabaseName.Value
- Run the report and select a value for the report parameters ServerName and DatabaseName
Enjoy it.
5 comments:
Very nice article & thank you so much André van de Graaf !
Satheesh
One small note,
If you're amending an existing report, please make sure the ServerName/DatabaseName parameters are parameters number 1 & 2 in the list (push them up).
Otherwise on run-time you'll get the error:
An error occurred during local report processing.
An error has occurred during report processing.
Error during processing of the ConnectionString Expression of Data Source 'DynamicDataSource'.
I can get this to work in Visual Studio (VS2013) but it does not work when deployed to the Report Manager (SQL Server 2014). The embedded data source is not recognized:
Cannot create a connection to data source 'Various'. (rsErrorOpeningConnection)
In this example, Syngergy is a static data source. You need the static data source to be able to load/refresh your dataset via the "refresh fields" button. However, make sure you do not forget to set your dataset's data source back to the dynamic one, or else the report's dataset will be stuck using the static data source.
It's a real shame that one cannot always use a dynamic datasource and still be able to refresh the dataset fields. There is no way to specify a default connection string for your dynamic datasource because Visual Studio and Report Builder will not allow you to refresh fields if your connection string is expression based.
"Connection string expressions cannot be evaluated at design time. You must preview or deploy the report to verify the connection."
If this were NOT the case, one would be able to specify a default by using a connection string expression like this:
=IIF(IsNothing(Parameters!ConnectionString.Value), "Data Source=;Initial Catalog=;", Parameters!ConnectionString.Value)
The article offers a comprehensive guide.gwalior apartments
Post a Comment