SSRS 2008 - Dealing with division by zero scenarios

Question

We're running into a problem with one of our reports. In one of our tablixes a textbox has the following expression:

=Iif(Fields!SomeField.Value = 0, 0, Fields!SomeOtherField.Value / Fields!SomeField.Value)

Which should be pretty self-explanatory. If "SomeField" is zero, set the text box value to zero, else set it to "SomeOtherValue / SomeValue".

What has us stumped is that the report still throws a runtime exception "attempted to divide by zero" even though the above expression should prevent that from happening.

We fiddled a bit with the expression just to make sure that the zero-check is working, and

=Iif(Fields!SomeField.Value = 0, "Yes", "No")

works beautifully. Cases where the data is in fact zero resulted in the textbox displaying "Yes" and vice versa. So the check works fine.

My gut feel is that the Report rendering engine throws the exception at run-time, because it "looks" as if we are going to divide by zero, but in actual fact, we're not.

Has anyone run into the same issue before? If so, what did you do to get it working?

Buckeen

Awesome! I used the suggestion to answer my question at: #43663227 – Creamery

It is good practice to never, never, never, never NEVER calculate values or call functions in an IIF statement. By the way, I left off a few nevers in case it got a bit overwhelming. – Smalto

Answer 1

IIf will always evaluate both results before deciding which one to actually return.

Try

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

This will use 1 as the divisor if SomeOtherField.Value = 0, which does not generate an error. The parent IIf will return the correct 0 for the overall expression.

Belshin

love this! especially the logic of "This will use 1 as the divisor if SomeOtherField.Value = 0, which does not generate an error. The parent IIf will return the correct 0 for the overall expression." hehehe, sneaky, indeed – Duprey

Answer 2

An easy clean way to prevent a divide by zero error is using the report code area.

In the Menu, go to Report > Report Properties > Code and paste the code below

Public Function Quotient(ByVal numerator As Decimal, denominator As Decimal) As Decimal
        If denominator = 0 Then
            Return 0
        Else
            Return numerator / denominator
        End If
    End Function

To call the function go to the the Textbox expression and type:

 =Code.Quotient(SUM(fields!FieldName.Value),SUM(Fields!FieldName2.Value))

In this case I am putting the formula at the Group level so I am using sum. Otherwise it would be:

 =Code.Quotient(fields!FieldName.Value,Fields!FieldName2.Value)

From: http://williameduardo.com/development/ssrs/ssrs-divide-by-zero-error/

Inveigle

I love it. Clean and reusable. If you'd paste this solution here more people would see how good it is :-) – Dependable

This is the best solutions, many thanks, i tried the solution MartW provided but still didnt work for some reason, your solutions workds – Enemy

Answer 3

On reflection, I feel best idea is to multiply by value to power -1, which is a divide:

=IIf
  (
      Fields!SomeField.Value = 0
    , 0
    , Fields!SomeOtherField.Value * Fields!SomeField.Value ^ -1
  )

This doesn't fire pre-render checks as val * 0 ^ -1 results in Infinity, not error

Oxbow

Answer 4

IIF evaluates both expression even thought the value of Fields!SomeField.Value is 0. Use IF instead of IIF will fix the problem.

Trici