I am trying to produce a different output in a column dependent on whether the value of a dimension [Scenario].[Option]
is +5
which I've tried to achieve using the IIf
function.
WITH MEMBER [XorY] AS
(
IIf([Scenario].[Option] = +5, 'X', 'Y')
--IIf([Scenario].[Option].&[+5], 'X', 'Y')
)
SELECT
NON EMPTY
(
[Scenario].[Option].[Option]
) ON ROWS,
NON EMPTY
(
[XorY]
) ON COLUMNS
FROM [RePro]
WHERE
(
[ABC].[ABC].[Val]
) CELL PROPERTIES VALUE
However, using either of the IIf
statements as above, [XorY]
is always Y
regardless of the value of [Scenario].[Option]
. It seems the comparison I'm doing is just syntactically wrong or something. How do I do this? I've noticed it works much better if I use a calculated member in a [Measures]
dimension in the IIf
condition, but that is not possible in my case - I must use [Scenario].[Option]
.
Thanks for any help received :)
When you use a member statement, you generally want to use currentmember, as it references the current cell. Combine that with membervalue and you get the value. In this case I then wrapped the expected answer in quotes.
WITH MEMBER [XorY] AS
(
IIf([Scenario].[Option].currentMember.membervalue = "+5", 'X', 'Y')
)
If you are comparing to a number you would do:
WITH MEMBER [XorY] AS
(
IIf(STRTOVALUE([Scenario].[Option].currentMember.membervalue) = 5, 'X', 'Y')
)
This can return an error if not all of the members in [Scenario].[Option] are numbers. There is also a potential performance issue with the conversion.