Implement Dynamic Data Sources in SQL Server Reporting Services

By: Daniel Farina   |   Updated: 2016-05-23   |   Comments (3)   |   Related: > Reporting Services Data Sources

Problem

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.

Solution

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]
GO

CREATE DATABASE [DBA]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'DBA', FILENAME = N'E:\MSSQL\DBA.mdf' ,
 SIZE = 10MB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'DBA_log', FILENAME = N'E:\MSSQL\DBA_log.ldf' ,
 SIZE = 10240KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

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.

USE DBA
GO

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

Then fill the table with data for your environment.

USE DBA
GO

INSERT INTO dbo.DatabaseServers ( ServerName )
VALUES ( 'ORIOM' );

INSERT INTO dbo.DatabaseServers ( ServerName )
VALUES ( 'PROMETEO' );

INSERT INTO dbo.DatabaseServers ( ServerName )
VALUES ( 'SQLA' );
GO 

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.

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; 

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

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

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.

Now we open the report’s embedded data source properties window and 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"

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.

Next Steps

• You can download the Visual Studio Project and the SQL code used in this example from this link.
• Are you new to Reporting Services? This tutorial will be the best place to start: SQL Server Reporting Services Tutorial.
• You can find more SSRS tips here.
• If you don’t know what Performance Dashboard reports are, take a look at my previous tip SQL Server Management Studio Performance Dashboard Reports Overview.

About the author

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