Descendants MDX Function in Planning Analytics

Definition

The ‘Descendants’ MDX function in TM1/Planning Analytics is used to generate a set containing all descendant members (i.e. children, grand children, great grand children etc) below a specified member within a hierarchy. This enables comprehensive selection within hierarchical data structures.

Syntax of Descendants MDX

The basic syntax for Descendants function is as follows:

Descendants([Hierarchy].[Member])

Example of Descendants MDX in TM1

Balance Sheet Accounts

DESCENDANTS([Account].[Balance Sheet])

This code will pass the value “Balance Sheet” into the descendants mdx function and return all elements beneath “Balance Sheet”.

In the example below, we have the Account structure containing all information like Trial Balance, Balance Sheet, Statistics, etc. We are interested in to present a set of elements only for the Balance Sheet accounts.

Original set of elements as per picture below with no MDX applied.

Planning Analytics Set Editor

Now, we are going to apply the descendants MDX function to retrieve only the Balance Sheet members.

DESCENDANTS([Account].[Balance Sheet])

Save Dynamic Set with Balance Sheet Accounts

After creating the MDX for the Dynamic Set of Balance Sheet Accounts, we can save it by clicking Save. With this any new elements, will be automatically added to the Set we just created for our reports. This can be either used in PAW or PAX.

After saving the Set and clicking on Apply and close, we can verify the results on screen.

Edit MDX to Create Additional Set

Another example, using the same Account structure, could be that I am only interested in having a set of elements for Current Assets for my reports. The MDX expression for it would be as follows. This could be a straight edit of the above code, swapping Balance Sheet (the alias) for CUAS (an element ID):

DESCENDANTS([Account].[CUAS])
Descendants MDX in Set Editor in Planning Analytics

The results below can be used on my reports after saving this Set as BS Current Assets and clicking on Apply and close.

Additional Operators for the Descendants MDX Function

There are some additional operators that can be included with Descendants. These allow you to determine the level to go down from the selected element and a set of flags that modify the outcome of the function.

Syntax of Additional Operators

Descendants([Hierarchy].[Member], Depth, Flag)

The Depth operator here is numeric, where it represents the number of levels to go beneath the member. So, a depth of 1 will return the immediate children. A depth of 2, the grand children, 3, the great grand kids and so on.

The Flag will modify the behaviour of the MDX to return different sets of members. According to Microsoft, the following flags are available:

FlagDescription
SELFReturns only descendant members from the specified level or at the specified distance. The function includes the specified member, if the specified level is the level of the specified member.
AFTERReturns descendant members from all levels subordinate to the specified level or distance.
BEFOREReturns descendant members from all levels between the specified member and the specified level, or at the specified distance. It includes the specified member, but does not include members from the specified level or distance.
BEFORE_AND_AFTERReturns descendant members from all levels subordinate to the level of the specified member. It includes the specified member, but does not include members from the specified level or at the specified distance.
SELF_AND_AFTERReturns descendant members from the specified level or at the specified distance and all levels subordinate to the specified level, or at the specified distance.
SELF_AND_BEFOREReturns descendant members from the specified level or at the specified distance, and from all levels between the specified member and the specified level, or at the specified distance, including the specified member.
SELF_BEFORE_AFTERReturns descendant members from all levels subordinate to the level of the specified member, and includes the specified member.
LEAVESReturns leaf descendant members between the specified member and the specified level, or at the specified distance.

Video Guide to Descendants MDX in TM1

In the video below, we will illustrate the simple methods shown above.

The video shows how to use the automated features of the Planning Analytics set editor to create a Balance Sheet Account set. Following this , we then show you how to edit the resulting MDX to create a new set for Current Asset accounts before rounding the video out with an illustration of where to find your new sets in both the set editor and task pane.

Do you need Help with MDX?

If you’d like some help with MDX in TM1/Planning Analytics – be it a simple tip to correct some code, or some assistance building new reports or PAW dashboards, please get in touch. We’d be delighted to help.

Exploring TM1 – Contact Us

  • This field is for validation purposes and should be left unchanged.

You might also like