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.
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])
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:
| Flag | Description |
|---|---|
| SELF | Returns 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. |
| AFTER | Returns descendant members from all levels subordinate to the specified level or distance. |
| BEFORE | Returns 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_AFTER | Returns 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_AFTER | Returns 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_BEFORE | Returns 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_AFTER | Returns descendant members from all levels subordinate to the level of the specified member, and includes the specified member. |
| LEAVES | Returns 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.
