49

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?

tobias86
  • 4,979
  • 1
  • 21
  • 30
  • 1
    Awesome! I used the suggestion to answer my question at: http://stackoverflow.com/questions/43662727/ssrs-expression-error-calculate-variable-shows-error-when-dividing-by-zero/43666171#43666171 – Aline Apr 27 '17 at 19:17
  • 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. – freeflow Apr 17 '20 at 22:00

4 Answers4

75

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.

MartW
  • 12,348
  • 3
  • 44
  • 68
  • 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 – Peter Perháč Oct 14 '13 at 13:51
25

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/

sǝɯɐſ
  • 2,470
  • 4
  • 33
  • 47
william mendoza
  • 276
  • 3
  • 3
  • 4
    I love it. Clean and reusable. If you'd paste this solution here more people would see how good it is :-) – Mariusz Dec 01 '16 at 10:15
  • This is the best solutions, many thanks, i tried the solution MartW provided but still didnt work for some reason, your solutions workds – asmgx Jun 21 '18 at 00:28
17

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

SkyDkn
  • 171
  • 1
  • 2
2

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

user1689669
  • 21
  • 1