Search code examples
ssasmdxrowsdimensions

How to display multiple dimensions on rows in MDX query?

I have measure called Sales KG in my cube and two dimensions: Groups and Formats.

Is there anyway to display last ones in single row?

I have this query:

select
[Measures].[Sales KG] on Columns,
[Formats].[Format_TT].[Format_TT] on Rows
from [Model]

and it's working, but when I try to follow examples from Internet and turn it into:

select
[Measures].[Sales KG] on Columns,
{ ([Formats].[Format_TT].[Format_TT]), ([Groups].[Group_Name].[Group_Name]) } on Rows
from [Model]

Everything ends with error saying that Elements, tuples and sets in functions must use same hierarchy.

I am new to MDX. I don't know why it works for others and no work for me. 3 days ago I didn't even knew about it's existence. enter image description here

Solution

  • It is giving you the above error because you are breaking dimensionality and hierarchility. When you write

     { ([Formats].[Format_TT].[Format_TT]),
 ([Groups].[Group_Name].[Group_Name]) }

    MDX translates that you have a set (marked by {}), containing two tuples ([Formats].[Format_TT].[Format_TT]),([Groups].[Group_Name].[Group_Name]), marked by "()" each. Now the issue is all the tuple in a SET must contain then same hierarchies(The principle of hierarchility), plus they should be in the same order (Dimensionality)

    select
[Measures].[Sales KG] on Columns,
{ ([Formats].[Format_TT].[Format_TT], [Groups].[Group_Name].[Group_Name]) } on Rows
from [Model]