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.
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!
0 comments:
Post a Comment