0

I have the following expression in SSRS:-

=IIf(IsNothing(Fields!Hours.Value), 0, Sum(Fields!Hours.Value) / Sum(Fields!ReportingHours.Value))

The expression appears as #Error. I removed the division part of the equation, leaving just this:

=IIf(IsNothing(Fields!Hours.Value), 0, Sum(Fields!Hours.Value))

Then the correct numbers appear. If I perform an addition operation, the calculation also errors out. It seems that it does not like something about performing mathematical operations when using the Sum() function. If I do simple division without the Sum, as in this expression:

=IIf(IsNothing(Fields!Hours.Value), 0, Fields!Hours.Value / Fields!ReportingHours.Value)

Then numbers show up fine. It is only when I have the Sum() function on the expression that causes an error. Any ideas as to how to fix this?

KrazzyNefarious
  • 3,202
  • 3
  • 20
  • 32
user3465260
  • 11
  • 3
    See http://stackoverflow.com/questions/13345926/how-can-i-eliminate-error-on-ssrs-expression-when-dividing-by-zero or http://stackoverflow.com/questions/19189132/ssrs-expression-divide-by-zero-error for various workarounds – Ian Preston Mar 26 '14 at 17:13
  • 1
    Indeed. There's no short-circuit evaluation on `IIf` in VBScript. How you deal with it will depend on your data and what you want to happen when `Sum(Fields!ReportingHours.Value)` may be zero or Nothing. – Matt Gibson Mar 26 '14 at 17:16

1 Answers1

0

Try the following expression:-

=IIf(IsNothing(sum(Fields!Hours.Value)), 0, Sum(Fields!Hours.Value) / Sum(Fields!ReportingHours.Value)

EDIT

I have edited my previous post.

KrazzyNefarious
  • 3,202
  • 3
  • 20
  • 32
  • The proposed solution did not work. It seems that any expression that contains two fields with the SUM() function returns an error. If I only use SUM() on one field, it works. I can't figure out how to fix it. – user3465260 Mar 27 '14 at 18:25
  • @user3465260 I have edited my previous post. please check and comment. – KrazzyNefarious Mar 27 '14 at 18:47