Learn MDX with Me – Part - 6

Introduction

Now we are continuing our journey of MDX. Now we are trying to drill down more on MDX Query. Hope the session is very interesting.

Understanding Parent function

Parent function represents the Parent of the current member. To understand it, we are creating a new Hierarchy with the name of [CallenderHierarchy] 

Now try to see a member of Calendar Hierarchy

SELECT {[Measures].[Qty Sold],

        [Measures].[Sales Rate],

        [Measures].[Calculated Sales Amout]} on Columns,

        [Dim Time].[CallenderHierarchy].[Year Name].&[Calendar 2017].

           &[Quarter 1, 2017].&[January 2017] on Rows

FROM   [CUBESales]

Output:

	Qty Sold	Sales Rate	Calculated Sales Amout
Jan-17	80	900	35000

SELECT {[Measures].[Qty Sold],

        [Measures].[Sales Rate],

        [Measures].[Calculated Sales Amout]} on Columns,

        [Dim Time].[CallenderHierarchy].[Year Name].&[Calendar 2017].

           &[Quarter 1, 2017].&[January 2017].parent on Rows

FROM   [CUBESales]

Output:

	Qty Sold	Sales Rate	Calculated Sales Amout
Quarter 1, 2017	100	1200	38000

SELECT {[Measures].[Qty Sold],

        [Measures].[Sales Rate],

        [Measures].[Calculated Sales Amout]} on Columns,

        [Dim Time].[CallenderHierarchy].[Year Name].&[Calendar 2017].

           &[Quarter 1, 2017].&[January 2017].parent.parent on Rows

FROM   [CUBESales]

Output:

	Qty Sold	Sales Rate	Calculated Sales Amout
Calendar 2017	100	1200	38000

Ancestors Function

It is quite difficult to use parent function because if we have lot of Hierarchy Levels. Suppose we have 9 Hierarchy levels and the members of the last Hierarchy Level we want to move the top level… we just do code like that

[Members of Last Level].Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent

So we have another function named Ancestors. It tales the current member and the level number that we need to see.

SELECT {[Measures].[Qty Sold],

        [Measures].[Sales Rate],

        [Measures].[Calculated Sales Amout]} on Columns,

        Ancestors([Dim Time].[CallenderHierarchy].[Year Name].&[Calendar 2017].

           &[Quarter 1, 2017].&[January 2017], 2) on Rows

FROM   [CUBESales]

Output:

	Qty Sold	Sales Rate	Calculated Sales Amout
Calendar 2017	100	1200	38000

SELECT {[Measures].[Qty Sold],

        [Measures].[Sales Rate],

        [Measures].[Calculated Sales Amout]} on Columns,

        Ancestors([Dim Time].[CallenderHierarchy].[Year Name].

            &[Calendar 2017].&[Quarter 1, 2017], 1) on Rows

FROM   [CUBESales]

Output:

	Qty Sold	Sales Rate	Calculated Sales Amout
Calendar 2017	100	1200	38000

Instead of Level 1,2,3 we can directly use the Level name also.

SELECT {[Measures].[Qty Sold],

        [Measures].[Sales Rate],

        [Measures].[Calculated Sales Amout]} on Columns,

        Ancestors([Dim Time].[CallenderHierarchy].[Year Name].

            &[Calendar 2017].&[Quarter 1, 2017],

            [Dim Time].[CallenderHierarchy].[Year Name]) on Rows

FROM   [CUBESales]

Output:

	Qty Sold	Sales Rate	Calculated Sales Amout
Calendar 2017	100	1200	38000

Ascendants Function

The Ascendants function returns all of the ancestors of a member from the member itself up to the top of the member’s hierarchy; more specifically, it performs a post-order traversal of the hierarchy for the specified member, and then returns all ascendant members related to the member, including itself, in a set. This is in contrast to the Ancestor function, which returns a specific ascendant member, or ancestor, at a specific level.

Try this

SELECT {[Measures].[Qty Sold],

        [Measures].[Sales Rate],

        [Measures].[Calculated Sales Amout]} on Columns,

        ascendants([Dim Time].[CallenderHierarchy].[Year Name].&[Calendar 2017].

           &[Quarter 1, 2017].&[January 2017]) on Rows

FROM   [CUBESales]

	Qty Sold	Sales Rate	Calculated Sales Amout
Jan-17	80	900	35000
Quarter 1, 2017	100	1200	38000
Calendar 2017	100	1200	38000
All	100	1200	38000

Finding Brothers and Sister of Specified members

We have to find the current members Parent first and then find the children of the parents.

   

     Finding Parent

SELECT {[Measures].[Qty Sold],

        [Measures].[Sales Rate],

        [Measures].[Calculated Sales Amout]} on Columns,

        [Dim Time].[CallenderHierarchy].[Year Name].&[Calendar 2017].

           &[Quarter 1, 2017].&[January 2017].parent on Rows

FROM   [CUBESales]

Output:

	Qty Sold	Sales Rate	Calculated Sales Amout
Quarter 1, 2017	100	1200	38000

  

  Finding Children of the Parent

SELECT {[Measures].[Qty Sold],

        [Measures].[Sales Rate],

        [Measures].[Calculated Sales Amout]} on Columns,

        [Dim Time].[CallenderHierarchy].[Year Name].&[Calendar 2017].

           &[Quarter 1, 2017].&[January 2017].parent.children on Rows

FROM   [CUBESales]

Output:

	Qty Sold	Sales Rate	Calculated Sales Amout
Feb-17	20	300	3000
Jan-17	80	900	35000
Mar-17	(null)	(null)	(null)

Siblings Function

It is the same output as Parent.Children function

SELECT {[Measures].[Qty Sold],

        [Measures].[Sales Rate],

        [Measures].[Calculated Sales Amout]} on Columns,

        [Dim Time].[CallenderHierarchy].[Year Name].&[Calendar 2017].

           &[Quarter 1, 2017].&[January 2017].siblings on Rows

FROM   [CUBESales]

Output:

	Qty Sold	Sales Rate	Calculated Sales Amout
Feb-17	20	300	3000
Jan-17	80	900	35000
Mar-17	(null)	(null)	(null)

This learning session will be continued. Please make your interest by commenting it.

Posted by: MR. JOYDEEP DAS