reporting-services IF expression Using IIF to Screen for Division by Zero

Help us to keep this website almost Ad Free! It takes only 10 seconds of your time:
> Step 1: Go view our video on YouTube: EF Core Bulk Extensions
> Step 2: And Like the video. BONUS: You can also share it!

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.



Got any reporting-services Question?