SSRS Multi Value Parameters with MDX and checking whether all values are selected

It is a very common question in many forums: How can we check whether all values in the parameter are selected and how to add selected values to a dynamic query that will be used for loading the main dataset. Once I wrote something similar to SQL, here is the link if you need it: http://dinesql.blogspot.com/2011/09/reporting-services-parameters-adding.html

What if you need to generate a dynamic MDX statement for your main dataset and need to get values from a parameter which is formed using a dimension, in to your query. I have seen many different implementations on this: Some have used another hidden parameter for holding the same dataset, and use it for comparing the selection using Iif function for getting the right set. Some have used CountRows functions for comparing number of records in the dataset with number of selected values in the parameter. Most of methods are working fine but there is another easy way of doing it.

If you have built the MDX for the main dataset using the designer, then you know how parameters are handled with it. Designer uses StrToSet MDX function for getting values for multi value parameters. This is the easiest way for handling it and it can be used with dynamic MDX statement too.

Here is an example:

Assume that parameters are loaded using your own query. In that case, you need to use a code like below for loading dimension members.

// There are multiple ways of loading values to parameters
// Either one is okay

// 1.
// This query returns two columns; one for value and one for label
WITH 
 MEMBER [Measures].[ParameterLabel] AS [Dim PoP].[PoP].CurrentMember.Member_Caption 
 MEMBER [Measures].[ParameterValue] AS [Dim PoP].[PoP].CurrentMember.UniqueName
SELECT 
 {[Measures].[ParameterLabel], [Measures].[ParameterValue]} ON 0
 , [Dim PoP].[PoP].[Pop].Members ON 1
FROM [OLAP_Voice];
GO


// 2.
// This query returns only one column
// You need to add two additional calculated fields in the dataset
// 1. Name - ParameterLabel, Value - =Fields!Product.Value
// 1. Name - ParameterValue, Value - =Fields!Product.UniqueName

SELECT {} ON 0
 , [Dim PoP].[PoP].[Pop].Members 
 DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME on 1
FROM OLAP_Voice;

Now if you need to check and see whether all values are selected, multiple values are selected or, one value is selected and then use selected one(s) with your main query, without using Iif function, StrToSet can be easily used . This requires two steps;

1. Add a parameter to the main dataset like below;

2. Include the parameter with StrToSet function where you need to have values from the parameter.

" WITH " & 
"  MEMBER [Measures].[Measure1] " & 
"   AS ..... " & 
"  MEMBER [Measures].[Measure2] " & 
"   AS ..... " & 

"  SET [MainSet] " &
"   AS NonEmptyCrossjoin({[Dim Geography].[Country].[Country].Members} " &
"     , StrToSet(@Product) " & // no need to check whether one or many have been selected
"    , { [Dim Department].[Department].[Department].Members} " &


" SELECT " & 
"  {[Measures].[Measure1], [Measures].[Measure2]} ON 0 " & 
"  , {MainSet} ON 1 " & 

" FROM   Cube;"

For more info on StrToSet, read this: https://msdn.microsoft.com/en-us/library/ms144782.aspx