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, as RS evaluates the false condition and creates a divide by zero error.

The following problems is trying to divide last weeks total hours worked by the number of days someone worked, which could be zero.

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

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

even though the division is not used, it errors. So by converting the zero to a 1, stops it 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..

Advertisements
  1. Jane
    22 March, 2010 at 8:12 pm

    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?

    • jcrawfor74
      26 March, 2010 at 1:51 am

      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.

  2. 7 September, 2010 at 4:03 pm

    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.

  3. BVD
    10 March, 2011 at 8:18 am

    Awesome post, thanks 🙂

  4. 19 July, 2011 at 7:15 pm

    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.

  5. Nishad FDeen
    16 September, 2011 at 4:55 pm

    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

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: