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]
