How to merge two MDX query results having different measures and filters?

Question

Let's start by saying that I'm a total newbie on MDX, I need to merge two (or more) query results into one pivot.

The queries will have the same dimensions on ROWS and COLUMNS, but different measures and filters (normally a time period).

Here is an example

Query 1:

SELECT
NON EMPTY {{[stores].[storecountry].[storecountry].Members}} ON COLUMNS, 
NON EMPTY {{[SalesTypes].[Description].[Description].Members}} * 
           {[Measures].[TransactionValue], [Measures].[TransQty]} ON ROWS 
FROM [Model] 
WHERE ({[dDates].[Date].[Date].&[2016-01-05T00:00:00] : [dDates].[Date].[Date].&[2016-01-12T00:00:00]})

Result of query 1:

                                    CA    US
Regular Sale    TransactionValue    761   16
Regular Sale    TransQty            8     233
Return          TransactionValue    156   4
Return          TransQty            1     45

Query 2:

SELECT
NON EMPTY {{[stores].[storecountry].[storecountry].Members}} ON COLUMNS, 
NON EMPTY {{[SalesTypes].[Description].[Description].Members}} * 
           {[Measures].[DiscountPerc]} ON ROWS 
FROM [Model] 
WHERE ({[dDates].[Date].[Date].&[2015-03-12T00:00:00] : [dDates].[Date].[Date].&[2015-06-02T00:00:00]})

Result of query 2:

                                CA       US
Regular Sale    DiscountPerc    40 %     59 %
Return          DiscountPerc    32 %     43 %

Expected result after merging

                                    CA       US
Regular Sale    TransactionValue    761      16
Regular Sale    TransQty            8        233
Regular Sale    DiscountPerc        40 %     59 %
Return          TransactionValue    156      4
Return          TransQty            1        45
Return          DiscountPerc        32 %     43 %

Is it achievable without manually merging the AdomdClient.CellSet from the calling application?

Thank you!

Answer 1

I'd use calculated members:

with 
Member [Measures].[TransactionValueReport] as
Aggregate(
    {[dDates].[Date].[Date].&[2016-01-05T00:00:00]:[dDates].[Date].[Date].&[2016-01-12T00:00:00]},
    [Measures].[TransactionValue]
)

Member [Measures].[TransQtyReport] as
Aggregate(
    {[dDates].[Date].[Date].&[2016-01-05T00:00:00]:[dDates].[Date].[Date].&[2016-01-12T00:00:00]},
    [Measures].[TransQty]
)

Member [Measures].[DiscountPercReport] as
Aggregate(
    {[dDates].[Date].[Date].&[2015-03-12T00:00:00]:[dDates].[Date].[Date].&[2015-06-02T00:00:00]},
    [Measures].[DiscountPerc]
)

Select
Non Empty [stores].[storecountry].[storecountry].Members on 0,
Non Empty [SalesTypes].[Description].[Description].Members * {[Measures].[TransactionValueReport],[Measures].[TransQtyReport],[Measures].[DiscountPercReport]} on 1
From [Model]