Multivalue parameters in Reporting Services
Part two of a three-part series of blogs

This blog shows how to create multivalue parameters based on dropdowns, whether in SQL Server Reporting Services or Report Builder 3.0.

  1. MultiValue Parameters - SSRS and Report Builder
  2. Creating multivalue parameters in Reporting Services (this blog)
  3. Showing the Values Chosen for a Multivalue Parameter

Posted by Andy Brown on 07 November 2011

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Creating multivalue parameters in Reporting Services

The following technique works just as well in Report Builder 3.0.  There are four stages:

  1. Create the basic report.
  2. Create a dataset for the dropdown list.
  3. Create a parameter based on this dropdown list.
  4. Incorporate this parameter as a filter into your report.

Let's have a look at each one in turn.

Creating the Basic Report

This blog assumes that you're not a Reporting Services novice, and that you know how to create a basic table report (here ours is of film data):

List of films report

A simple table listing a few details for each film in a database

Creating a Dataset for the Dropdown List

The next stage is to create a dataset upon which to base our multivalue parameter:

Droplist of studios

The drop list will be based solely on the table of studios.

 

This is what you should be aiming for:

Dataset of studios, with two fields

A report with a parameter dropdown should have two datasets: one upon which the report itself is based (here dsFilms), and the second on which the dropdown is based (here dsStudios).

 

Here is what your dsStudios dataset should look like:

Query listing studio id and name

The studios dataset must include the studio name (which appears on screen) and the studio id (which is used to pick out the right films).

 

Creating the Parameter

The following steps show how to create a multivalue dropdown parameter.  First, create the parameter:

Adding a parameter

Right-click on Parameters and choose to add one!

 

The next step is to configure the parameter:

Steps to create multivalue parameter

Follow these steps:

 

Finally, choose the possible available values for the parameter:

Setting the available values

To set the parameter's available values, follow these steps:

 

Incorporating the Parameter as a Filter in your Report

To get the report to show only films for the combination of studios you've chosen, first show the table's properties:

Displaying tablix properties

Firstly, choose to show the properties of your table by right-clicking on the edge of it (you'll need to click in the table to select it first).

 

You can now specify a filter for the table, so that it shows only films for the combination of studios you've chosen:

Steps to create filter for a table

Follow the numbered steps below.

The numbered steps shown above are as follows:

  1. Choose to work with filters for the table.
  2. Choose to add one.
  3. We're filtering the table by the film studio chosen (you did include the FilmStudioId link field in your table's dataset, didn't you? If not, you'll have to edit this dataset to add it in).
  4. We're showing all the films where the studio id is in a list.
  5. Click on the expression builder to create the expression shown below.

The expression for the value at step 5 above is as follows:

Creating parameter expression

Choose to refer to a parameter, then double-click on the StudioId parameter to create the expression shown.

 

When you select OK and try previewing your report, everything should now all work!

 

  1. MultiValue Parameters - SSRS and Report Builder
  2. Creating multivalue parameters in Reporting Services (this blog)
  3. Showing the Values Chosen for a Multivalue Parameter
This blog has 0 threads Add post