MDX -- ASCENDANTS FUNCTION-- Part-9

                                     MDX -- ASCENDANTS FUNCTION-- Part-9
ASCENDANTS Function :-                                                                                                                                                                               ASCENDANTS Function is used to return/get associated Parent Member of the specified member at all levels , until it reaches LEVEL 0.

-- ASCENDANTS

  /* The ASCENDANT function will return associate Parent member of the specified member at all levels until it reaches Level 0 */

 -- EXAMPLE- 1

SELECT [Measures].[Sales Amount] ON COLUMNS

      ,ASCENDANTS (

                    [Dim Customer].[Geography Hierarchy].[English Country Region Name].& [Australia]

                  )

       ON ROWS

FROM [MSBI]

 -- EXAMPLE- 2

SELECT [Measures].[Sales Amount] ON COLUMNS

      ,ASCENDANTS (

                    [Dim Customer].[Geography Hierarchy].[English Country Region Name].&[Australia].&[New South Wales]

                  )

       ON ROWS

FROM [MSBI]

-- EXAMPLE-3

SELECT [Measures].[Sales Amount] ON COLUMNS

      ,ASCENDANTS (

                     [Dim Customer].[Geography Hierarchy].[English Country Region Name].&[Australia].&[New South Wales].&[Sydney]

                  )

       ON ROWS

FROM [MSBI]

Ascendants with ORDER Function :- 

--  ASCENDANTS WITH ORDER FUNCTION

/*  ORDER Function IS USED to reorder the members written by the Ascendants function in the Descending Order */

 -- EXAMPLE- 1

SELECT [Measures].[Sales Amount] ON COLUMNS

      ,ORDER(

                  ASCENDANTS (

                                 [Dim Customer].[Geography Hierarchy].[English Country Region Name].&[Australia].&[New South Wales].&[Sydney]

                              )

                 , DESC

            )

       ON ROWS

FROM [MSBI]

 -- EXAMPLE- 2

SELECT [Measures].[Sales Amount] ON COLUMNS

      ,ORDER(

                  ASCENDANTS (

                               [Order Date].[Calendar Hierachy].[Calendar Year].&[2006].&[2].&[4].&[December]

                             )

                 , DESC

             )

       ON ROWS

FROM [MSBI]

Ascendants with HIERARCHIZE Function :- 

-- ASCENDANTS WITH HIERARCHIZE FUNCTION

/*  Hierarchize function to reorder the members written by the Ascendants function from Top to bottom   */

 -- EXAMPLE- 1

SELECT [Measures].[Sales Amount] ON COLUMNS

      ,HIERARCHIZE (

                        ASCENDANTS (

                                     [Dim Customer].[Geography Hierarchy].[English Country Region Name].&[Australia].&[New South Wales].&[Sydney]

                                   )

                   )

       ON ROWS

FROM [MSBI]

 -- EXAMPLE- 2

SELECT [Measures].[Sales Amount] ON COLUMNS

      ,HIERARCHIZE (

                        ASCENDANTS (

                                     [Order Date].[Calendar Hierachy].[Calendar Year].&[2006].&[2].&[4].&[December]

                                   )

                   )

       ON ROWS

FROM [MSBI]

Thanks for Looking into this.....