none
How to display all members in unbalanced parent-child hierarchy

    Question

  • Hello,

    I have a dimension name: Account, and a hierarchy name: Parent Account COA. It is an unbalanced hierarchy.
    The draft is like this:

    AccountCOA
       Liability and Equity
          Liability
             Current Liability
             Long-term Liability
          Equity
             Paid-in Capital
             Profit and Loss
       Assets
          Fixed Assets
          Current Assets

    Level1: AccountCOA
    Level2: Liability and Equity, Assets
    Level3: Liability, Equity, Fixed Assets, Current Assets
    Level4: Current Liability, Long-term Liability, Paid-in Capital, Profit and Loss

    I want to display all of the members in different column for each level, and its amount like this:

    Lvl1        Lvl2            Lvl3        Lvl4            Amount
    AccountCOA    Liability and Equity    Liability    Current Liability    10
    AccountCOA    Liability and Equity    Liability    Long-term Liability    10
    AccountCOA    Liability and Equity    Equity        Paid-in Capital        10
    AccountCOA    Liability and Equity    Equity        Profit and Loss        10
    AccountCOA    Assets            Fixed Assets                10
    AccountCOA    Assets            Current Assets                10

    I use calculated member
    WITH
    MEMBER [Measures].[Level3] AS [Account].[Parent Account COA].Parent.MEMBER_CAPTION
    MEMBER [Measures].[Level2] AS [Account].[Parent Account COA].Parent.Parent.MEMBER_CAPTION
    MEMBER [Measures].[Level1] AS [Account].[Parent Account COA].Parent.Parent.Parent.MEMBER_CAPTION

    to display the parent of Level4, etc
    SELECT {[Measures].[Level1], [Measures].[Level2], [Measures].[Level3], [Measures].[Amount]} ON COLUMNS,
    {[Account].[Parent Account COA].Levels(4).Members} ON ROWS
    FROM [Cube]

    However, the query result cannot display members who do not have members on lvl4, like Fixed Assets and Current Assets.
    Please let me know if you know any solution for this. Thank you...
    Monday, June 18, 2012 7:02 AM

Answers

  • Thank you, Darren for answering. I just found the answer, by using

    WITH

    MEMBER [Measures].[Level2] AS ANCESTOR([Account].[Parent Account COA].CurrentMember, [Level2]).Name

    ...

    DESCENDANTS([Account].[Parent Account COA].Hierarchy.CurrentMember,,LEAVES) ON ROWS

    :)

    Wednesday, June 20, 2012 9:36 AM

All replies

  • Just selecting the members of the hierarchy instead of just level 4 on the rows should work.

    [Account].[Parent Account COA].Members


    http://darren.gosbell.com - please mark correct answers

    • Proposed as answer by Eileen Zhao Tuesday, June 19, 2012 5:21 AM
    Tuesday, June 19, 2012 1:29 AM
  • Thank you, Darren for answering. I just found the answer, by using

    WITH

    MEMBER [Measures].[Level2] AS ANCESTOR([Account].[Parent Account COA].CurrentMember, [Level2]).Name

    ...

    DESCENDANTS([Account].[Parent Account COA].Hierarchy.CurrentMember,,LEAVES) ON ROWS

    :)

    Wednesday, June 20, 2012 9:36 AM