Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

getting warning: attempted to divide by zero

Posted on 2013-01-11
6
1,354 Views
Last Modified: 2013-01-16
Hi,

I would like to get help in adding a check to the formula down below that takes care of the divide by zero error, if possible.


IIf(CDec(Sum(IIf(Fields!OrderLineStatus.Value = "OPEN", Fields!OrderBalanceQty.Value, CDec(0)))) <> 0, (CDec(Sum(IIf(Fields!OrderLineStatus.Value = "OPEN", (Fields!OrderBalanceQty.Value * Fields!OrderPrice.Value), CDec(0)))) / CDec(Sum(IIf(Fields!OrderLineStatus.Value = "OPEN", Fields!OrderBalanceQty.Value, CDec(0))))), 0)

Open in new window



Thank you
0
Comment
Question by:metropia
6 Comments
 
LVL 28

Expert Comment

by:Ryan McCauley
ID: 38768988
You get the divide by zero if LineStatus <> OPEN or if BalanceQty = 0 - as long as you check to ensure that neither of those is the case (another IIF outside your existing formula), that should cover you against your divide by zero error.

Alternatively, you could use the little known IFERROR() function to replace your error with a suitable value:

=IFERROR(Your Expression Here, 0)

Open in new window


And it would give you a zero value if the expression causes a calculation error (in this case, divide by zero).
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 38769530
You can check it before division using expression.
0
 
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 38778403
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 37

Accepted Solution

by:
ValentinoV earned 500 total points
ID: 38778772
ryanmccauley: are you sure that IFERROR is a standard SSRS function? I can't seem to find anything on it except something related to Aspose.Cells. (And also, it doesn't work...)  It would have been great otherwise though!

What seems to be forgotten frequently is that SSRS evaluates the whole expression.  It doesn't stop evaluating the ELSE part of the IIF, not even if the THEN part evaluates to True.  So adding an IIF around the existing expression will still result in divide by zero, as Nicobo wants to point out with that link.

With that in mind, you need to prevent that the denominator is zero under any circumstances.  The most simple way to achieve that is by putting the condition in the denominator itself:

=Fields!Number1.Value / IIF(Fields!Number2.Value = 0, 1, Fields!Number2.Value)

Open in new window

But that's only part of the solution because instead of "division by zero" this will now result in Number1.

So we need another IIF around the whole thing, to be able to output another value in case of div by zero:

=IIF(Fields!Number2.Value = 0, "result of div by zero" ,
	Fields!Number1.Value / IIF(Fields!Number2.Value = 0, 1, Fields!Number2.Value)
)

Open in new window

0
 
LVL 28

Expert Comment

by:Ryan McCauley
ID: 38780024
ValentinoV: I've never used ISERROR in SSRS, but I saw it referenced in a couple of SSRS forums (like http://social.msdn.microsoft.com/forums/en-US/sqlreportingservices/thread/ca1f9bad-8f7a-44c5-8c8b-9739e433cbfd) and I've used it extensively in Excel - I should have confirmed it before assuming it worked.

My mistake - glad your solution did the trick!
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 38782303
After ryanmccauley's last comment I got curious and started to investigate a little.  IFERROR (as Ryan mentioned in his first comment) doesn't exist but ISERROR is indeed an existing VB function.  However, it will only work in case of an exception.  Here's its description:

"Returns a Boolean value indicating whether an expression is an exception type."

So I decided to give the following a try: IsError(1/0)

And that returned False.  Erm, did I miss something? Apparently I did.

Then I put the following in a textbox: =1/0

Guess what?  That actually runs without any errors!  And it will produce the string "Infinity".

Alright, what about the following: =IsError(1.1/"duh")

That will produce #Error on the report and the following rsRuntimeErrorInExpression in the Output pane: Input string was not in a correct format. (I admit I'm taking it a bit far there but from a function as IsError I'd expect if to "handle" the exception.)

To be honest, so far I haven't succeeded in getting a True out of the IsError function.  So I decided to not use it.


Another thing which I've discovered is that the expression result behavior is not consistent over different data types.  The "Infinity" above should already ring a bell.  Surely I remember situations where I'd actually gotten a "division by zero" error?  And also, if my memory would be failing, why are there so many references to issues with it on the internet?

Let's move to the next step of the investigation.  If 1/0 doesn't generate a "div by zero" error, doesn't that mean that the following should work?

=IIF(Fields!Number2.Value = 0, "divzero", Fields!Number1.Value / Fields!Number2.Value)

Well, apparently it does work!  But not in all situations.  And that's where my confusion comes from.  There's actually a hidden gem in the following Brian Welcker blog post: End of Amnesia (Avoiding Divide By Zero Errors)

If you read his article you'd think that it confirms what I explained earlier.  And it does.  But there's a hidden condition in the second paragraph:

"This works fine unless a value in the Cost field is zero (and is not an integer or double), in which case you will get '#error' displayed in your report. This is because a divide by zero with a decimal field will throw an exception."

In other words, the simple IIF(B = 0, "it's zero", A/B) will work fine as long as the data type of A and B is not decimal.  But if they are decimal, you'll start seeing #Error in your report and the "Attempted to divide by zero" rsRuntimeErrorInExpression in the Output pane.

Why this behavior difference occurs isn't yet 100% clear to me but it must be either related to how VB has implemented the / operator for the different data types OR to how the report processing engine handles different exceptions.  There must be some code somewhere that decides to display "Infinity" instead of generating an error.

I hope you found reading this as interesting as much as I learned from the research.

Have fun!
Valentino.
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Hi, I have heard from my friends that it’s not possible to create Label Printing report using SSRS. I am amazed after hearing this words not possible in SSRS. I googled lot and found that it is possible to some of people know about the Report Bui…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

809 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