Solved

#Error when using iif statement with divide by zero

Posted on 2014-12-01
6
161 Views
Last Modified: 2014-12-01
Hi all,

I have an expression on a report as follows:

=iif(sum(Fields!EstHours.Value) = 0,"N/A",sum(Fields!ACT_COMP_HRS.Value)/sum(Fields!EstHours.Value))

From time to time, the value of EstHours is zero. When this occurs, my expression returns a #Error as opposed to the N/A I require. I understand this is because the iif statement needs to evaluate the whole expression which includes the divide by zero. My question is, how do I remedy this?

Thanks in advance
0
Comment
Question by:FMabey
6 Comments
 
LVL 37

Accepted Solution

by:
ValentinoV earned 500 total points
ID: 40473281
Your understanding of the issue is correct.  One way to avoid the error is to write this code through the Custom Code functionality, which means you'll need a couple of lines of code in Visual Basic.

Or you can add an additional IIF following the /, like this:

=iif(sum(Fields!EstHours.Value) = 0,"N/A",sum(Fields!ACT_COMP_HRS.Value)/IIF(sum(Fields!EstHours.Value)=0, 1, sum(Fields!EstHours.Value))

More info: FAQ: Why does the “Attempted to divide by zero” error still happen?
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40473290
Nice.
0
 
LVL 10

Expert Comment

by:HuaMinChen
ID: 40473358
Use
=iif(sum(Fields!EstHours.Value) = 0,1,sum(Fields!ACT_COMP_HRS.Value)/sum(Fields!EstHours.Value))

Open in new window


to avoid the error.
0
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40473367
Does it?
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 40473373
no it doesn't, that will still result in Divide by zero...
0
 
LVL 3

Author Closing Comment

by:FMabey
ID: 40473886
Thank you, this works just as required.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Server 2014 - Report Server - Stored Procs 8 61
source and target row counts 4 47
SSRS  - Dropdown with Null 3 33
Need return values from a stored procedure 8 40
Introduction In the following article I’ll be discussing and demonstrating several different ways of how images can be put on a report. I’m using SQL Server Reporting Services 2008 R2 CTP, more precisely version 10.50.1352.12, but the methods ex…
I recently went through setting up a JasperReports Server using the AWS EC2 instance, and this article will cover some basic administration tasks I had to perform.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question