1

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.

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.

3
  • The databases are on different instances, unfortunately. I'm trying to see if I can parameterize the connection somehow - I don't much like the idea of dynamic sql and a centralized database, although I'll resort to it if I have to. Thanks for your help! – Mansfield Apr 30 '14 at 15:52
  • No worries. Let me know if you find another way but from memory the only other way to parameterise a connection is to start using XP_CMDSHELL to run SQLCMD. This method and using a linked server with dynamic SQL both have their problems when it comes to security and performance. Personally I have chosen the later in the past. – James Anderson May 1 '14 at 10:20
  • I've got it limited to a hidden parameter now which is working fine, except the user can change it when they subscribe. I'll have to see if that can be fixed... – Mansfield May 1 '14 at 15:15
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.