Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
The Trade-off Between SQL Server Security and Performance - Free Webinar
 

Implement Dynamic Data Sources in SQL Server Reporting Services


By:   |   Last Updated: 2016-05-23   |   Comments (2)   |   Related Tips: > 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.

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


Last Updated: 2016-05-23


next webcast button


next tip button



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.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

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

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'.

SPN?†

Thx,

Hiram


Learn more about SQL Server tools