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]

Image

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)

Image

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

Image

 

4. You’re all set!

Image

 

 

About these ads

About Sal De Loera
I have been involved in Information Technology, specifically Data Warehousing and BI, for over 8 years. Over these years I have assumed the following roles: Software Engineer Intern, Programmer/Analyst, Sr. Data Warehouse Engineer, Data Architect, Director of Information Technology, and as most recently as a Data Warehouse / BI consultant. Additionally, my IT experience spans the following industries: Insurance, Restaurant, Mass Media, Education, and Local Government. I have created this blog primarily as a means to not only share information and case studies regarding Business Intelligence, Data Warehousing, and Data Modeling, but also receive input from the community on some of the topics that I'l cover. Looking forward to your feedback and to the exciting opportunities that come with new people and technology!

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

  1. Bill Edmett says:

    I really like this approach. It is much cleaner than all of the other recommended methods of using parameters within an SSRS / MDX report. I like the use of the custom MDX for the parameter dataset.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 56 other followers

%d bloggers like this: