Parameter All Level

Hello,

I'm using SQL Server Reporting Services with an Analysis Services Cube. My current report has a query based parameter. The query returns country codes and the ALL-Level of my country dimension.

Is there a way to remove the ALL-Level of the result set of the query? How can I realize this?

Thanks in advance.

Christian

Hi,

I suggest to change the query. I think, your generated query looks like:

WITH MEMBER [Measures].[ParameterCaption] AS '[Country].[Country].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Country].[Country].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Country].[Country].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Country].[Country].ALLMEMBERS ON ROWS FROM [MyCube]

Change it to:

WITH MEMBER [Measures].[ParameterCaption] AS '[Country].[Country].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Country].[Country].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Country].[Country].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , Descendants([Country].[Country], [Country].[yourLevel1name]) ON ROWS FROM [MyCube]

For further help, look at the Descendants Function in MDX.

Hans