MDX Ascendants Function

The MDX Ascendant function will return the associate Parent member of the specified member at all levels until it reaches Level 0. Or, We can say, Ascendant Function will return all the ascendants of a specified Member. For instance, you know the customer’s name. If you want to find the Post Code, City, State, and Country of that particular customer, then you can use this Ascendant function.

MDX Ascendants Function Syntax

The basic syntax of the MDX Ascendants Function in Multidimensional Expression is as shown below:

ASCENDANTS (Member_Expression)

Member_Expression: Any Multidimensional Expression that returns valid Member.

In this article, we will show you how to write Ascendants Function Query with examples. For this, we are going to use below shown hierarchy

MDX Ascendants Function Example

In this MDX example, we are going to find the City, State, and Country of the Post Code 2055

SELECT 
 [Measures].[Reseller Sales Amount] ON COLUMNS,
  ASCENDANTS ([Geography].[Geography].[Postal Code].[2055]) ON ROWS
FROM [Adventure Works];

Order Function along with Ascendants

In this example, we are going to use the MDX Order Function to reorder the members written by the Ascendants function in the Descending Order.

SELECT [Measures].[Reseller Sales Amount] ON COLUMNS,
ORDER (
   ASCENDANTS ([Geography].[Geography].[Postal Code].[2055])
  ,DESC) ON ROWS
FROM [Adventure Works];

Using Hierarchize along with Ascendants

This example uses the Hierarchize function to reorder the members written by the Ascendants function from Top to bottom.

SELECT [Measures].[Reseller Sales Amount] ON COLUMNS,
HIERARCHIZE 
  (
    ASCENDANTS ([Geography].[Geography].[Postal Code].[2055])
  ) ON ROWS
FROM [Adventure Works];

NOTE: Hierarchize Function is a very useful function to order the members written by the Ascendants rather than Order Function.