Extremely simple query (I hope) but I can't solve it

Hi, I'm new in MDX and I have a request that I think is extremely simple, but I can't make it.

I have a dimension with a hierarchy, like this

[Dim1]

[Hier_Dim1]

All

Level1

SLevel12

SLevel13

Level 2

SLevel21

SSlevel211

SSLevel212

etc.

I want the values of a measure [Measures].[Total] on the childrens of SLevel21, I write this MDX

SELECT NON EMPTY { [Measures].[Total] } ON COLUMNS

, NON EMPTY { [Dim1].[Hier_Dim1].&[SLevel21].Children} ON ROWS

FROM [My Cube]

Result Set I expect is

SSLevel211 Total1

SSLevel212 Total2

But I have this

Level1 SLevel2 SSLevel211 Total1

Level1 SLevel2 SSLevel212 Total2

I want to use result set on a report that receive as a parameter the member of hierarchy selected, so the number of columns returned should't depend on parameter selected.

Regards

Julio Diaz

What you are seeing here is a "flattened" cellset. I don't know of anyway of suppressing these extra columns that are generated when the results are "flattened" (converted from a multi-dimensional to a 2 dimensional result), but what you can do is to create a calculated member which will give you a consistant column name that you can use in your report. So in the example below you would use the [Dim1_MbrName] column in your report to get the names of the children of the selected member.

WITH MEMBER [Measures].[Dim1_MbrName] AS [Dim1].[Hier_Dim1].CurrentMember.Name

SELECT NON EMPTY { [Measures].[Dim1_MbrName],[Measures].[Total] } ON COLUMNS

, NON EMPTY { [Dim1].[Hier_Dim1].&[SLevel21].Children} ON ROWS

FROM [My Cube]

|||

Thanks Darren, It works good!

Now I′m trying to pass [Dim1].[Hier_Dim1].&[SLevel21] as a parameter, but it is another topics my question was answered :)

Regards

Julio Diaz