Change SSRS data source of report programmatically in server side

Question

Today, for each customer, we deploy same SSRS reports folder and data source folder. The difference between these folders are the name of each folder and the connection string of the data source.

We are using Report Server 2008 R2.

Is it possible to maintain only one reports and data source folder and change programmatically its connection string on server-side before the report been rendered?

If not, Is it something that can be achieved by changing some logic in reports? Today we use "shared data source" option.

Solution 1

This is something we've done in our environment - we maintain one set of reports that can be deployed at any client with their own configuration.

You've got a couple of options here. Since you're using a Shared Data Source this makes things easier as you won't need to define a Data Source for each report.

1. Use the rs.exe utility and a script file

rs.exe at Books Online

This program allows you to create script files (in VB.NET) that can interact with a Report Server Web Service. You create a script file (e.g. Deploy.rss) and call the rs.exe program with various parameters, including any custom ones you define:

rs.exe -i DeployReports.rss -s http://server/ReportServer -v DatabaseInstance="SQL" -v DatabaseName="ReportDB" -v ReportFolder="ClientReports"

So this would call a script DeployReports.rss, connect to http://server/ReportServer, with three user defined parameters which could be used to create a data source and the report folder.

In the scipt file you could have something like this:

Public Sub Main()

    rs.Credentials = System.Net.CredentialCache.DefaultCredentials

    CreateFolder(reportFolder, "Report folder")
    CreateFolder(datasourceFolder, "Data source folder")
    CreateDataSource()

End Sub

Which can then make Web Service calls like:

rs.CreateFolder(folderName, "/", Nothing)

'Define the data source definition.
Dim definition As New DataSourceDefinition()
definition.CredentialRetrieval = CredentialRetrievalEnum.Integrated
definition.ConnectString = "data source=" + DatabaseInstance + ";initial catalog=" + DatabaseName
definition.Enabled = True
definition.EnabledSpecified = True
definition.Extension = "SQL"
definition.ImpersonateUser = False
definition.ImpersonateUserSpecified = True
'Use the default prompt string.
definition.Prompt = Nothing
definition.WindowsCredentials = False

Try

    rs.CreateDataSource(datasource, datasourcePath, False, definition, Nothing)
    Console.WriteLine("Data source {0} created successfully", datasource)

Catch e As Exception

    Console.WriteLine(e.Message)

End Try

You haven't specified what version of Reporting Services you're using, so I'm assuming 2008. Please note that there are multiple endpoints that can be used, depending on SQL Server version. The 2005/2008 end point is deprecated in 2008R2 and above but is still usable. Just something to bear in mind when writing your script.

2. Call the SSRS Web Service through an application

Report Server Web Service overview

The same calls that are made from the script above can be made in any other application, too. So you'd just need to add a reference to a Report Server Web Service through WSDL and you can connect to a remote service and call its methods to deploy reports, data sources, etc.

So ultimately you're connecting to the Report Server Web Service, it's just the medium used that you need to think about.

Using a script is easier to get running as it's just running a program from the command line, but writing your own deployment application will certainly give greater flexibility. I would recommend getting the script going, so you understand the process, then migrate this to a bespoke application if required. Good luck!

Solution 2

You can use an Expression Based Connection String to select the correct database. You can base this on a parameter your application passes in, or the UserId global variable. I do believe you need to configure the unattended execution account for this to work.

Note: be careful about the security implications. Realize that if you would pass sensitive data (e.g. passwords) into a parameter, that (a) it will go over the wire, and (b) will be stored in the execution log tables for reporting services.

Comments

[Eddie Rozenblat]

thanks. i think i didn't described well by problem. i would like to change the data source before rendering the report, depending on client. since my purpose is multi-tenant report system, it need it to be a "single folders" system too, but with dynamic data source. BTW i'm using report server 2008 R2.

[Ian Preston]

Coming back and reading the comments since I posted, this seems like the best candidate so far. It seems like you'll have to go from a shared Data Source to embedded but once that's done you should have the flexibility you require. +1; I think this should be the accepted answer.

[callisto]

The expression based connection string method works well for us. We self-host, but only pass server ip and db name to the datasource using the expression conn string- so no username or password is in the execution log table. PS. SSRS can not use this method in a shared datasource