MDX : Combine two role playing dimension, with multi values

Question

I'm working since few days on a MDX question and I dont see any issues...

Here is the context :

I have a fact table :

+----------+--------+-------------+------------+------------------+
| Line num | Amount |  Line Type  |    Date    | DateConfirmation |
+----------+--------+-------------+------------+------------------+
|        1 |    100 | Reservation | 01/01/2016 | 12/01/2016       |
|        2 |     50 | Reservation | 01/01/2016 | Empty            |
|        3 |     80 | Reservation | 20/12/2015 | 01/01/2016       |
|        4 |     30 | DirectSales | 01/01/2016 | 01/01/2016       |
+----------+--------+-------------+------------+------------------+

So in SSAS i have designed a cube with

Amount measure
Date dimension
Date Confirmation dimension

Then 2 date dimensions are role playing dimensions

What I need is to combine, when analysis by Date, all the reservations plus the reservations that have been confirmed at the same date of currentMember.

So i've writted this MDX :

CREATE MEMBER CURRENTCUBE.[Sales].[Type].[All].[Confirmed Reservations]
 AS NULL , 
VISIBLE = 1; 

Scope ( [Sales].[Type].[All].[Confirmed Reservations] ); 

    Scope(  MeasureGroupMeasures("Sales") , [Date].[Hierarchy].Members , [Date].[Date].Members
        , [Date Confirmation].[Hierarchy].[All] , [Date Confirmation].[Date].[All]  );   

        This =  ([Sales].[Type].&[Reservation], StrToMember("[Date Confirmation].[Hierarchy]." + Right(MemberToStr([Date].[Hierarchy].CurrentMember),  Len(MemberToStr([Date].[Hierarchy].CurrentMember)) - Len("[Date].[Hierarchy].") ) ), [Date].[Hierarchy].[All] );

    End Scope; 

End Scope;

The expected result, if I analyse the sales & reservations with the Date dimension at 01/01/2016 is

+------------------------+-----------+
| Reservation            | 150 (1+2) |
| DirectSales            | 30 (4)    |
| Confirmed Reservations | 80 (3)    |
+------------------------+-----------+

This works perfectly if I select in Excel only one date. But It produce very bad result when more than one date is select.

All your suggestions will be very helpfull for me ! Many thanks at all :)

GregGalloway GregGalloway · Accepted Answer · 2016-02-24T02:18:14

Instead of trying to tackle this in MDX I would suggest a simpler approach. If your current fact table query in the DSV is:

Select LineNum, Amount, LineType, Date, DateConfirmation
From YourFact

I would change it to:

Select LineNum, Amount, LineType, Date, DateConfirmation
From YourFact

UNION ALL

Select LineNum, Amount, 'Confirmed Reservations' as LineType, DateConfirmation as Date, DateConfirmation
From YourFact
WHERE DateConfirmation is not null

Then you shouldn't need any MDX.

MDX : Combine two role playing dimension, with multi values

1 Answers