SSRS: Working with Multi-Value Parameters

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).

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.

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:

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”.”

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

Comments

[Ryan]

Thanks for the useful information. I want to ask an additional question, have you tried using these at all with Report Subscriptions in CRM? I ask because it seems like even though I have setup a multi-value parameter, the subscription only lets me choose one value at a time per subscription. This seems to greatly reduce the usability of this type of parameter and could cause my users to manage multiple subscriptions to utilize the report?

[Andrew Magnotta]

Ryan, I see your point. Unfortunately, since Report Subscriptions are designed to be snapshots in time, it doesn’t look like they would work well with multi-value params because as you point out subscriptions require a default to be set for each parameter so when the job runs it knows what values to return.

[Ryan]

Thanks for the reply, I did find a slight workaround though. Once I created the Snapshot of my report, then went into the Reports server page and modified the Properties\Parameters Default Values to include all the values in my multi-select. This did require me to copy the comma delimited list of param values into the Default Value text box but this seems to work once I refresh my snapshot.

My problem now is figuring out why the subscription I created is not writing the output file to the UNC path I provided, but I'm sure thats a different blog post :)

[Andrew Magnotta]

Thats a good find. I was also thinking you can clone your report and strip off the multi-value parameters (defaulting it to all values in a sense). Then use that report for your Subscriptions.