# IIF Formula #Error

on
Hi Experts,

I am trying to use an IIF formula to avoid dividing by zero that causes an #Error but can not seem to get it right.

Essentially what i need is to divide
sum of bill and non bill hours by sum of std and leave hours.

Problem is when Std and Leave hours are the same value, the result is Zero hence when dividing by zero causes an error.

=IIF(Fields!StdHour.Value=Fields!LeaveHours.Value,0,
SUM(Fields!BillHours.Value+Fields!NonBillHours.Value)/SUM(Fields!StdHours.Value-Fields!LeaveHours.Value))*100

Any help would be appreciated, and thanks in anticipation.
Comment
Watch Question

Do more with

EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Hi,
Out of the box, Reporting services does not handle divide by zero conditions gracefully.
I recommend to add a custom code function for the division (in Report -> Report Properties -> Code):
Public Function Divide(ByVal first As Double, ByVal second As Double) As Double
If second = 0 Then
Return 0
Else
Return first / second
End If
End Function

Then, modify the expression accordingly:

= IIF(Fields!StdHour.Value=Fields!LeaveHours.Value,0, Code.Divide(SUM(Fields!BillHours.Value+Fields!NonBillHours.Value)/SUM(Fields!StdHours.Value-Fields!LeaveHours.Value))*100))

Commented:
Hi, thank you for your feedback/response.

I have just tried to add the code to report properties then modified the expression as suggested.

However, Code.Divide is unrecognized, in preview, 'divide' is invalid.

Is this something that needs to be defined?

Commented:
hi sorry try this
after including the above code in the report properties modify the expression as
= IIF(Fields!StdHour.Value=Fields!LeaveHours.Value,0, Code.Divide(SUM(Fields!BillHours.Value+Fields!NonBillHours.Value)/SUM(Fields!StdHours.Value-Fields!LeaveHours.Value)))*100

Commented:
Hi, tried as suggested, still the same error. In Edit Expressions, Divide is underlined as invalid item in the expression. Code. only allows for Equials, GetHashCode, GetType, ReferenceEquals, ToString.

I am using VS2008, Dynamics AX 2009 Reporting Tools. Does that make a difference in the the expression syntax?

Commented:
I haven't used MS SQL reporting, so I'm doing some guesswork here, but does the original formula that you posted evaluate without getting any errors, but you still get the divide by 0 error, or do you get errors on the formula itself (as mentioned in later posts)?

If the formula evaluates, but you still get the divide by 0 error, then it seems to me that the problem is that you're checking Fields!StdHour.Value and Fields!LeaveHours.Value (individual values), but you're dividing by the SUM of the difference between those fields.  That would only make sense to me if the "sum" actually only contained one set of values.  I would think that the formula should be:

=IIF(SUM(Fields!StdHours.Value-Fields!LeaveHours.Value)=0,0,
SUM(Fields!BillHours.Value+Fields!NonBillHours.Value)/SUM(Fields!StdHours.Value-Fields!LeaveHours.Value))*100

As I said, just guessing.

James

Commented:
Thank you, works!

Commented:
You're welcome.  Glad I could help.

James

Do more with