Link to home
Start Free TrialLog in
Avatar of globalwm2
globalwm2Flag for United States of America

asked on

SSRS 2005: Divide by Zero error even with IIF statement used

I have the following expression:

=IIF(SUM(Fields!TOTPY.Value)=0,0,((SUM(Fields!TOTPY.Value) - SUM(Fields!COGSPY.Value)) / SUM(Fields!TOTPY.Value)))

The values on my report are all zero values - yet the report displays "#Error" in this column when I should expect "0".

Any ideas on what's wrong or a better way to handle this?
Avatar of ralmada
ralmada
Flag of Canada image

Avatar of Guy Hengel [angelIII / a3]
please try this variant:


=IIF(SUM(Fields!TOTPY.Value)=0,0,(SUM(Fields!TOTPY.Value) - SUM(Fields!COGSPY.Value)) / IIF(SUM(Fields!TOTPY.Value)=0,1,SUM(Fields!TOTPY.Value) )

Open in new window

Avatar of globalwm2

ASKER

angelIII:  What does the last part of the statement do?  I had to add another ")" at the end for validation:

IIF(SUM(Fields!TOTPY.Value)=0,1,SUM(Fields!TOTPY.Value)))

It's looking good so far.

ralmada: I came across that site for adding custom code before. I wanted to explore a expression first before doing that.
ASKER CERTIFIED SOLUTION
Avatar of ralmada
ralmada
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I'm sure I will be using this over and over - thanks for the tip on the second IIF.