# #Error using IIF and divide by zero

### Question

• I am getting an error in a calculated field that could potentially divide by zero, even though I'm using an IIF.  The column displays in the report as "#Error".  My expression looks like this:

= IIF(Fields!Qty.Value = 0, "None", Fields!Hours.Value / Fields!Qty.Value)

I have successfully used this approach with INT fields, but this time the Hours field is a NUMERIC(9,2).  My workaround is to do this:

IIF(Fields!Qty.Value = 0, "None", IIF(Fields!Qty.Value = 0, 42, Fields!Hours.Value) / Fields!Qty.Value)

I guess the 42 is cast to an INT inside the second IIF and the calculation works.

What's strange is that the division would even be carried out in the event of Qty = 0 from the first IIF, because the expression should just evaluate to "None" and that would be that.

Has anybody run into this problem?  Is my workaround the recommended approach?

-Larry

Friday, January 19, 2007 3:56 PM

• Hi Larry,

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!Qty.Value = 0, "None", Code.Divide(Fields!Hours.Value, Fields!Qty.Value))

-- Robert

Tuesday, January 23, 2007 2:52 AM

### All replies

• Lawrence

Try

IIf(Fields!Income2.Value = 0, nothing,Fields!Income.Value/Fields!Income2.Value)

This works for me when my value is zero

Ham

Friday, January 19, 2007 9:37 PM
• Hi Larry,

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!Qty.Value = 0, "None", Code.Divide(Fields!Hours.Value, Fields!Qty.Value))

-- Robert

Tuesday, January 23, 2007 2:52 AM
• Thanks Robert, that's a good (dare I say) workaround.  I'm still curious why the IIF errors out with the double division but works with integer division.

Also, the Edit Expression dialog has the "Divide" text underlined in red, but my project builds successfully and runs ok too.  Any idea why it might think it's invalid?

-Larry

Wednesday, January 24, 2007 12:41 PM
• Hi Ham,

Looks like I would still have to do two nested IIF statements -- one for my "None" message, and the other to return Nothing.  I'm trying to avoid that.  But the Code.Divide approach is working, so I'm on my way.

Thanks.

-Larry

Wednesday, January 24, 2007 12:43 PM
• Worked like a charm.....Thanks
Thursday, December 09, 2010 5:29 AM
• hi Larry,

use this,it would work

=iif(Fields!Hours.Value = 0,0, Fields!NetSales.Value/Fields!Hours.Value)

amit

• Proposed as answer by Thursday, December 30, 2010 9:59 AM
Wednesday, December 15, 2010 2:08 PM
• Hi,

Code Worked perfectly...:-)

Monday, June 06, 2011 9:12 AM
• The Code.Divide worked great! Thanks!
Friday, September 21, 2012 3:02 PM
• I have noticed that the problem occurs only when dividing decimals.

When dividing integers it works properly.

So I tried using something like this:

=iif(a = 0, 0, CInt(b * 100) / CInt(a * 100))

Of course, for it to work, we need to decide how many decimal digits are significant and multiply by 10 to their power e.g. for 3 digits multiply by 10^3=1000).

Needless to say that the code solution offered Robert Bruckner is much more preferable, I just wanted to specialize the problem a bit more.

• Edited by Tuesday, November 19, 2013 10:51 AM typo
Tuesday, November 19, 2013 10:50 AM
• This function works great, but if you have a calculated field you will get errors about aggregates, not sure how to get round this! Any ideas?

Thanks,

Kev

• Proposed as answer by Wednesday, February 05, 2014 12:00 AM
• Unproposed as answer by Wednesday, February 05, 2014 12:00 AM
Thursday, January 30, 2014 4:24 PM
• First a diatribe: This is just one of the many annoying "features" of SSRS that needs to be fixed. It should just flat out work. Period.

Now the fix.

The following formula to calculate Discount% gives the #Error result if List = 0

=iif(Fields!List.Value=0,0, (Fields!List.Value-Fields!Net.Value)/Fields!List.Value)

However, if you add a very small number to the divisor it will work.

=iif(Fields!List.Value=0,0, (Fields!List.Value-Fields!Net.Value)/(Fields!List.Value+.000001))

Apparently SSRS evaluates the false part of the expression before it tests. Try this in most any other language and you will not have this problem.

You just need to add a small enough number to your formula to not impact your results when the divisor is not zero.

You can actually leave out the IIF entirely if you do this:

Fields!List.Value-Fields!Net.Value)/(Fields!List.Value+.000001)

I usually leave the syntax in there just so I know what the intention was.

• Edited by Wednesday, February 05, 2014 1:34 AM
Wednesday, February 05, 2014 12:15 AM
• Is this confirmed? "SSRS evaluates the false part of the expression before it tests."
Monday, February 02, 2015 3:16 AM
• Yes; IIF evaluates both sides of the equation, so upon further reading, you have to apply an IIF in 2 places as shown below:

``=IIf(Fields!SomeField.Value = 0, 0, Fields!SomeOtherField.Value / IIf(Fields!SomeField.Value = 0, 1, Fields!SomeField.Value))``

Thursday, December 10, 2015 8:32 PM
• SSRS is confusing the types I calculated fields try:- Similar to using float in sql 0 is not zero but an approximation close to zero

=iif(

round(sum(Fields!Qty.Value),2) = 0.00 ,

0.00,

round(

sum(Fields!FirstQty.Value) * 1.00

/

sum(Fields!Qty.Value) * 1.00

,2

)

)

Wednesday, June 29, 2016 1:07 PM