Search code examples
ssasmdxcube

MDX grouped dimensions

I have a dimension [product].[type] having its member containing 'Food','Book','Metal','Meat', etc. My task is to show a categorized dimension to group my sales value, such as, group Food & Meat as 0, Metal & Tool as 1, Book as 2, etc..

My Query seems doesn't work:

WITH calculated member [Measures].[Grouped Type] AS
  IIF([product].[type].CurrentMember IS [product].[type].[Meat], 0,
  IIF([product].[type].CurrentMember IS [product].[type].[Food], 0,
  IIF([product].[type].CurrentMember IS [product].[type].[Tool], 1,
  ...... , 9)))))
SELECT {[Measures].[Sales Amount], [Measures].[Grouped Type].Children} on 0
FROM Cube
WHERE ([Condition])

It looks my total sales amount is shown, but not correctly categorized. any help? Appreciated.

Solution

  • Try calculated members against the product dimension, not measure's one:

    With
Member [product].[type].[0] as
Sum({[product].[type].[Meat],[product].[type].[Food]})

Member [product].[type].[1] as
Sum({[product].[type].[Tool]})

select 
[Measures].[Sales Amount] on 0,
{[product].[type].[0],[product].[type].[1]} on 1
From Cube
Where ([Condition])