CODEWITHSUNDEEP
sqlssasmdx
Harshita Singh
Harshita Singh

Reputation: 39

To join 2 mdx queries with same hierarchy

I have 2 mdx queries as:

SELECT 
NON EMPTY { [Measures].[Sum of Sales_Value_USD], [Measures].[Sum of Sales_Value_USD Late] } ON COLUMNS, 
NON EMPTY { ([DateDimension].[DATE_QUARTER].[DATE_QUARTER].ALLMEMBERS ) } ON ROWS
FROM [Model]
where
( [DateDimension].[CurrentQuarter].&[Yes], { [ProductLine].[ProductHierarchy]
.[Product_Level5].&[PP100 - Electric Solutions], [ProductLine].[ProductHierarchy].[Product_Level5].&[PP200 - Gas Solutions], [ProductLine].[ProductHierarchy]
.[Product_Level5].&[PP300 - Water Solutions] } , { [SalesHistory].[Status].&[BACKLOG]
, [SalesHistory].[Status].&[HISTORY] } ) 



SELECT 
NON EMPTY { [Measures].[Sum of Sales_Value_USD], [Measures].[Sum of Sales_Value_USD Late] } ON COLUMNS, 
NON EMPTY { ([DateDimension].[DATE_QUARTER].[DATE_QUARTER].ALLMEMBERS ) } ON ROWS 
FROM [Model]
where 
( [DateDimension].[NextQuarter].&[Yes] , { [ProductLine].[ProductHierarchy].[Product_Level5].&[PP100 - Electric Solutions], [ProductLine].[ProductHierarchy]
.[Product_Level5].&[PP200 - Gas Solutions], [ProductLine].[ProductHierarchy]
.[Product_Level5].&[PP300 - Water Solutions] },{ [SalesHistory].[Status].&[BACKLOG]
, [SalesHistory].[Status].&[HISTORY] } )

I need to combine them into 1 result set..Please help me in achieving this objective... I am very new to MDX and does not have enough information.

Thanks

Upvotes: 2

Views: 507

Answers (1)

Delphie
Delphie

Reputation: 56

Since [DateDimension].[NextQuarter].&[Yes] and [DateDimension].[CurrentQuarter].&[Yes] are not the same hierarchies, you can not simply combine the results.

But you can create new calculated members to have all measures extracted in one MDX.

WITH MEMBER [Measures].[Sum of Sales_Value_USD CurrentQuarter] AS
    ([DateDimension].[CurrentQuarter].&[Yes], [Measures].[Sum of Sales_Value_USD])

MEMBER [Measures].[Sum of Sales_Value_USD Late CurrentQuarter] AS
    ([DateDimension].[CurrentQuarter].&[Yes], [Measures].[Sum of Sales_Value_USD Late])

MEMBER [Measures].[Sum of Sales_Value_USD NextQuarter] AS
    ([DateDimension].[NextQuarter].&[Yes], [Measures].[Sum of Sales_Value_USD])

MEMBER [Measures].[Sum of Sales_Value_USD Late NextQuarter] AS
    ([DateDimension].[NextQuarter].&[Yes], [Measures].[Sum of Sales_Value_USD Late])

SELECT 
NON EMPTY{ 
    [Measures].[Sum of Sales_Value_USD CurrentQuarter], 
    [Measures].[Sum of Sales_Value_USD Late CurrentQuarter]，
    [Measures].[Sum of Sales_Value_USD NextQuarter],
    [Measures].[Sum of Sales_Value_USD Late NextQuarter]
 } ON COLUMNS, 
NON EMPTY { ([DateDimension].[DATE_QUARTER].[DATE_QUARTER].ALLMEMBERS ) } ON ROWS 
FROM [Model]
where
({[ProductLine].[ProductHierarchy].[Product_Level5].&[PP100 - Electric Solutions], 
  [ProductLine].[ProductHierarchy].[Product_Level5].&[PP200 - Gas Solutions], 
  [ProductLine].[ProductHierarchy].[Product_Level5].&[PP300 - Water Solutions] } , 
 {[SalesHistory].[Status].&[BACKLOG], [SalesHistory].[Status].&[HISTORY] } )

Upvotes: 2

Related Questions