Parent Child heirarchy members in MDX

Question

Hi, I created a parent child hierarchy in cube. it has multiple levels of Merchants. Now, I want to display different level of merchants in different columns and that too till certain levels. here is the example:

Level 02 (root) > Level 03 > Level04 > Level 05 > Level 06 .....

I want to display :

Level 02 | Level 03 | Level 04 | Sales

I tried below but not working:

WITH

MEMBER [RootMerchant]
AS IIF(ISEMPTY([Measures].[Sales Transaction Count])
, NULL
, [Merchant].[Merchants].CURRENTMEMBER.PARENT.NAME
)
MEMBER [ChildMerchant]
AS IIF(ISEMPTY([Measures].[Sales Transaction Count])
, NULL
, [Merchant].[Merchants].CURRENTMEMBER.CHILDREN
)
SELECT
NON EMPTY {
[Measures].[Sales]
, [Measures].[RootMerchant]
, [Measures].[ChildMerchant]
} ON COLUMNS
, NON EMPTY {
( [Merchant].[Merchants].[Level 03]
)
} ON ROWS

FROM [Cube]

Answer 1

Why not create hierarchy in visual studio project, then use them in the query ?
The member in your MDX don't relate to the hierarchy relation . What is the the problem here ?

Answer 2

@KamiFen ,
Thanks for the reply. this is not typical hierarchy ( like country > State> city) . it is just parent child recursive hierarchy. Problem when I execute the code, I see parent members but not child member.

MEMBER [ChildMerchant]
AS IIF(ISEMPTY([Measures].[Sales Transaction Count])
, NULL
, [Merchant].[Merchants].CURRENTMEMBER.Children

Answer 3

@Prajwal Potula ,

Thanks for posting and welcome to Microsoft Q&A.

Regarding your questions of child members, this is because we only have one parent, but might have more then one child members, thus you might need to use aggregate function as below code:

AGGREGATE([Merchant].[Merchants].CURRENTMEMBER.CHILDREN,  
[Measures].[Sales Transaction Count])  

Thus you MDX query might be like this:

WITH  
MEMBER [RootMerchant]  
AS IIF(ISEMPTY([Measures].[Sales Transaction Count])  
, NULL  
, [Merchant].[Merchants].CURRENTMEMBER.PARENT.NAME  
)  
MEMBER [ChildMerchant]  
AS IIF(ISEMPTY([Measures].[Sales Transaction Count])  
, NULL  
, AGGREGATE([Merchant].[Merchants].CURRENTMEMBER.CHILDREN,[Measures].[Sales Transaction Count])  
)  
MEMBER [DataMemberMerchant]  
AS IIF(ISEMPTY([Measures].[Sales Transaction Count])  
, NULL  
, ([Merchant].[Merchants].CURRENTMEMBER.CHILDREN,[Measures].[Sales Transaction Count])  
)  
SELECT  
NON EMPTY {  
[Measures].[Sales]  
, [Measures].[RootMerchant]  
, [Measures].[ChildMerchant]  
, [Measures].[DataMemberMerchant]  
} ON COLUMNS  
, NON EMPTY {  
( [Merchant].[Merchants].[Level 03]  
)  
} ON ROWS  
FROM [Cube]  

See below tested code in cube AdventureWorks:

By the way, this might be an issue of MDX in SSMS , just because MDX in SSMS doesn’t support drill down function. The same MDX query in cube browser/Excel/Power BI Desktop can expand multiple levels to multiple columns, but you cannot do that in SSMS.