Martin's Blog

The iif-function (immediate if function) was introduced some time ago, and allowed us to essentially use if-statements in our SSRS reports. I still remember how happy I was when I first saw it, and it made conditional formatting so much easier.

In my mind at least, the iif-function represented a compact type of if-statement and would behave in the same way…but the other day I was again reminded to read the fine-print.

The typical if-statement looks like this:

And on the surface, the iif-function looks very similar: iif(<em>condition</em>, <em>true-part</em>, <em>false-part</em>)

There is one key difference between the two though: In the classic if-statement, the true-part of the statement will only be evaluated if the condition is true (the same goes for the false-part). In the iif-function though, all the parts of the function are evaluated, irrespective of the condition. This means that you need to be especially careful when using the iif-function in expressions that involve divisions, as I found out the hard way.

At first I thought it was a bug, but after doing some more research I found that it was a classic case of “It’s a feature, not a bug!!!” (More info here and here)

Here is an example of what I did, what happened and how I managed to work around the issue:

The workaround above works fine, but somehow I am still not content. Especially when working with Reporting Services, I would prefer not to have messy (nested) iif-functions…and I can just imagine how confusing this must be for beginners.

Is it too much to ask that iif-functions behave in the same way as if-statements? I think not…development tools are supposed to make our lives easier. I don’t want to generalize, but in this particular case it is both confusing and counter-intuitive to what we expect. Maybe it’s just me…