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

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.

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.

As you can see this is a pretty simple task.

Trust me I’m a Dr.