Parent Child heirarchy members in MDX

Prajwal Potula 1 Reputation point
2021-01-25T22:15:05.563+00:00

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]

SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,249 questions
{count} votes

3 answers

Sort by: Most helpful
  1. KamiFen 121 Reputation points
    2021-01-26T09:41:52.757+00:00

    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 ?

    0 comments No comments

  2. Prajwal Potula 1 Reputation point
    2021-01-26T21:39:12.333+00:00

    @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

    0 comments No comments

  3. Willsonyuan-MSFT 111 Reputation points
    2021-01-27T03:15:34.81+00:00

    @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:

    60748-mdx.jpg

    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.

    0 comments No comments