2

I am building a report in SSRS that divides two numbers to get their percentage. There are some values that will be divided by 0, which throws an error. I have this expression to get around the divide by 0 error, but it has to run the equation twice.

=IIF(System.Double.IsNaN(Sum(Fields!ON_TIME.Value)/Sum(Fields!COUNT_FIELD.Value)),Nothing,(Sum(Fields!ON_TIME.Value)/Sum(Fields!COUNT_FIELD.Value)))

In order to speed up performance I would like to run this expression as a NULLIF expression, like this: 

=NULLIF(Sum(Fields!ON_TIME.Value)/Sum(Fields!COUNT_FIELD.Value), 'Nan')

When I run the report with this expression I get an error saying "Name 'NULLIF' is not declared". I have been trying to find the fully qualified name for NULLIF, as I think that will solve my problem, but I have not had any luck finding it. Does anyone know what the fully qualified name for NULLIF is, or is there some other way to implement NULLIF in SSRS?

Thanks

Spen
  • 43
  • 2
  • 5

2 Answers2

2

Instead of NULLIF, in SSRS VB expressions you want to use IsNothing.

However, this won't solve your divide by zero problem, nor will the answer by D Stanley. The problem is that IIF is a function not a language construct so SSRS evaluates both parameters before passing them to the function regardless of the result of the boolean condition. This means that Sum(Fields!ON_TIME.Value)/Sum(Fields!COUNT_FIELD.Value) will be evaluated even when Sum(Fields!COUNT_FIELD.Value) is zero, which you have stated it will be.

You need to use a double IIF to prevent the divide by zero error, like so:

=IIF(Sum(Fields!COUNT_FIELD.Value) <> 0, 
    Sum(Fields!ON_TIME.Value) / IIF(Sum(Fields!COUNT_FIELD.Value) = 0, 1, Sum(Fields!COUNT_FIELD.Value)),
    Nothing
    )

What we are doing is when Sum(Fields!COUNT_FIELD.Value) is zero, we divide by 1 instead, then throw that result away and give Nothing as the result.

Chris Latta
  • 20,316
  • 4
  • 62
  • 70
1

NULLIF is a SQL function, not VB. Try just checking for 0 instead:

=IIF(SSum(Fields!COUNT_FIELD.Value) = 0,
     Nothing,
     Sum(Fields!ON_TIME.Value)/Sum(Fields!COUNT_FIELD.Value)
    )
D Stanley
  • 149,601
  • 11
  • 178
  • 240
  • I did try something like that earlier in the week, but was having problems with it keeping the final numbers as percentages. I copied your code in and tested it out and it seems to be working fine. Thanks – Spen Oct 15 '14 at 13:28
  • Note that this will still give a divide by zero error when `Sum(Fields!COUNT_FIELD.Value)` is zero, as explained in my answer. – Chris Latta Oct 16 '14 at 00:38