Reporting Services Expression-based Connection Strings can't have any data driven parameters

Question

I need my reports to have dynamic connections strings. We have multiple database servers and catalogs and only want to maintain a single Report file. The only solution I could find that would let me do this programmatically was "Expression-based Connection Strings". Basically I programmatically pass in parameter values to the report for the ServerName and InitialCatalog.

This works for simple reports. Although not ideal as modifying the report requires changing the connection to a hardcoded connection string and then switch back to the expression-based one when I want to save and publish.

HOWEVER, this does not work for reports that have data-driven parameters. For example I have a report that filters data based on a "City" parameter that the user selects when they first open the report. The City parameter is feed data from a query. It seems that I can't just set the connection parameters and let reporting services query for the City parameter.

I'm open to ideas here other than "Expression-based connection strings".

Thanks.

Answer 1

A possible option is to create a deployment script(which uses rs.exe) and deploy multiple versions of the report. In the deployment script you can update the datasource of the report. Your source control would still only have one report and each time you released it you run the script to update the multiple copies you have in production.