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.
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.