Alias or Group SSAS Dimension at Query time.

In an MDX Query i am trying to alias (or group ) the returned dimension as shown below but i am getting the wrong result.I believe the issue is in the case statement logic.

Is there a way to alias (or group dynamically) dimension without creating a named column in DSV?

Any help will be appreciated.

WITH MEMBER [Measures].[Long] AS

IIF(

[Measures].[Risk Value]<0,

[Measures].[Risk Value],

null)

SET [GroupedRatings] AS

CASE

WHEN [Curve Family].[SP Rating].&[AA-] THEN [Curve Family].[SP Rating].&[AA]

WHEN [Curve Family].[SP Rating].&[AA+] THEN [Curve Family].[SP Rating].&[AA]

WHEN [Curve Family].[SP Rating].&[AAA+] THEN [Curve Family].[SP Rating].&[AAA]

WHEN [Curve Family].[SP Rating].&[AAA+] THEN [Curve Family].[SP Rating].&[AAA]

WHEN [Curve Family].[SP Rating].&[BB-] THEN [Curve Family].[SP Rating].&[BB]

WHEN [Curve Family].[SP Rating].&[BB+] THEN [Curve Family].[SP Rating].&[BB]

WHEN [Curve Family].[SP Rating].&[BBB+] THEN [Curve Family].[SP Rating].&[BBB]

ELSE NULL

END

SELECT { [Measures].[Long]} ON COLUMNS,

{ ([GroupedRatings]*[Book].[Desk].[Desk].Members) } --cross join grouped rating and desk members

ON ROWS

FROM [DM]

This is where the similarities between MDX and SQL can be confusing. What you really want to do is to create some calculated members to do your grouping and then create a set of these members.

eg.

WITH MEMBER [Measures].[Long] AS

IIF(

[Measures].[Risk Value]<0,

[Measures].[Risk Value],

null)

MEMBER [Curve Family].[SP Rating].&[AA] AS Aggregate({[Curve Family].[SP Rating].&[AA-],[Curve Family].[SP Rating].&[AA+]})

MEMBER [Curve Family].[SP Rating].&[AAA] AS Aggregate({[Curve Family].[SP Rating].&[AAA-],[Curve Family].[SP Rating].&[AAA+]}

MEMBER [Curve Family].[SP Rating].&[BB] AS Aggregate({[Curve Family].[SP Rating].&[BB-], [Curve Family].[SP Rating].&[BB+]})

MEMBER [Curve Family].[SP Rating].&[BBB] AS Aggregate({[Curve Family].[SP Rating].&[BBB+]})

SET [GroupedRatings] AS

{[Curve Family].[SP Rating].&[AA]
,[Curve Family].[SP Rating].&[AAA]
,[Curve Family].[SP Rating].&[BB]
,[Curve Family].[SP Rating].&[BBB]}

SELECT { [Measures].[Long]} ON COLUMNS,

{ ([GroupedRatings]*[Book].[Desk].[Desk].Members) } --cross join grouped rating and desk members

ON ROWS

FROM [DM]

|||

The case statement won't create new members dynamically, which it looks like you're trying to do. You could declare each member explicitly, like:

WITH MEMBER [Measures].[Long] AS

IIF(

[Measures].[Risk Value]<0,

[Measures].[Risk Value],

null)

Member [Curve Family].[SP Rating].[AA] as

Sum({[Curve Family].[SP Rating].&[AA-], [Curve Family].[SP Rating].&[AA+]}),

SOLVE_ORDER = 10

Member [Curve Family].[SP Rating].[AAA] as

Sum({[Curve Family].[SP Rating].&[AAA-], [Curve Family].[SP Rating].&[AAA+]}),

SOLVE_ORDER = 10

Member [Curve Family].[SP Rating].[BB] as

Sum({[Curve Family].[SP Rating].&[BB-], [Curve Family].[SP Rating].&[BB+]}),

SOLVE_ORDER = 10

Member [Curve Family].[SP Rating].[BBB] as

Sum({[Curve Family].[SP Rating].&[BBB-], [Curve Family].[SP Rating].&[BBB+]}),

SOLVE_ORDER = 10

SET [GroupedRatings] AS

{[Curve Family].[SP Rating].[AA], [Curve Family].[SP Rating].[AAA],

[Curve Family].[SP Rating].[BB], [Curve Family].[SP Rating].[BBB]}

SELECT { [Measures].[Long]} ON COLUMNS,

{ ([GroupedRatings]*[Book].[Desk].[Desk].Members) } --cross join grouped rating and desk members

ON ROWS

FROM [DM]

|||

Thanks Darren for pointing me in the right direction.I changed the code to the sample below to make it work properly.

WITH

MEMBER [Curve Family].[SP Rating].[AA] AS

Aggregate({FILTER([Curve Family].[SP Rating].&[AA-],([Measures].[Risk Value])<0),FILTER([Curve Family].[SP Rating].&[AA+],([Measures].[Risk Value])<0)})

MEMBER [Curve Family].[SP Rating].[AAA] AS

Aggregate({FILTER([Curve Family].[SP Rating].&[AAA-],([Measures].[Risk Value])<0),FILTER([Curve Family].[SP Rating].&[AAA+],([Measures].[Risk Value])<0)})

MEMBER [Curve Family].[SP Rating].[BB] AS

Aggregate({FILTER([Curve Family].[SP Rating].&[BB-],([Measures].[Risk Value])<0),FILTER([Curve Family].[SP Rating].&[BB+],([Measures].[Risk Value])<0)})

MEMBER [Curve Family].[SP Rating].[BBB]AS

Aggregate({FILTER([Curve Family].[SP Rating].&[BBB-],([Measures].[Risk Value])<0),FILTER([Curve Family].[SP Rating].&[BBB+],([Measures].[Risk Value])<0)})

SET [GroupedRatings] AS

{

[Curve Family].[SP Rating].[AA]

,[Curve Family].[SP Rating].[AAA]

,[Curve Family].[SP Rating].[BB]

,[Curve Family].[SP Rating].[BBB]

}

SELECT

NON EMPTY { [Measures].[Risk Value]} ON COLUMNS,

NON EMPTY {([GroupedRatings]*[Vdim Book].[Desk].[Desk].Members)} ON ROWS

FROM

[DM]