IIF Formula #Error

Ausway
Ausway used Ask the Experts™
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
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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))

Author

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?
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
Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Author

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?

Many thanks in advance.
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

Author

Commented:
Thank you, works!
You're welcome.  Glad I could help.

 James

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial