DESCENDANTS (MDX)
Returned Type
Syntax and Details
DESCENDANTS(member_expression, level_expression, OPTIONAL_FLAG)
Or:
DESCENDANTS(member_expression, level_offset, OPTIONAL_FLAG)
-
member_expression is an expression that evaluates to a member. In the following discussion, this member is the target member.
-
level_expression is a level identifier. This must identify a level that is in the same hierarchy as the target member and that is lower in the hierarchy (more granular).
As an alternative, you can specify level_offset, which is an integer that indicates a level relative to the one that contains the target member. For example, use 1 to specify the next lowest level.
In the following discussion, the phrase target level refers to the level specified by level_expression or level_offset.
-
OPTIONAL_FLAG specifies the level or levels whose members should be returned. This option describes a relationship to the target level. If specified, OPTIONAL_FLAG must be one of the following keywords, which are not case-sensitive:
-
SELF (the default) — Returns only descendents that are members of the target level.
-
AFTER — Returns descendents that are members of all levels below the target level.
-
BEFORE — Returns all descendents up to and including the target member. That is, it returns descendents that are members of all levels above the target level, and it also returns the target member.
-
BEFORE_AND_AFTER — Returns descendants from both above and below the target level, but does not include members from the target level.
-
SELF_AND_AFTER — Returns descendents that are members of the target level, as well as descendents from all levels below it.
-
SELF_AND_BEFORE — Returns descendents that are members of the target level, descendents that belong to all levels above the specified level, and the target member.
-
SELF_BEFORE_AFTER — Returns descendents from all levels, including the target member.
In this case, the target level does not affect the outcome.
-
When the function returns members of more than one level, the hierarchy affects the order of the members as follows: A member of a higher level is followed by its children from the next lowest level, followed by the next member of the higher level, and so on. See the example for SELF_AND_AFTER.
This implementation of DESCENDANTS does not support the optional LEAVES flag.
Example
For reference, in the Patients cube, the BirthD dimension contains the following levels, from highest to lowest:
-
[BirthD].[H1].[Decade]
-
[BirthD].[H1].[Year]
-
[BirthD].[H1].[Quarter Year] (which represents year plus quarter)
-
[BirthD].[H1].[Period] (which represents year plus month)
-
[BirthD].[H1].[Date] (which represents year plus month plus day)
The following example gets all the descendents of the year 1990, within the [BirthD].[H1].[Period] level:
SELECT DESCENDANTS(birthd.1990,birthd.period) ON 1 FROM patients
1 Jan-1990 *
2 Feb-1990 2
3 Mar-1990 1
4 Apr-1990 1
5 May-1990 1
6 Jun-1990 *
7 Jul-1990 2
8 Aug-1990 2
9 Sep-1990 1
10 Oct-1990 3
11 Nov-1990 1
12 Dec-1990 *
This example uses the default for OPTIONAL_FLAG (SELF), so the function returns only descendents of 1990 that are members of the period level.
The following variation uses NON EMPTY and thus filters out periods when no patients were born:
SELECT NON EMPTY DESCENDANTS(birthd.1990,birthd.period) ON 1 FROM patients
1 Feb-1990 2
2 Mar-1990 1
3 Apr-1990 1
4 May-1990 1
5 Jul-1990 2
6 Aug-1990 2
7 Sep-1990 1
8 Oct-1990 3
9 Nov-1990 1
The period level is two levels below the year level, and the following query (which uses level_offset as 2) is equivalent to the first query:
SELECT DESCENDANTS(birthd.1990,2) ON 1 FROM patients
1 Jan-1990 *
2 Feb-1990 2
3 Mar-1990 1
4 Apr-1990 1
5 May-1990 1
6 Jun-1990 *
7 Jul-1990 2
8 Aug-1990 2
9 Sep-1990 1
10 Oct-1990 3
11 Nov-1990 1
12 Dec-1990 *
The next variation uses AFTER:
SELECT DESCENDANTS(birthd.1990,birthd.period,AFTER) ON 1 FROM patients
1 Jan 1 1990 *
2 Jan 2 1990 *
3 Jan 3 1990 *
...
363 Dec 29 1990 *
364 Dec 30 1990 *
365 Dec 31 1990 *
This example returns descendents of 1990 of all levels below the period level. In this case, there is only one lower level: date, which corresponds to year plus month plus day of the month.
The next variation uses SELF_AND_AFTER. This example returns members of more than one level and demonstrates the order in which these members are returned.
SELECT DESCENDANTS(birthd.1990,birthd.period,SELF_AND_AFTER) ON 1 FROM patients
1 Jan-1990 *
2 Jan 1 1990 *
3 Jan 2 1990 *
4 Jan 3 1990 *
...
33 Feb-1990 2
34 Feb 1 1990 *
35 Feb 2 1990 *
36 Feb 3 1990 1
...
346 Dec-1990 *
347 Dec 1 1990 *
348 Dec 2 1990 *
349 Dec 3 1990 *
...
377 Dec 31 1990 *
The next variation uses BEFORE:
SELECT DESCENDANTS(birthd.1990,birthd.period,BEFORE) ON 1 FROM patients
1 1990 14
2 Q1 1990 3
3 Q2 1990 2
4 Q3 1990 5
5 Q4 1990 4
In this case, the query obtains all descendents of 1990 that are members of the levels above the period level (that is, it returns members of the quarter year level). Notice that 1990 is also returned.
