We help IT Professionals succeed at work.

SSRS 2005 Report error Division By Zero

teamwork123
teamwork123 asked
on
Currently trying to compute percentage in BIDS SSRS 2005, on a footer group row from two text box's that sum up there column of values.  Problem is when I run an IIF statement or a Switch statement i still receive the error.  I have tried the following:


=Switch(ReportItems!textbox22.Value = 0,0
      ,(ReportItems!textbox22.Value = 0 OR ReportItems!textbox13.Value = 0), 0
      ,ReportItems!textbox13.Value < 0, ReportItems!textbox22.Value / reportItems!textbox13.Value * 1
      ,ReportItems!textbox13.Value > 0, ReportItems!textbox22.Value / reportItems!textbox13.Value
      )

=IIf(ReportItems!textbox22.Value =0 OR ReportItems!textbox13.Value = 0, 0,
      (IIf(ReportItems!textbox13.Value < 0, ReportItems!textbox22.Value / reportItems!textbox13.Value * 1,
            (IIf(ReportItems!textbox13.Value > 0, ReportItems!textbox22.Value / reportItems!textbox13.Value, 0)))))

I have tried a few other combinations, but no good.   How do I get past my error?

This statement below works as expected
=IIf(ReportItems!textbox22.Value =0,0,(IIf(ReportItems!textbox13.Value = 0, 0,1)))

Thanks
Comment
Watch Question

Director, Practice Manager and Computing Consultant
Awarded 2014
Top Expert 2014
Commented:
The problem arises because, unlike (say) Excel, SSRS evaluates all parts of the Switch/IIF formula, regardless if they will never be evaluated.
 
For instance,
 
=IIF(1=0,5/0,0)
 
will result in a Division by Zero error, if though 1 will never equal 0. In Excel you can get away with it. In SSRS, you can't.
 
What you do there is make sure that all parts can work. Therefore:
 
ReportItems!textbox22.Value / iif(ReportItems!textbox13.Value = 0, 1, reportItems!textbox13.Value)
 
In the above, you are therefore never dividing by zero, and it should work.