SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

SSRS How-To: Simple Method to Pass MDX Multi-Select Parameters to MDX Datasets

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!




Useful information and case studies covering Data Warehousing, Data Modeling, and Business Intelligence

I started my career in IT nearly 10 years ago and have remained influenced and driven by two particular technology initiatives - Business Intelligence and Data Warehousing. Opportunities for partnership, learning, and innovation will continue to present themselves as we strive to meld people, business, and technology. I look forward to these opportunities as I am fulfilled by my membership to a scientific community that is driven by the development and execution of technology solutions that enhance the way we live and conduct business.


Leave a comment on the original post [saldeloera.wordpress.com, opens in a new window]

Loading comments...