MDX expression where dimension members become column totals

0
votes

I have data in a cube (SSAS 2014) that looks like this:

Dim1    Dim2    Dim3        Meas1
Alpha   Marco   Chicago     10
Alpha   Polo    Atlanta     15
Alpha   Marco   Chicago     17
Beta    Polo    New York    21
Beta    Marco   Atlanta     31
Beta    Polo    Chicago     41

How do I write a MDX query to get a result like this?

Dim1    Dim2    ChicagoTotl/AtlantaTotl/NewYorkTotl
Alpha   Marco       27     /    0      /    0
Alpha   Polo        0      /    15     /    0
Beta    Marco       0      /    31     /    0
Beta    Polo        41     /    0      /    21
                                            ---
                                            135

In the report Dim1 & Dim2 are going to be user selectable filters.

But how do I string Dim3 out as individual columns?

What does this MDX look like? Do columns done in this style have a name? What do you call me?

Thanks for your help.

2
ssasmdxcube

2 Answers

1
votes

You can do something like this:

WITH 
  MEMBER [Measures].[ChicagoTotal] AS 
    (
      [Measures].[Meas1]
     ,[Dim3].[Hierarchy].[Chicago]
    ) 
  MEMBER [Measures].[NewYorkTotal] AS 
    (
      [Measures].[Meas1]
     ,[Dim3].[Hierarchy].[New York]
    ) 
  MEMBER [Measures].[AtlantaTotal] AS 
    (
      [Measures].[Meas1]
     ,[Dim3].[Hierarchy].[Atlanta]
    ) 
SELECT 
  NON EMPTY 
    {
      [Measures].[ChicagoTotal]
     ,[Measures].[NewYorkTotal]
     ,[Measures].[AtlantaTotal]
    } ON 0
 ,NON EMPTY 
    [Dim1].[Hierarchy].MEMBERS * [Dim2].[Hierarchy].MEMBERS ON 1
FROM [YourCube];
1
votes

Assume aggregation type for Meas1 is Sum. Then the query will be something like this:

SELECT [Dim3].[Hier3].MEMBERS on 0,  
[Dim1].[Hier1].MEMBERS * [Dim2].[Hier2].MEMBERS on 1 
FROM [Cube] WHERE ([Measures].[Meas1])

This query will create columns automatically based on members in Dim3.Hier3.