Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

mdx

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?

like image 918
Tim C Avatar asked Dec 07 '25 04:12

Tim C


2 Answers

DESCENDANTS([Location].[Test Company],[Location].[Site], SELF_AND_BEFORE)
like image 141
Santiago Cepas Avatar answered Dec 10 '25 14:12

Santiago Cepas


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

like image 34
Magnus Smith Avatar answered Dec 10 '25 13:12

Magnus Smith



Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!