Solved

#Error when using iif statement with divide by zero

Posted on 2014-12-01
6
153 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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

How to use Variables  and Custom code in SSRS report and Assembly reference to use compile shared code in SSRS. Its big question for all who are working with SSRS. It is easy to create assembly and refer in SSRS report, still there are some steps…
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.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

707 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now