Home > Reporting Services, SQL Server > SQL Server Reporting Services (SSRS) – IIF statement divide by zero error

SQL Server Reporting Services (SSRS) – IIF statement divide by zero error

If you use the IIF statement to solve your divide by zero errors, you will most likely find that you still get divide by zero errors.

The following example is trying to divide last weeks total hours worked

The following errors:

=IIF(Sum(Fields!LWWorkingDays.Value) = 0, 
  0,
  Sum(Fields!LWTotal.Value)/Sum(Fields!LWWorkingDays.Value) 
 )

The Problem
Even though the number of days = 0 and it should evaluate to the true condition of 0, SSRS still evaluates the false result, and the false result still throws a divide by zero error because it is evaluating:
Sum(Fields!LWTotal.Value) / 0

The solution:
We need to try to make the false result not error when working days = 0.
The false result

Sum(Fields!LWTotal.Value) / Sum(Fields!LWWorkingDays.Value)

Needs to change to the following:

Sum(Fields!LWTotal.Value) / 
IIF(Sum(Fields!LWWorkingDays.Value) = 0, 1, Sum(Fields!LWWorkingDays.Value))

Why does this work?
When working days = 0 the result is going to be 0. So we don’t really care what the false result is going to be we just don’t want it to error. So the new IIF statement on the denominator returns 1 when the working days are zero.

This evaluates to:

Sum(Fields!LWTotal.Value) / 1 

and this does not error.

Final Code Example

=IIF(Sum(Fields!LWWorkingDays.Value) = 0, 
    0,
    Sum(Fields!LWTotal.Value) / 
    IIF(Sum(Fields!LWWorkingDays.Value) = 0, 1, Sum(Fields!LWWorkingDays.Value)) / 60
 )

In short by converting the zero to a 1, it stops the false result from erroring and allows the calculation to work correctly when it is evaluated.

That is a work around for a problem that should never of existed, good one Microsoft..

VN:F [1.9.22_1171]
Rating: 10.0/10 (3 votes cast)
SQL Server Reporting Services (SSRS) - IIF statement divide by zero error, 10.0 out of 10 based on 3 ratings

Categories: Reporting Services, SQL Server Tags:
  1. Jane
    March 22nd, 2010 at 20:12 | #1

    I am using formula for calculated column: =sum(Fields!MarketBetaAdjExp.Value)/sum(Fields!Exposure.Value)

    if Field.Exposure = 0 in calculated Column I need show just blank ,what to use?

    VA:F [1.9.22_1171]
    Rating: 0 (from 0 votes)
    • jcrawfor74
      March 26th, 2010 at 01:51 | #2

      Apply a format to the field,(by using the format property on the Textbox that the data is being displayed).
      You can define formats for “positive:Negative:zero”, similar to excel like so;

      #0.0;(#0.0);-
      negative values with this would display as a dash, -.

      you should use
      #0.0;(#0.0);

      where there is a single space specified as the zero value.

      I just tested it on my report (against 2008 R2 Nov CTP) and it works.

      VN:F [1.9.22_1171]
      Rating: 0 (from 0 votes)
  2. September 7th, 2010 at 16:03 | #3

    Many thanks, I’ve been scratching my head for weeks about div by 0 errors when I’ve tried to account for them with iif statements.

    VA:F [1.9.22_1171]
    Rating: 0 (from 0 votes)
  3. BVD
    March 10th, 2011 at 08:18 | #4

    Awesome post, thanks 🙂

    VA:F [1.9.22_1171]
    Rating: 0 (from 0 votes)
  4. July 19th, 2011 at 19:15 | #5

    Well done microsoft on this one. Impressive. How could it be that bad.

    Thanks for the blog – I was looking for ages to find out what i had done wrong.

    VA:F [1.9.22_1171]
    Rating: 0 (from 0 votes)
  5. Nishad FDeen
    September 16th, 2011 at 16:55 | #6

    Please I need some answeres.
    Ihave a situation in (Sql reporting services) columns I have two value,
    One is postive numbers ex(676767)next is (-45623)I have a lot of numbers ,what i need advise I have to sum the negative numbers and devide by another column it hase positive numbers , if any can help me please , I need this very urgent. god bless you

    VA:F [1.9.22_1171]
    Rating: 0 (from 0 votes)
  6. April 9th, 2013 at 02:57 | #7

    This one was driving me crazy! Thanks for the solution, it worked perfectly.

    VA:F [1.9.22_1171]
    Rating: 0 (from 0 votes)
  7. Bob
    November 14th, 2013 at 01:19 | #8

    Thanks! This is the most helpful post I’ve ever found. I would never have figured this one out. You are amazing! Microsoft, not so much on this one.

    VA:F [1.9.22_1171]
    Rating: 0 (from 0 votes)
  1. No trackbacks yet.