How to exclude a certain member from a MDX call that gets all descendants of a member at a higher level

14
votes

In an OLAP database I work with there is a 'Location' hierarchy consisting of the levels Company -> Region -> Area -> Site -> Room. I am using the following MDX to get all the descendants of a particular member at company level.

DESCENDANTS([Location].[Test Company],[Location].[Site], SELF_AND_BEFORE)

I now have a requirement to exclude a particular Region, named "Redundant", from the report. How can I change the above MDX to exclude this particular Region (and all its descendants)? I know this Region will be called "Redundant" but I do not want to hard-code any of the other Region names, as these may change.

2
mdx

2 Answers

24
votes

The EXCEPT function will take a set, and remove the members you dont want. In your case you need to say:

EXCEPT(
{DESCENDANTS([Location].[Test Company],[Location].[Site], SELF_AND_BEFORE)},
{DESCENDANTS([Location].[Whatever].[Redundant],[Location].[Site], SELF_AND_BEFORE)}
)

This gives you everything in the first set except what you've mentioned in the second. It's easier to understand like this:

EXCEPT({the set i want}, {a set of members i dont want})

You shouldnt need to worry about the third (optional) argument: http://msdn.microsoft.com/en-us/library/ms144900.aspx

2
votes

When returning members for your hierarchy simply use "-" to exclude a member you don't want. This is how I exclude unknown members:

select
{[Module].[Hierarchy].[Module].Members - [Module].[Hierarchy].[Module].[Unknown]} on rows,
{[Date].[Month-day].[Day Of Month].Members - [Date].[Month-day].[Day Of Month].[Unknown]} on     columns
from [StatsView]
where {[Measures].[Maintainability Index]}