Implement Dynamic Data Sources in SQL Server Reporting Services

By:   |   Comments (3)   |   Related: > Reporting Services Data Sources


You have a SQL Server Reporting Services (SSRS) report that needs to be executed against different SQL Servers. For example, you have sales databases on different servers hosting different countries data and you need to run the same report against each server.  Or you want to run a SQL Server Performance Dashboard reports against different servers. Instead of having to deploy the same report multiple times with just different data sources, in this tip I will show you how you can make the data source dynamic.


When we deploy a report on Reporting Services we have two possibilities regarding the DataSource configuration. Either we choose to use a shared data source or we use an embedded data source in the report. Usually we use a shared data source when we have other reports that use the same connection settings.  If you decide to use a shared data source, the database or server it points to cannot be changed at run time. In contrast, with an embedded data source you can use expressions to modify connection parameters at runtime which we will do in this tip.

Usage Scenario

Imagine you have 50 SQL Server instances in your organization and you want to use the SQL Server Performance Dashboard reports to monitor them remotely by accessing one Reporting Services portal. The first thing that may come to mind is to create one project per server, but that demands a lot of work and even more work when you need to make changes. The best approach, in my opinion is to deploy a single project with a shared data source pointing to a database that contains a list of servers and an embedded data source we can change dynamically.

Implementing a Dynamic Data Source in SSRS

As I stated in the previous paragraph, the first step is to create a database that will hold the information about our infrastructure.

USE [master]

( NAME = N'DBA_log', FILENAME = N'E:\MSSQL\DBA_log.ldf' ,
 SIZE = 10240KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

Now we create a table that will hold the names of our servers. Since in this example we are working with Performance Dashboards reports I wonít add a column for the database name, because the report runs on the same database across servers which is the msdb database.


CREATE TABLE DatabaseServers
      Server_ID INT IDENTITY(1, 1) NOT NULL ,
      ServerName VARCHAR(255)  NOT NULL
      CONSTRAINT PK_DatabaseServers 

Then fill the table with data for your environment.


INSERT INTO dbo.DatabaseServers ( ServerName )

INSERT INTO dbo.DatabaseServers ( ServerName )

INSERT INTO dbo.DatabaseServers ( ServerName )

After completing the previous steps, we open the SQL Server Data Tools and create a new Report Server project in which we will import the Performance Dashboard reports. Then we have to create a shared data source and point it to the database we have created in the previous steps.

Configure the Shared Data Source According to your Environment.

The next step is to create a shared data set using the shared data source with the following query that will output the list of servers we inserted in the table above.

SELECT ServerName
FROM dbo.DatabaseServers 
ORDER BY ServerName; 

Setting up Shared Dataset.

Then on the report dataset, we create a new dataset to point to the shared dataset we have created in the previous step.

Pont the Report Dataset to the Shared Dataset.

After creating the dataset we create a report parameter named ServerName of type text and set it as visible.

Create a new Report Parameter.

On the Available Values page, we select our previously created Shared Dataset as the Dataset and select the ServerName field on both "Value field" and "Label field" combo boxes.

Report Parameter Available Values.

Now we open the reportís embedded data source properties window and click on the Expression button.

Click on the Expression Button.

When the expression editor opens we build the connection string with the ServerName parameter with the following text. Remember that Parameter names are case sensitive.

="Data Source="+ Parameters!ServerName.Value+ ";Initial Catalog=DBA"

Setting up ChartData Dataset.

Running the SSRS Report with a Dynamic Data Source

After completing the previous steps you can deploy the project to the Report Server and when you run the report you will be asked which server you want to execute the report against like on the below image.

Screen Capture of Report Execution.
Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Daniel Farina Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Friday, January 31, 2020 - 9:42:08 AM - Hussain Majed Back To Top (84067)

Your instructions is clear, but what about the procedure that is runs when the report is executed? Shall we write the procedure in the DBA located in the master and the dBName and the servername as parameters sent from the report parameters to the master database to the procedure to guide the procedure where to execute the report from which database?

Tuesday, August 28, 2018 - 3:29:11 PM - Hiram Back To Top (77323)

Wasn't the SPN.

Edited the Paginated Report > Manage > Data Sources > DS_Dynamic (custom), and set to Using the following credentials (Windows) domain\username, password. Instead of As the user viewing the report.

Friday, August 24, 2018 - 6:32:40 PM - Hiram Back To Top (77302)

How did you get the authentication on the rpt web server to work against the remote dynamic target data source server? I keep getting Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.




get free sql tips
agree to terms