Search code examples
ssasmdxolap

MDX If statement syntax

I'm working on a calculated measure in SQL Server Analysis Service. the case is as follows;

we have a string value that holds a 4 digit numeric value. if the value starts with 7 we want to add a value with another value, if not then we just want to display the value.

i tried to use a when statement first, with a then and an else portion;

Case

    when (Left([Kostensoort].[Kosten code], 1) is "7")
        Then [Measures].[Inkoop bedrag] + [Measures].[Bank bedrag]
        Else [Measures].[Inkoop bedrag]
End

this doesn't seem to work.

i then changed it to an if statement like this;

if  (Left([Kostensoort].[Kosten code], 1) is "7")
    then [Measures].[Inkoop bedrag] + [Measures].[Bank bedrag]
    else [Measures].[Inkoop bedrag]
end if

it keeps saying there is an error at the then keyword, which leads me to believe i did something wrong with the if statement

i also replaced the is with a '=' to see if that mattered, which it didn't

i found multiple sources on the internet saying what i can and can't do in a mdx expression;

http://msdn.microsoft.com/en-us/library/ms145517.aspx http://www.iccube.com/support/documentation/mdx/functions.html http://publib.boulder.ibm.com/infocenter/comphelp/v101v121/index.jsp?topic=/com.ibm.xlf121.aix.doc/proguide/ifclause.html http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp?topic=%2Fcom.ibm.dwe.cubemdx.doc%2Fmdx_expressions.html

but none seem to solve my problem.

obvouisly im missing something, is there anyone there that has expercience with MDX and SSAS, that can give me a few pointers or a direction?

Solution

  • In MDX you should use the IIF function; there's no IF / THEN / ELSE that I know of.