asked on

IIF Formula #Error

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.

PoornimaKolapalli

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))

Ausway

ASKER

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?

PoornimaKolapalli

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

Ausway

ASKER

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.

ASKER CERTIFIED SOLUTION

James0628

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ausway

ASKER

Thank you, works!

James0628

You're welcome. Glad I could help.

James