Business Intelligence Blogs

View blogs by industry experts on topics such as SSAS, SSIS, SSRS, Power BI, Performance Tuning, Azure, Big Data and much more! You can also sign up to post your own business intelligence blog.

MDX/SSRS: How to remove the ALL member from your parameter

  • 18 January 2011
  • Author: ChrisAlbrektson
  • Number of views: 36709
  • 0 Comments

This brief blog is for individuals that are new to creating reports from a cube.

Since you’re new to creating reports from a cube you still probably rely on using the query designer because maybe you don’t know MDX yet and that’s fine.  So pretend you had a request to create a report with a drop down parameter that allows multiple selections and you used the query designer to create the MDX for you.

This is something similar to how your MDX will look like.

WITH

MEMBER [Measures].[ParameterCaption] AS [Product].[Category].CURRENTMEMBER.MEMBER_CAPTION

MEMBER [Measures].[ParameterValue] AS [Product].[Category].CURRENTMEMBER.UNIQUENAME

MEMBER [Measures].[ParameterLevel] AS [Product].[Category].CURRENTMEMBER.LEVEL.ORDINAL

 

SELECT

{[Measures].[ParameterCaption],

[Measures].[ParameterValue],

[Measures].[ParameterLevel]} ON COLUMNS,

[Product].[Category].ALLMEMBERS ON ROWS

FROM [Adventure Works]

This is what your parameter will look like.

1

 

 

Now let’s say the end-user doesn’t like seeing the ‘Select All’ and ‘All’ in the drop down, as shown above. Below I will show you how to fix this issue quickly by modifying the MDX and using the Children Function.

This is the new MDX.

WITH

MEMBER [Measures].[ParameterCaption] AS [Product].[Category].CURRENTMEMBER.MEMBER_CAPTION

MEMBER [Measures].[ParameterValue] AS [Product].[Category].CURRENTMEMBER.UNIQUENAME

MEMBER [Measures].[ParameterLevel] AS [Product].[Category].CURRENTMEMBER.LEVEL.ORDINAL

 

SELECT

{[Measures].[ParameterCaption],

[Measures].[ParameterValue],

[Measures].[ParameterLevel]} ON COLUMNS,

[Product].[Category].Children ON ROWS

FROM [Adventure Works]

This is how the parameter looks like now with the modified MDX.

2

As you can see this is a pretty simple task.

Trust me I’m a Dr. Wink

 

Print
Categories: Blogs
Tags:
Rate this article:
4.5

ChrisAlbrektsonChrisAlbrektson

Other posts by ChrisAlbrektson

Please login or register to post comments.