none
Combine different hierarchy levels using MDX Query

    Question

  • 1.Description

    Now I have two mdx query, the only differences is the ON ROWS settings.

    Query 1 : (get data using"[PLOwner].[PLOwner].Members" rows)

    SELECT
    
    NON EMPTY
    {
      [PLOwner].[PLOwner].Members
    }
    ON ROWS
    ,
    NON EMPTY
    Crossjoin(
      {
        [InfoType].[InfoType].[Risk_RecoveryJTDTable],
        [InfoType].[InfoType].[Equivalent_Notional],
        [InfoType].[InfoType].[Risk_SPC],
        [InfoType].[InfoType].[Risk_PSM],
        [InfoType].[InfoType].[Risk_PV10],
        [InfoType].[InfoType].[Notional],
        [InfoType].[InfoType].[Notional_IMM],
        [InfoType].[InfoType].[PnL],
        [InfoType].[InfoType].[TomorrowPnL],
        [InfoType].[InfoType].[RollPnL],
        [InfoType].[InfoType].[RollDownPnL],
        [InfoType].[InfoType].[Risk_JTD],
        [InfoType].[InfoType].[Risk_Raw_KC],
        [InfoType].[InfoType].[Risk_RR],
        [InfoType].[InfoType].[FundingPnLWCOF],
        [InfoType].[InfoType].[FundingPnLRR],
        [InfoType].[InfoType].[FundingPnLSHW],
        [InfoType].[InfoType].[FundingPnLBox],
        [InfoType].[InfoType].[FundingPnLInterest]
      }
      ,
      {
        DrillDownLevel([Category].[ALL].[AllMember])
      }
      ,
      {
        DrillDownLevel([Label1].[ALL].[AllMember])
      }
      ,
      {
        DrillDownLevel([IsError].[ALL].[AllMember])
      }
    )
    ON COLUMNS
    
    FROM
      [UnityRiskCube]
    
    WHERE
    (
      [Measures].[Risk.SUM],
      [BusinessGroup].[BusinessGroup].[AeJ Flow Credit],
      [Context].[ContextId].[official:Live]
    )

    Result 1: (I use Excel to show String[][] result from Pivot)

    


    Ankit Garg

    -9.84E+07

    Eric Pang

    3.60E+08

    Ichiro Fujisawa

    -1.05E+08

    James Martin

    0

    Lip Jin Lee

    -1.78E+08

    Rahul Gupta

    -1115342

    Ruan Wenjun

    -6.56E+07

    Sandeep Aggarwal

    -5.94E+07

    Varun Varshney

    -8.75E+07

    Wentao Yan

    -1.61E+07

    William Mak

    -3.51E+08


    Query 2 : (get data using"Crossjoin{[PLOwner].[PLOwner].[Ankit Garg] }, {[PLGroup].[PLGroup].Members}" rows)

    SELECT
    
    NON EMPTY
    Crossjoin(
      {
        [PLOwner].[PLOwner].Members
      }
      ,
      {
        [PLGroup].[PLGroup].Members
      }
    )
    ON ROWS
    ,
    NON EMPTY
    Crossjoin(
      {
        [InfoType].[InfoType].[Risk_RecoveryJTDTable],
        [InfoType].[InfoType].[Equivalent_Notional],
        [InfoType].[InfoType].[Risk_SPC],
        [InfoType].[InfoType].[Risk_PSM],
        [InfoType].[InfoType].[Risk_PV10],
        [InfoType].[InfoType].[Notional],
        [InfoType].[InfoType].[Notional_IMM],
        [InfoType].[InfoType].[PnL],
        [InfoType].[InfoType].[TomorrowPnL],
        [InfoType].[InfoType].[RollPnL],
        [InfoType].[InfoType].[RollDownPnL],
        [InfoType].[InfoType].[Risk_JTD],
        [InfoType].[InfoType].[Risk_Raw_KC],
        [InfoType].[InfoType].[Risk_RR],
        [InfoType].[InfoType].[FundingPnLWCOF],
        [InfoType].[InfoType].[FundingPnLRR],
        [InfoType].[InfoType].[FundingPnLSHW],
        [InfoType].[InfoType].[FundingPnLBox],
        [InfoType].[InfoType].[FundingPnLInterest]
      }
      ,
      {
        DrillDownLevel([Category].[ALL].[AllMember])
      }
      ,
      {
        DrillDownLevel([Label1].[ALL].[AllMember])
      }
      ,
      {
        DrillDownLevel([IsError].[ALL].[AllMember])
      }
    )
    ON COLUMNS
    
    FROM
      [UnityRiskCube]
    
    WHERE
    (
      [Measures].[Risk.SUM],
      [BusinessGroup].[BusinessGroup].[AeJ Flow Credit],
      [Context].[ContextId].[official:Live]
    )

    Result 2: (I use Excel to show String[][] result from Pivot)

    Ankit Garg:Hong Kong IG

    -5102963

    Ankit Garg:IG Greater China Corps

    -9.33E+07

    Eric Pang:Australia

    1.13E+07

    Eric Pang:Sovereigns

    3.49E+08

    Ichiro Fujisawa:Australia

    -2.05E+07

    Ichiro Fujisawa:Japan G3 Credit

    -8.30E+07

    Ichiro Fujisawa:Unused UF

    0

    James Martin:Repo

    0

    Lip Jin Lee:IG Greater China CNH

    0

    Lip Jin Lee:IG Greater China Fins

    -1.04E+07

    Lip Jin Lee:IG Korea/Others

    -1.39E+08

    Lip Jin Lee:Sovereigns

    -2.80E+07

    Lip Jin Lee:Unused UF

    0

    Rahul Gupta:High Yield

    -1115342

    Ruan Wenjun:IG Greater China

    -6.56E+07

    Sandeep Aggarwal:HY Management

    -2.49E+07

    Sandeep Aggarwal:IG Mgmt

    -1.94E+07

    Sandeep Aggarwal:Macro

    -1635149

    Sandeep Aggarwal:Management

    -1.35E+07

    Sandeep Aggarwal:Unused UF

    0

    Varun Varshney:High Yield

    -4.22E+07

    Varun Varshney:IG India

    -3.18E+07

    Varun Varshney:Quasi Sov/HY

    -1.35E+07

    Varun Varshney:Unused UF

    0

    Wentao Yan:HY China

    6.33E+07

    Wentao Yan:Unused UF

    -7.75E+07

    William Mak:Australia

    -2.82E+08

    William Mak:IG Greater China

    -6.90E+07

    William Mak:Unused UF

    0



    2.Question

    How can i query these data set together using one Mdx query? Thanks a lot.

    • Changed type Xun Cui Thursday, May 17, 2018 8:17 AM
    Thursday, May 17, 2018 8:16 AM

All replies

  • There is mistake in "Query 2 : (get data using"Crossjoin{[PLOwner].[PLOwner].[Ankit Garg] }, {[PLGroup].[PLGroup].Members}" rows)" . It is not [PLOwner].[PLOwner].[Ankit Garg], it is [PLOwner].[PLOwner].Members.
    Thursday, May 17, 2018 8:22 AM
  • Hi Xun Cui,

    Thanks for your question.

    >>>How can i query these data set together using one Mdx query? Thanks a lot.
    Please refer to a similar thread answered by me:
    Combine 2 mdx queries


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Proposed as answer by alexander fun Thursday, May 17, 2018 9:10 PM
    Thursday, May 17, 2018 9:34 AM
    Moderator