Tutorial Gateway

  • C
  • C#
  • Java
  • Python
  • SQL
  • MySQL
  • Js
  • BI Tools
    • Informatica
    • Talend
    • Tableau
    • Power BI
    • SSIS
    • SSRS
    • SSAS
    • MDX
    • R Tutorial
    • Alteryx
    • QlikView
  • More
    • C Programs
    • C++ Programs
    • Go Programs
    • Python Programs
    • Java Programs

MDX Ascendants Function

by suresh

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

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];
MDX ASCENDANTS FUNCTION 1

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];
MDX ASCENDANTS FUNCTION 2

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];
MDX ASCENDANTS FUNCTION 3

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

Placed Under: MDX

  • MDX Ascendants Function
  • MDX AVG Function
  • MDX BottomCount Function
  • MDX BottomPercent Function
  • MDX BottomSum Function
  • MDX Children Function
  • MDX ClosingPeriod Function
  • MDX COUSIN FUNCTION
  • MDX FirstChild Function
  • MDX FirstSibling Function
  • MDX Head Function
  • MDX LastChild Function
  • MDX LastSibling Function
  • MDX LastPeriods Function
  • MDX LAG Function
  • MDX LEAD Function
  • MDX NextMember Function
  • MDX NONEMPTY Function
  • MDX NON EMPTY
  • MDX OpeningPeriod Function
  • MDX ParallelPeriod Function
  • MDX PeriodsToDate Function
  • MDX PrevMember Function
  • MDX Parent Function
  • MDX Siblings Function
  • MDX Tail Function
  • MDX TOPSUM Function
  • MDX TopPercent Function
  • MDX TopCount Function

Copyright © 2021 · All Rights Reserved by Suresh

About Us | Contact Us | Privacy Policy