SSRS: Common Issues with #Error and IIF

Is there anything more frustrating then seeing this non-error message and wondering what of massively complicated function you've just entered is causing the problem (let's not forget that the Expression editor in Report Builder 3 doesn't allow you to do multi-line statements - just to make it easier!). 

Drives me wild.

Having worked with other people to debug functions the one thing that seemed to really help people was giving them a better understanding of how IIF worked (oddly IIF was the cause of *most* of the problems!).

The IIF function (and the key word here is "function") requires ALL the values passed to it to be valid.

For example if you try IIF( a > 0, 200/a, 0) you will get a "Divide By Zero" error when the value for a is zero because SSRS will still try and evaluate 200/a even though it will never be displayed.

The (clumsy) way around this would be to change the statement to;

=IIF(a > 0, 200/IIF(a = 0, 1, a), 0)

This would prevent the error by setting a to 1 when it is used as the divisor. 

You should also watch out for this problem when using InStr or InStrRev in conjunction with a function to get a substring (as, for example, asking for the 9-character of a 8-character string will give you the dreaded #Error).

If ever there was a bunch of functions crying out for a default value to return in the event of an an error it's these!