globalwm2
asked on
SSRS 2005: Divide by Zero error even with IIF statement used
I have the following expression:
=IIF(SUM(Fields!TOTPY.Valu e)=0,0,((S UM(Fields! TOTPY.Valu e) - 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?
=IIF(SUM(Fields!TOTPY.Valu
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?
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) )
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!TOT PY.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.
IIF(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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I'm sure I will be using this over and over - thanks for the tip on the second IIF.
http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/ef1a0375-414e-41f4-b32f-3b782c5b4c85/