Descendants

The Descendants functionality although in name de reverse of the Ascendants functionality has a lot more options.

Before you read on just a reminder the Geography hierarchy in the Adventure Works sample is: All ==> Country ==> State-Province ==> City ==> Postal Code.

Let’s start with the base:

SELECT Descendants
   (
    [Geography].[Geography].[Country].&[United States]
   ) ON ROWS
   ,
    [Measures].[Reseller Order Count] on COLUMNS
FROM [Adventure Works]

Will return the current member and all members on all levels below the current member (Country, State-Province, City, Postal Code):

The first option is the level. This can be a named level or a level number relative to the start member.

SELECT Descendants
   (
      [Geography].[Geography].[Country].&[United States]
    , [Geography].[Geography].[City]
   ) ON ROWS
   ,
    [Measures].[Reseller Order Count] on COLUMNS
FROM [Adventure Works]

This will return only members on the selected level:

The third option is the description flag. This flag enables which of the levels before and or after the level option will be returned. There are eight options:

Option SELF

SELECT Descendants
   (
      [Geography].[Geography].[Country].&[United States]
    , [Geography].[Geography].[City]
    , SELF
   ) ON ROWS
   ,
    [Measures].[Reseller Order Count] on COLUMNS
FROM [Adventure Works]

This is the default option and will return only members on the selected level (City):

Option AFTER

SELECT Descendants
   (
      [Geography].[Geography].[Country].&[United States]
    , [Geography].[Geography].[City]
    , AFTER
   ) ON ROWS
   ,
    [Measures].[Reseller Order Count] on COLUMNS
FROM [Adventure Works]

This will only return members after the selected level (Postal Code):

Option BEFORE

SELECT Descendants
   (
      [Geography].[Geography].[Country].&[United States]
    , [Geography].[Geography].[City]
    , BEFORE
   ) ON ROWS
   ,
    [Measures].[Reseller Order Count] on COLUMNS
FROM [Adventure Works]

This will return all members above the selected level, including the start member (Country, State-Province)

Option BEFORE_AND_AFTER

SELECT Descendants
   (
      [Geography].[Geography].[Country].&[United States]
    , [Geography].[Geography].[City]
    , BEFORE_AND_AFTER
   ) ON ROWS
   ,
    [Measures].[Reseller Order Count] on COLUMNS
FROM [Adventure Works]

This will return all members above and after the selected level, including the start member (Country, State-Province, Postal Code)

Option SELF_AND_AFTER

SELECT Descendants
   (
      [Geography].[Geography].[Country].&[United States]
    , [Geography].[Geography].[City]
    , SELF_AND_AFTER
   ) ON ROWS
   ,
    [Measures].[Reseller Order Count] on COLUMNS
FROM [Adventure Works]

This will return all members including and after the selected level (City, Postal Code)

Option SELF_AND_BEFORE

SELECT Descendants
   (
      [Geography].[Geography].[Country].&[United States]
    , [Geography].[Geography].[City]
    , SELF_AND_BEFORE
   ) ON ROWS
   ,
    [Measures].[Reseller Order Count] on COLUMNS
FROM [Adventure Works]

This will return all members above and including the selected level, including the start member (Country, State-Province, City)

Option SELF_BEFORE_AFTER

SELECT Descendants
   (
      [Geography].[Geography].[Country].&[United States]
    , [Geography].[Geography].[City]
    , SELF_BEFORE_AFTER
   ) ON ROWS
   ,
    [Measures].[Reseller Order Count] on COLUMNS
FROM [Adventure Works]

Will return the current member and all members on all levels below the current member, this is the same as no defined options (Country, State-Province, City, Postal Code):

Option LEAVES

This one is a bit tricky. With a level option it acts like the option SELF.

SELECT Descendants
   (
      [Geography].[Geography].[Country].&[United States]
    , [Geography].[Geography].[City]
    , LEAVES
   ) ON ROWS
   ,
    [Measures].[Reseller Order Count] on COLUMNS
FROM [Adventure Works]

This will return only members on the selected level (City):

Without a level option it will return the leaves on the lowest level of the hierarchy (Postal Code)

SELECT Descendants
   (
      [Geography].[Geography].[Country].&[United States]
    ,// [Geography].[Geography].[City]
    , LEAVES
   ) ON ROWS
   ,
    [Measures].[Reseller Order Count] on COLUMNS
FROM [Adventure Works]

Have Fun And Till Next Time