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.
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
[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.
[Product].[Category].Children ON ROWS
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.