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 simply say, Ascendant Function will return all the ascendants of a specified Member. For instance, you Know the customer name and 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 in MDX Query with examples. For this we are going to use below shown hierarchy

MDX Ascendants Function Example

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

MDX CODE

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

OUTPUT

Order Function along with MDX 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.

MDX CODE

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

OUTPUT

Using Hierarchize along with MDX Ascendants

In this example we are going to use the MDX Hierarchize function to reorder the members written by the Ascendants function from Top to bottom.

MDX CODE

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

OUTPUT

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

Thank you for Visiting Our Blog