SSRS Tip: Simplest Method to Pass MDX Multi-Select Parameters to MDX Datasets
I recently ran across a poorly covered topic in regards SSRS development using MDX parameters and datasets. After some trail and error and visiting a number of online resources, I found the approach below to be the most straight forward.
To pass a multivalued MDX parameter to an MDX based dataset, perform the following routine:
1. Create the parameter dataset (Right click DataSet >> Add Dataset…) – Note: the MEMBER_CAPTION and UNIQUENAME enables the cleanest approach to assigning values to the parameter (Step 2). Also, we will be filtering out unwanted parameter values.
Step 1.1 – click ‘use dataset embedded in report’
Step 1.2 – select datasource
Step 1.3 – click ‘Query Designer’
Step 1.4 – Click ‘Design Mode’ icon
Step 1.5 – Enter query
WITH MEMBER [Measures].[ParameterCaption] AS [Fixed Asset Book].[Book ID].CURRENTMEMBER.MEMBER_CAPTION MEMBER [Measures].[ParameterValue] AS [Fixed Asset Book].[Book ID].CURRENTMEMBER.UNIQUENAME
SET [Book ID] AS [Fixed Asset Book].[Book Id].ALLMEMBERS
SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue]} ON COLUMNS , FILTER([Book ID], [ParameterCaption] <> “All” AND [ParameterCaption] <> “” AND [ParameterCaption] <> “N/A” AND [ParameterCaption] <> “Unknown”) ON ROWS FROM [FixedAssets]
Notice the full unique names in ‘ParameterValue’ Column…the use of these values will enable a StrToSet function later in our primary MDX dataset.
2. Create the parameter (on the left pane, right click ‘Parameters’, ‘Add Parameter…’)
2.1 – From the General tab, add ‘allow multiple selection’ if needed
2.2 – Assign ParameterCaption as the label (will be displayed to the user on the report)and ParameterValue as the value (will be interpreted by SSRS when filtering report data)
3. Create the dataset (Right click DataSet >> Add Dataset…)
Step 3.1 – click ‘use dataset embedded in report’
Step 3.2 – select datasource
Step 3.3 – click ‘Query Designer’
Step 3.4 – Click ‘Design Mode’ icon
Step 3.5 – Enter query to include the WHERE (StrToSet(@BookID, CONSTRAINED)) clause – this will enable the MDX to interpret the @BookID multiselect object (from step 1) as dynamic memberset.
Step 3.6 – Click the ‘Query Parameters’ icon
Step 3.7 – Create the BookID parameter assignment
4. You’re all set!
