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.
Best Answer
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.