SSRS - Set default parameters and datasources based on folder it is deployed to

Question

We're running into a dilemma where we're maintaining the same reports with a simple parameter and datasource change

I'm wondering if there is a way to set a default parameter/datasource based on the folder the report is deployed to.

For example, if the path is in Folder1, set the parameter to X and datasource to X. If it's in Folder2, set the parameter to Y and datasource to Y, etc...

Is this possible in SSRS 2016?

Answer 1

I have used Dynamic Data Sources to analyze data with the same database on different servers (Examples are scenarios like ReportServer databases, or applications that are duplicated for different divisions or locations). You could possibly leverage this technique to set the data source dynamically based upon the folder. You will probably also need to use the Global functions ReportFolder() and ReportServerUrl().

Here is the steps for how you could set a default parameter/datasource based on the folder the report is deployed to.

---

Global functions:

Expression:

="Folder: " & Globals!ReportFolder & ", ReportName: " & Globals!ReportName & ", Url: " & Globals!ReportServerUrl

Create a Dynamic Data Source

Here is a link: mssqltips.com/sqlservertip/4302. Below is the steps from the MSSQLTips article.

1) 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

2) Create a table that will hold the names of our servers. (You might also need to create a table for holding the name of the databases; if the database name changes through the environment).

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

3) 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 

4) In Visual Studio SSRS Project, create a shared data source and point it to the database we have created in the previous steps.

Name: DataSource1
Type: Microsoft SQL Server
Conn String: Data Source=ORIOM;Initial Catalog=DBA

5) 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. (Call it "SharedDataSet").

SELECT ServerName
FROM dbo.DatabaseServers 
ORDER BY ServerName; 

6) On the report dataset, we create a new dataset to point to the shared dataset we have created in the previous step. (Call it "Shared").

7) After creating the dataset we create a report parameter named "ServerName" of type text and set it as visible. Prompt is "Enter Server Name".

8) 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.

9) Open the report’s embedded data source properties window and click on the Expression button. Build the connection string with the ServerName parameter.

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

10) Deploy report to the server. Run the report, and you will be asked which server you want to execute the report against.