What is the best way to list a member and all of its desendants in MDX?

Question

In an OLAP database I work with there is a 'Location' hierarchy consisting of the levels Company, Region, Area, Site, Room, Till. For a particular company I need to write some MDX that lists all regions, areas and sites (but not any levels below Site). Currently I am achieving this with the following MDX

HIERARCHIZE({
[Location].[Test Company], 
Descendants([Location].[Test Company], [Location].[Region]), 
Descendants([Location].[Test Company], [Location].[Area]), 
Descendants([Location].[Test Company], [Location].[Site])
})

Because my knowledge of MDX is limited, I was wondering if there was a simpler way to do this, with a single command rather that four? Is there a less verbose way of achieveing this, or is my example the only real way of achieving this?

Answer 1

The command you want is DESCENDANTS. Keep the 'family tree' analogy in mind, and you can see that this will list the descendants of a member, down as far as you want.

You can specify the 'distance' (in levels) from the chosen member, 3 in your case.

There are a few weird options you can specify with the third argument, you want SELF_AND_AFTER, see http://msdn.microsoft.com/en-us/library/ms146075.aspx

EDIT - oops, as santiiiii noticed, it should be SELF_AND_BEFORE

Answer 2

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