Customer Effective is a leading innovator in relationship management and process automation solutions based on the Microsoft Dynamics CRM platform. The company is a Gold Certified National System Integrator having completed hundreds of Microsoft Dynamics CRM implementations and development projects. Recent CRM and Microsoft awards include recognition as Microsoft’s 2013, 2012, 2011 and 2010 East Region CRM Partner of the Year, the 2013 US Financial Services Partner of the Year, a 2013 US Reseller of the Year Award Finalist, the a 2012 Worldwide Microsoft Financial Services Partner of the Year Finalist, the 2013 and 2010 Worldwide CRM Partner of the Year Finalist, the 2009 Worldwide Microsoft Financial Services Partner of the Year Award Winner, being named to the Microsoft Dynamics President’s Club from 2006 through 2010 and in 2012 and 2013, recipient of the Microsoft Dynamics Inner Circle distinction in 2008 through 2010 and in 2012 and 2013, as well as being a member of the 2008, 2009,2010, 2011, 2012 & 2013 Inc. 500|5000 list.
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
Follow Microsoft Dynamics