Search code examples
ssasmdx

MDX: Combining output for different criteria


I am new to MDX queries, and working in SSAS. I have two queries that are working suitably, but I want their output combined in a single result. The two queries differ in the cities selected by their where clauses, but they both come from the same cube and use the same measure.

A_to_B: Supplier city A to Consumer city B

SELECT { [Measures].[Quantity - Transactions] } ON COLUMNS,
       { [Tb Product].[Name].[Name].ALLMEMBERS } ON ROWS
FROM [Cube]
WHERE ([Tb Supplier].[City].&[A],
       [Tb Consumer].[City].&[B])

B_to_A: Supplier city B to Consumer city A

SELECT { [Measures].[Quantity - Transactions] } ON COLUMNS,
       { [Tb Product].[Name].[Name].ALLMEMBERS } ON ROWS
FROM [Cube]
WHERE ([Tb Supplier].[City].&[B],
       [Tb Consumer].[City].&[A])

Is there a way for the output of these queries to be produced side-by-side by Product, like this? In SQL I would have used a FULL OUTER JOIN, but I can't figure out the equivalent in MDX.

|          | A_to_B | B_to_A |
| ProductA |     10 |      2 |
| ProductB |    100 |      0 |
| ProductC |      0 |     99 |

Solution

  • Simply move you Where statements to columns:

    Select
    {[Measures].[Quantity - Transactions]} *
    {
        ([Tb Supplier].[City].&[B], [Tb Consumer].[City].&[A]),
        ([Tb Supplier].[City].&[A], [Tb Consumer].[City].&[B])
    } on 0,
    {[Tb Product].[Name].[Name].AllMembers} on 1
    From [Cube]
    

    You may create calculated members in order to unite two tuples:

    With 
    
    Member [Tb Supplier].[City].[B2A] as
    Aggregate([Tb Supplier].[City].&[B], [Tb Consumer].[City].&[A])
    
    Member [Tb Supplier].[City].[A2B] as
    Aggregate([Tb Supplier].[City].&[A], [Tb Consumer].[City].&[B])
    
    Select 
        {[Tb Supplier].[City].[A2B],[Tb Supplier].[City].[B2A]} on 0
    From [Cube]
    Where ([Measures].[Quantity - Transactions])