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.
Here is an example statement that returns a few different values from the FilteredOpportunity view:
SELECT new_saleschannelname, accountidname, owneridname FROM FilteredOpportunity
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
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.
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”.”
Other Microsoft Sites
I'm a Customer
I'm a Partner
Use the official Twitter tags:
#MSDYNCOMM | #CONV13