An SSRS feature that was introduced in Visual Studio 2005 is the ability to add multi-value parameters (MVPs) or basically parameters that allow the user to choose from a set of values and apply them to a report. Here I will describe how to setup an SSRS report with multiple MVPs, along with tips that will assist you to utilize this functionality in Visual Studio 2008.

1. Setup your main dataset that will populate your report.

Here is an example statement that returns a few different values from the FilteredOpportunity view:

SELECT new_saleschannelname, accountidname, owneridname                                  FROM FilteredOpportunity

2. Utilize separate datasets for each Multi-Value Parameter.

After setting up the main SQL dataset, create additional datasets to return a list of values to represent each individual MVP.

TIP: When possible use the DISTINCT command to pass only the values that will be available to the main SQL dataset.

Here are the three datasets used for this example:

A. Sales Channel:                                                                                                       SELECT DISTINCT new_saleschannelname                                                                FROM FilteredOpportunity                                                                                       ORDER BY new_saleschannelname

B. Account:                                                                                                                   SELECT DISTINCT accountidname                                                                             FROM FilteredOpportunity                                                                                      ORDER BY accountidname

C. Owner:                                                                                                              SELECT DISTINCT owneridname                                                                              FROM FilteredOpportunity                                                                                         ORDER BY owneridname

3. Setup the Multi Value Parameter(s)

Add each MVP to the list of report Parameters as follows:

General Tab: Set the Name and Prompt for the parameter. Check the Allow blank value (""), and Allow multiple values. Set visibility to (Visible).

clip_image002[4]

Available Values Tab: Select 'Get values from a query' and utilize the corresponding query for your MVP. Select the correct dataset and set the Value and Label fields from your query.

clip_image004[4]

TIP: Default Values Tab - If you want to have all values for an MVP selected by default you can set the default value of the parameter to the same Value field used in the 'Available values' tab.

4. Use the "IN" statement to call the MVP from the WHERE clause in the main SQL dataset.

The statement (FieldName IN (@Parameter)) tells SSRS that @Parameter is a multi value parameter and to act accordingly.

SELECT saleschannelname, accountidname, owneridname                                           FROM FilteredOpportunity                                                                                     WHERE (new_saleschannelname IN (@Channel))AND (accountidname IN (@Client)) AND (owneridname IN (@Owner))

Also, be sure that each parameter is properly declared in the Properties of the Main SQL dataset:

clip_image002

TIP: If you don’t properly declare each parameter using the IN statement or in the dataset parameters you will receive the error “Must declare the scalar variable “@Parameter”.”

clip_image004

5. The result is a clean report that gives users the power to filter the results of a report to very specific data points:

image