reporting-services Using IIF to Screen for Division by Zero


Example

The IIF statement can be used in expressions to screen for division by zero:

=IIF(Fields!PossibleZero.Value=0,0,Fields!Denominator.Value/IIF(Fields!PossibleZero.Value=0,1,Fields!PossibleZero.Value))

SSRS does not short circuit IIF arguments. Therefore, using a single IIF function to screen for division by zero will have no effect and give an #ERROR value.

Instead, a pair of nested IIF statements can be used. The outer IIF controls the value returned in the case of division by zero, 0 in the example above. The inner IIF is a "dummy" value that prevents the engine from actually performing a division by zero in this case.