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.

Answer 2

Edit - another suggestion

Have the datasource in Visual Studio point to the development database. Have the deployed datasource point to the production database. Set the datasource to not overwrite existing datasources on deployment. This means all development in VS will be against the development database but when you deploy the report, it will use the previously deployed datasource that points to the production database.

Original suggestion

We have a similar issue - we have a suite of reports that need to be able to run on any of our databases for historical financial years so the user needs to be able to select which database to run the report for.

The strength of Reporting Services is that everything is an expression and the SQL for the data set is no different. What we do is modify the SQL to use the database as selected by the user.

It is set up as follows: On Sql Server in a common database we create a table called DatabaseLookup that has the fields DatabaseLabel (the display name) and DatabaseName (the actual name of the database) plus an INT SortOrder so we can order the databases sequentially backwards as we create a new one. In Reporting Services we create a datasource that points to our common database and a new dataset called Databases that uses that datasource:

SELECT DatabaseLabel, DatabaseName
FROM DatabaseLookup
ORDER BY SortOrder

A Parameter is set up called Database which uses the Databases dataset with Value field = DatabaseName and Label field = DatabaseLabel and the non-queried default to be our current database.

Now we modify the SQL for our main table of the report into an expression. Let's say our SQL looks like this:

SELECT SomeField, OtherField
FROM SomeTable

We turn that into a calculated expression, like so:

="SELECT SomeField, OtherField "
&"FROM " & Parameters!Database.Value & ".dbo.SomeTable "

The label of the database selected is also included in the header in a small font so people know what data they are looking at.

When a new financial year comes along, we simply add a new row to our DatabaseLookup table and every report in our system can now use that new database.