Is it possible to specify the data source during execution of an SSRS report via the web service?

Question

I have a single server that hosts our production and development databases. It's running SQL Server 2008 (not R2) with SQL Server Reporting Services.

I deploy a VS 2008 Reporting Services project to the server to provide the reports. All the reports use a single shared data source, currently pointing at the production database.

A background service elsewhere creates PDF reports via the web service interface to SSRS.

My question is, given this environment, is it possible to programatically change the data source to be used during execution via the web service so either the dev or production database is used by a report?

I don't want to modify the shared data source or the report definition, I just want to be able to set the name of the database before I execute the report and I can't see how this is possible.

The only "simple" solution I can see is to install another report server instance, which seems like overkill for what I want. I'm about to do this unless anyone has a cunning answer for me!

Answer 1

Yes try this:

1. Create two report parameters "Server" and "Database" (you should probably create a default value so the report will run by default)

2. define the data source as a Expression like so:

   ="data source=" & Parameters!Server.Value & ";initial catalog=" & Parameters!Database.Value

Now you should be able to specify the database and server dynamically at run time

Keep in mind, that the underlying dataset definition needs to be valid in each db and server you execute the report from.

You can also pass the paremeters via a url string like so:

http://server/reportserver?/dir/Report&rs:Command=Render&Server=VALUE1&Database=VALUE2

Keep in mind that these need to be URL encoded and to remember to pass the Value, and not the Label if they are different.