How can I allow users to switch data sources for an SSRS report?

Question

I have two SQL Server databases with identical schemas, but different data. I also have SSRS generating reports, in native mode, for one of the databases. All reports the same shared data source.

I would like to allow users to get reports for the other database. I created a second shared data source for the second database. Modifying the reports to use this second data source results in reports as expected.

Because the RDLs are the same, except for the data source, and because I don't want to maintain what are basically duplicate reports, I'm looking for a way to dynamically switch data sources, depending on user input.

Is there an easy means of accomplishing this? An existing solution would be best. Barring that, can the RDL's data source be parametrized? Or, can the RDS's connection string be parametrized?

Answer 1

You can use parameters or other expressions in the connection string.
See http://msdn.microsoft.com/en-us/library/ms156450%28SQL.90%29.aspx (2005)
or http://msdn.microsoft.com/en-us/library/ms156450.aspx (2008)