2

I'm in a situation where multiple "customers" will be running the same report, but against a different database (with the exact same structure).

What I've done so far is created a generic "Deploy" folder, and deployed all reports to it. I then create "customer-specific" folders, and add linked reports to the source folder. However, I don't seem to be able to make the reports use different data sources (there's no option to change it on the linked report). Is there any way to do this?

Alternatively, how could I handle this situation if I can't use different data sources? I want to avoid re-deploying the same report 10 different times to ten different places only to change the datasource.

Mansfield
  • 1,061
  • 8
  • 20
  • 33

2 Answers2

2

If all the databases are on the same instance you could have a central database (named reports, admin or whatever you like). Your report could use that database for your reports datasource.

You could then have a stored procedure that made use of dynamic SQL and a parameter to determine which client database the query should read from.

So your report connects to the reports database and runs the stored procedure with the parameter of 'ClientA', this is passed into the dynamic SQL and executed.

If the databases are in separate instances you would need to use linked servers for this method to work which could cause some security issue so I would look into that before enabling it.

James Anderson
  • 5,794
  • 2
  • 27
  • 43
1

If I understand the situation correctly, you might be able to use an expression to create a dynamic connection string in the data source for the linked report. I've done this in the past where I create a parameter called @Server and then set the connection string of the data source to the following expression: ="Data Source=" & Parameters!Server.Value & "; Initial Catalog=DBName"

In my case, I was populating that parameter with available values that the user had to select in order to view the report. However, passing a variable from a parent report should work as well. Then you'd just have to figure out a way to set the parameter in the parent report based on the different users.

gunther_montez
  • 11
  • 1