Dynamic DataSource in SSRS report

I ended 2015 in a good way by passing 70-462 Microsoft certification.

Hello 2016 ! 

I am writing the first blog of 2016. I wish Everyone Happy New Year. May this year bring you joy,peace,good health and prosperous.

I want to blog about how to set up a dynamic datasource in SSRS reports. You might have already known how to set it up. Hope my blog comes in handy when you need to recall how to do it.

In this example, I am creating SSRS Execution Log Report. We have three different datasources (Dev/QA/PROD)- SSRS reportserverdb servers. I have created a report which queries SSRS executionlog3 view from reportserverdb. In my case, I am connecting different datasource one at a time. In order to dynamically connect to each datasource upon choosing a server name from a drop down box, I will need to create a dynamic datasource.

The report below is already created. I want to show you how you can set up dynamic datasource.

Under Data Source, Create a datasource which is named DDS in my case. DDS is my dynamic datasource which will connect to different server upon request (drop down server name choice). 

Open DDS datasource by double clicking it. Choose Embedded connection and open expression (fx)

In expression, enter   ="Data Source=" & Parameters!ServerName.Value

Under credential, choose to use windows authentication

I have two datasets in my report - Serverlist and Executionlog

Under Datasets, Serverlist dataset will get servername from a table called SQLServers. (SQLServers table should be created and populated with data(server names)before this report)

ExecutionLog Dataset has a query which retrieves ssrs executionlog from report server.

I used the below query in Executionlog dataset

use ReportServer

SELECT  

InstanceName,

UserName,

Itempath,

ItemAction,

TimeStart,

TimeEnd,

DateDiff(MILLISECOND,TimeStart,TimeEnd) TimeTakenToggle,

TimeDataRetrieval + TimeProcessing + TimeRendering as [total time_ms],

TimeDataRetrieval TimetoRetrieveAllDataset, 

        TimeProcessing TimeSpentInProcessEngine,

TimeRendering TimeSpentOnReportingService,

ByteCount NoOfBytes,

[RowCount],

AdditionalInfo,

CHARINDEX('\', Instancename) Getposition,

Substring(InstanceName,1,CHARINDEX('\', Instancename)-1) ServerName,

'\\'+Substring(InstanceName,1,CHARINDEX('\', Instancename)-1)+'\e$\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\LogFiles' Logfilelink

FROM ExecutionLog3(nolock)

where convert(varchar(10),timestart,101) >= @DateStart and 

convert(varchar(10),timestart,101) <= @DateEnd

ORDER BY Timestart DESC

When I preview the report, I will have my SSRS reportserverdb server list. I can choose one server and choose datestart and dateend (which I set it up in parameters)

Preview did not give errors. I am going to deploy the report in SSRS reporting service.

After deploying the report to reporting service, I will need to configure permission for this report in reporting service. For example, I am using domain credential MYDOMAIN\SSRSEA and it's password to connect to each servers. MYDOMAIN\SSRSEA account must be added to each SSRS server as db reader in reportserverdb. To use MYDOMAIN\SSRSEA to connect to each server, we will need to configure as below.

In username : MYDOMAIN\SSRSEA and enter it's password accordingly.

NOTE : Check Use as windows credential when connecting to the data source 

Click Apply at the bottom of the page.

Now, you will be able to view the report and it is working!