Using a Parameter to set the Data Source for a Report
A single report in Reporting Services can serve many masters. This blog shows how to allow a user to choose which data a report should display, using a dropdown parameter.

Posted by Andy Brown on 06 August 2012 | 1 comment

SSRS Reports using Dynamic Data Queries

How cool would this be? 

Choosing data source

When you run the report, you can either show a list of film directors of a list of film studios.

 

You get different reports according to which table you select:
List of film directors List of film studios
If you choose to show directors If you choose to show studios

It turns out that this sort of one-report-serving-all effect is remarkably easy to achieve.  There are 3 stages: creating the drop-down parameter, creating the dataset for the report and creating the table.

Step 1 - Creating the Dropdown Parameter

The first thing to do is to make sure that the dropdown parameter appears at the top of your report:

Adding a parameter

First add your parameter (this assumes you're using SSRS 2008 or 2012).

 

It's a text parameter, which we've called ReportTable (so far, so straightforward):

Creating parameter

Give your parameter a name, and a sensible prompt.

 

For the list of available values to comprise the drop list, make the visible label something easy to read, but use the underlying table name for the value:

The dropdown available values

Add one possible available value for each table a user can choose.

Step 2 - Creating the Dataset for your Report

The trick to get our report working is to make sure that whether we choose directors or studios, we still have the same field names.  First create a dataset:

Create a variable dataset

When creating the dataset, click on the expression builder as shown.

In the expression builder, type in an expression which will evaluate to a valid SQL statement:

Dynamic SQL expression

This will always return two fields, called IdField and NameField.

 

Suppose that the user chooses to show Studios.  Then the dataset expression will evaluate to:

SELECT

StudioId AS IdField,

StudioName AS NameField

FROM

tblStudio

This gives the following dataset:

The dataset with field names

It doesn't matter what parameter value the user chooses, the fields in the dataset will always be the same.

 

Step 3 - Creating the Table for the Report

We can now create a table for the report:

The table for the report

A standard table, displaying two columns.

 

The expression for the title of the report could be something like:

Title for report

The title changes according to whether directors or studios are being reported.

 

An Alternative Approach

Although the above is relatively easy to implement, it may run slowly (dynamic SQL can't be optimised).  Perhaps a better idea would have been to create two stored procedures, and dynamically allocate these to the report dataset:

Stored procedure expression

You can use an expression for the stored procedure, which would depend on the parameter value chosen.

Just a thought to finish up on!

This blog has 1 comment

Comment added on 05 April 2013 at 13:58 GMT
This works fine for Single valued parameter , what about multi valued parameter, means if user wants to show records of both tables
Reply from Andy Brown

You couldn't show records from two different tables in the same tablix item.  You could easily configure a multivalue parameter (let's call it prmTable, say) to allow a user to choose mutliple answers, but what then?

You could use Parameters!prmTable.Value(0) to get at the first value chosen, for example.  I suppose you could then have two separate report items, each one displaying different data, but I can't see why you'd want to do this!

A full-blown discussion forum is being built for this site, which will allow you once more to add comments and discussion threads.