3

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 :)

Neo
  • 4,145
  • 6
  • 53
  • 76

1 Answers1

6

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.

Rick
  • 1,755
  • 1
  • 15
  • 22
  • 2
    Thanks! I actually found this answer in Deepak Puri's answer here: http://social.msdn.microsoft.com/Forums/sqlserver/en-US/d6e4eb06-aba4-4aa1-9c28-65f264464832 The TechNet on `IIf` (http://technet.microsoft.com/en-us/library/ms145994.aspx) only covers `[Measures]` dimensions, not non-`[Measures]` dimensions. I'm sure this is noddy stuff, but it's taken me all day to resolve this. Interestingly, using just `MemberValue` instead of `CurrentMember.MemberValue` works as well - not sure what the difference is, though. – Neo Oct 02 '13 at 17:22