I have to write MDX that will be displayed on column and is dividing rows into three groups. First group is distinguished by few numbers, second one is by attribute, and third group is where rest doesn't fit.
My code looks like that so far:
case
when [Customer].[Customer's Document].CURRENTMEMBER.MEMBERVALUE = "4254255527" then "ABC"
when [Customer].[Customer's Document].CURRENTMEMBER.MEMBERVALUE = "2752637520" then "ABC"
when [Customer].[Customer's Document].CURRENTMEMBER.MEMBERVALUE = "5637839739" then "ABC"
when [Customer].[Customer's Document].CURRENTMEMBER.MEMBERVALUE = "9378793737" then "ABC"
when [Customer].[Customer's Document].CURRENTMEMBER.MEMBERVALUE = "3789789397" then "ABC"
when [Document].[Document series].CURRENTMEMBER.MEMBERVALUE = "XYZ" then "XYZ"
else "Rest"
end
But I'm getting "Rest" everytime.
How should I correct that?
Edit: Another try but still not working:
case
when [Customer].[Customer's Document].[&5196189651] then "ABC"
when [Customer].[Customer's Document].[&7885181585] then "ABC"
when [Customer].[Customer's Document].[&7511535861] then "ABC"
when [Customer].[Customer's Document].[&4742575277] then "ABC"
when [Customer].[Customer's Document].[&7272727272] then "ABC"
when [Customer's Document].[Document Series].[&CHP] then "XYZ"
else "Rest"
end
I get the feeling you want to do something more like the following:
WITH
SET [ABC] AS
{
[Customer].[Customer's Document].&[5196189651]
,[Customer].[Customer's Document].&[7885181585]
,[Customer].[Customer's Document].&[7511535861]
,[Customer].[Customer's Document].&[4742575277]
,[Customer].[Customer's Document].&[7272727272]
}
MEMBER [Customer].[All].[ABC] AS
Aggregate([ABC])
MEMBER [Customer].[All].[XYZ] AS
[Customer].[Customer's Document].[Document Series].&[CHP]
SET [REST] AS
Except
(
[Customer].[Customer's Document].MEMBERS
,[ABC]
)
MEMBER [Customer].[All].[Rest] AS
Aggregate([REST])
SET [FINAL] AS
{
[Customer].[All].[ABC]
,[Customer].[All].[XYZ]
,[Customer].[All].[Rest]
}
SELECT
[FINAL] ON 1
,{[Measures].[Amount]} ON 0
FROM [YourCube];
Or maybe the following:
WITH
SET [ABC] AS
{
[Customer].[Customer's Document].&[5196189651]
,[Customer].[Customer's Document].&[7885181585]
,[Customer].[Customer's Document].&[7511535861]
,[Customer].[Customer's Document].&[4742575277]
,[Customer].[Customer's Document].&[7272727272]
}
MEMBER [Customer].[All].[ABC] AS
Aggregate([ABC])
MEMBER [Customer].[All].[XYZ] AS
[Customer].[Customer's Document].[Document Series].&[CHP]
SET [REST] AS
Except
(
[Customer].[Customer's Document].MEMBERS
,{
[ABC]
,[Customer].[Customer's Document].[Document Series].&[CHP]
}
)
MEMBER [Customer].[All].[Rest] AS
Aggregate([REST])
SET [FINAL] AS
{
[Customer].[All].[ABC]
,[Customer].[All].[XYZ]
,[Customer].[All].[Rest]
}
SELECT
[FINAL] ON 1
,{[Measures].[Amount]} ON 0
FROM [YourCube];
Edit
Just a warning - in the accepted answer there is the following mdx
:
case
when [Customer].[Customer's Document].CURRENTMEMBER.MEMBERVALUE = "4254255527" then "ABC"
when [Customer].[Customer's Document].CURRENTMEMBER.MEMBERVALUE = "2752637520" then "ABC"
when [Customer].[Customer's Document].CURRENTMEMBER.MEMBERVALUE = "5637839739" then "ABC"
...
...
This might be marked as the solution but it's not great mdx
. In this circumstance the IS
operator should be used:
case
when [Customer].[Customer's Document].CURRENTMEMBER IS
[Customer].[Customer's Document].[Customer's Document].&[4254255527] then "ABC"
when [Customer].[Customer's Document].CURRENTMEMBER IS
[Customer].[Customer's Document].[Customer's Document].&[2752637520] then "ABC"
when [Customer].[Customer's Document].CURRENTMEMBER IS
[Customer].[Customer's Document].[Customer's Document].&[5637839739] then "ABC"
...
...