Creating a set that excludes certain members from a hierarchy using MDX exclude and descendant
I have a report where the client wants to exclude all but 3 members below a certain level from a hierarchy.
The easiest way to achieve this is to exclude them when designing the query in BIDS, but this is much work and not easy to maintain, especially when you want to use this in multiple reports. And when this hierarchy is changed it will automatically be included even when we don’t want it.
I thendecided to create a set that includes all the members that need to be excluded, so i can use a NOT IN while designing the query. After browsing the internet i found How to exclude a certain member from a MDX call that gets all descendants of a member at a higher level. Exactly what i needed. It works like this:
Select all the members from the level i need from the hierarchy by using DESCENDANTS :
DESCENDANTS([Product].[Product Categories].[Category].&[4],
[Product].[Product Categories].[Product],
SELF)
This will select all the Products from the Category “Accessories”,
Next we want to Exclude the Products we still want in our set, we do this by using the Except function, this function lets you distract the values from one set from another:
Except(
DESCENDANTS([Product].[Product Categories].[Category].&[4],
[Product].[Product Categories].[Product],
SELF)
, { [Product].[Product Categories].[Product].&[483]
,[Product].[Product Categories].[Product].&[486]
}
)
The { } around the products make it a set so they can be distracted using Except. This statement can be added as a Set to the Cube, and thus used in the Query to build the dataset by using NOT IN and selecting the SET.
To test this I made the following MDX statement to execute at the Query analyzer:
SELECT
{} ON COLUMNS,
{
Except(
DESCENDANTS([Product].[Product Categories].[Category].&[4],
[Product].[Product Categories].[Product],
SELF)
,{[Product].[Product Categories].[Product].&[483]
,[Product].[Product Categories].[Product].&[486]})
}
ON ROWS
from [Adventure Works]
.
