Home > SSRS > Dynamic Data Source for SSRS Reports

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.

Advertisements
  1. Atiyeh
    April 24, 2014 at 8:13 am

    can we pass username and password in the expression

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: