CODEWITHSUNDEEP
mdx
Tim C
Tim C

Reputation: 70608

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

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.

Upvotes: 15

Views: 39469

Answers (2)

Stan Bashtavenko
Stan Bashtavenko

Reputation: 1095

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]}

Upvotes: 4

Magnus Smith
Magnus Smith

Reputation: 5963

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

Upvotes: 26

Related Questions