How do I Dynamically set Data Source in Reporting Services?

Question

I am looking for a method of changing the data source for a report in SQL Server Reporting Services.

Basically what functionality I am looking for is a drop down box (parameter) at the top of the report that permits the user of the report to select a different data source to run the report against (so when the drop down selection is changed and view report is clicked it runs the same report but against a different data source).

Is there any way to do this?

Answer 1

If you're referring to the SQL Server Reporting Services then you can only have one data source (either local to the report or shared). You have to get tricky, like having a stored procedure return different sets of data based on a parameter which can be linked to a drop down on the report.

See (SQL 2000 links, not sure what version you're using):

• Defining Report Data
• Using Dynamic Queries

If on the other hand you're happy to use the (IMHO much more flexible) Report Viewer control (either the Web or WinForms version) then you can represent any UI you like and generate any data you like based on user interation and simply get the viewer to render the output for you using the same engine. The big difference is that all the work is done by the client (which for a web app is your web server instead of your database server).

See:

• GotReportViewer.com