Grouping in MDX Query

Question

I am very newbie to MDX world..

I want to group the Columns based on only 3 rows. But, need join for 4th row also.. My query is :

SELECT 
 (    { 
   [Measures].[Live Item Count] 
          } 
 ) DIMENSION PROPERTIES parent_unique_name ON COLUMNS, 


 Crossjoin(
 crossjoin(

  [Item].[Class].&[Light],
  [Item].[Style].&[Fav]
  [Item].[Season Year].members),
  [Item].[Count].children )    on rows
FROM Cube

Output comes as :

Light(Row) | FAV(Row) | ALL(Row)  | 16(Row) | 2(col)
Light(Row) | FAV(Row) | ALL(Row)  | 7(Row)  | 1(col)
Light(Row) | FAV(Row) | 2012(Row) | 16(Row)| 2(col)
Light(Row) | FAV(Row) | 2011(Row) | 7(Row) | 1(col)

But, I want my output to be displayed as:

Light(Row) | FAV(Row) | ALL(Row)  |        | 3(col)
Light(Row) | FAV(Row) | 2012(Row) | 16(Row)| 2(col)
Light(Row) | FAV(Row) | 2011(Row) | 7(Row) | 1(col)

i.e., I want to group my first two rows such that there is no duplicate 'ALL' in 3rd column..

Thanks in advance

score 0 · Answer 1 · answered Aug 03 '12 at 21:57

Try this - using the level name Season Year with the Attribute name Season Year will pick up every member without teh ALL member:

SELECT 
 (    { 
   [Measures].[Live Item Count] 
          } 
 ) DIMENSION PROPERTIES parent_unique_name ON COLUMNS, 


 Crossjoin(
 crossjoin(

  [Item].[Class].&[Light],
  [Item].[Style].&[Fav]
  [Item].[Season Year].[Season Year].members),
  [Item].[Count].children )    on rows
FROM Cube

score 0 · Answer 2 · answered Aug 10 '12 at 21:31

You can use this query if there is an All member on the [Item].[Count] hierarchy:

SELECT {[Measures].[Live Item Count]} DIMENSION PROPERTIES parent_unique_name ON COLUMNS, 
Crossjoin(
  Crossjoin([Item].[Class].&[Light], [Item].[Style].&[Fav]),
  Union(
    Crossjoin({"All member of [Item].[Season Year]"}, {"All member of [Item].[Count]"}),
    Crossjoin(Except([Item].[Season Year].members, {"All member of [Item].[Season Year]"}), [Item].[Count].children),
  ) ON ROWS
FROM Cube

Grouping in MDX Query

2 Answers2