15

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.

Tim C
  • 70,053
  • 14
  • 74
  • 93

2 Answers2

26

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

Magnus Smith
  • 5,895
  • 7
  • 43
  • 64
3

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]}
Stan Bashtavenko
  • 1,025
  • 11
  • 16