Search code examples
ssasmdxolap

MDX IIF evaluation of string value

another MDX question;

as stated in this question MDX If statement syntax

i have a value of type string that holds a 4 digit number. i would like to evaluate the first number or character of that string

so what i do is this:

IIF(([Kostensoort].[Kosten code] >= 7000 AND [Kostensoort].[Kosten code] < 8000), 
[Measures].[Inkoop bedrag] + [Measures].[Bank bedrag], 
[Measures].[Inkoop bedrag])

what i suspect to happen, according to the documentation is the following;

syntax: IIF(Evalutation, true expression, false expression)

what actually happens is that the evaluation returns false in what ever way i try to write the expression. i know this, because the value gets printed. if i enter a static number, that also gets printed in my cube.

yes, i do know that im intepreting the value of Kosten code as a integer even though its a string, i tried using Instr and Left too, which yielded no results either.

i'm kind off at a loss here on what to do. Any insights are welcome

Solution

  • You need to make sure you are grabbing the currentmember of your dimension. Your example is likely returning "All"

    [Kostensoort].[Kosten code].currentMember

    Once you change that you can use StrToValue.

    IIF((StrToValue([Kostensoort].[Kosten code].currentMember) >= 7000 AND [Kostensoort].[Kosten code] < 8000), 
[Measures].[Inkoop bedrag] + [Measures].[Bank bedrag], 
[Measures].[Inkoop bedrag])