Dynamic Data Source for SSRS Reports

Microsoft SQL Server Reporting Services report data source can be handled dynamically during the execution of the report.

For instance, assume you have a database A with a table X. This database A is deployed in different servers but table X has different recordset in each server.

Now,  we have a requirement to develop a report that should display table X records. But, the restriction is that we have to develop only one report to serve for different data source.

Reporting Services has the option to pass data source string as an expression. This expression could replace the server name with the report parameters. So, you can change the report parameter run-time to see different values from different servers using single report.

Data source string definition for your reference

Data source	Example
SQL Server database on the local server	data source=”(local)”;initial catalog=AdventureWorks
SQL Server database on the local server	data source=”(local)”;initial catalog=AdventureWorks
SQL Server Instancedatabase	Data Source=localhost\MSSQL10_50.InstanceName; Initial Catalog=AdventureWorks
SQL Server Express database	Data Source=localhost\MSSQL10_50.SQLEXPRESS; Initial Catalog=AdventureWorks
SQL Azure Database in the cloud	Data Source=<host>;Initial Catalog=AdventureWorks; Encrypt=True
SQL Server Parallel Data Warehouse	HOST=;database= AdventureWorks; port=
Analysis Services database on the local server	data source=localhost;initial catalog=Adventure Works DW
Report model data source on a report server configured in native mode	Server=http://myreportservername/reportserver; datasource=/models/Adventure Works
Report model data source on a report server configured in SharePoint integrated mode	Server=http://server; datasource=http://server/site/documents/models/Adventure Works.smdl
SQL Server 2000 Analysis Services server	provider=MSOLAP.2;data source=<remote server name>;initial catalog=FoodMart 2000
Oracle server	data source=myserver
SAP NetWeaver BI data source	DataSource=http://mySAPNetWeaverBIServer:8000/sap/bw/xml/soap/xmla
Hyperion Essbase data source	Data Source=http://localhost:13080/aps/XMLA; Initial Catalog=Sample
Teradata data source	data source=<NNN>.<NNN>.<NNN>.<NNN>;
XML data source, Web service	data source=http://adventure-works.com/results.aspx
XML data source, XML document	http://localhost/XML/Customers.xml
XML data source, embedded XML document	Empty

 Note:

a) Data source should be embedded with the report. Because, it can not be implemented with shared data source.

b) You should complete the report development before changing the data source with expression.

Implementation

Step 1: Create report parameter

Step 2: Modify data source with expression given below

=”data source=” & Parameters!ServerName.Value & “;initial catalog=<Your Database Name>“

 

Step 3: Deploy and execute the report

 

To pass credentials with the data source please read this.

Read more about this topic here.

 

Thanks for reading.