Wednesday, August 1, 2012

How to remove the ALL member from your parameter

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