Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Divide by Zero frustration Expand / Collapse
Author
Message
Posted Monday, January 14, 2008 9:18 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:52 AM
Points: 7,026, Visits: 6,196
It doesn't seem to matter how I word this my Expression, I keep getting the following error: "rsRuntimeErrorInExpression The value expression for the textbox contains an error: Attempted to divide by zero.".

I've tried this:
=IIF((Fields!NbrClaims.Value)> 0 and (Fields!NbrClaims.Value)< 0, 
Round((First(Fields!PaidClaims.Value)/
First(Fields!NbrClaims.Value)),2), "0.00")

And this:
=IIF((Fields!NbrClaims.Value)<> 0, 
Round((First(Fields!PaidClaims.Value)/
First(Fields!NbrClaims.Value)),2), "0.00")

and this:
=IIF((Fields!NbrClaims.Value)= 0, "0.00", 
Round((First(Fields!PaidClaims.Value)/
First(Fields!NbrClaims.Value)),2))

and I've even put a NULL test in there, even though the results of my original dataset always come back with zeros not nulls. And I still get the stupid "Divide by Zero" error. SSRS doesn't seem to be parsing the IIF right. And if I take the quotes off the 0.00, it still doesn't make a difference.

Can anyone see what I'm missing? I'm getting really annoyed. The error only happens on the one line with zeros on it. The code works for every other group line.



Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #442497
Posted Monday, January 14, 2008 9:28 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:01 PM
Points: 7,081, Visits: 14,673
IIF is going to eval BOTH parts (the THEN and the ELSE) even if it returns just one.

Try this instead:
= Round(
First(Fields!PaidClaims.Value)/
IIF(First(Fields!NbrClaims.Value)=0,
1,
First(Fields!NbrClaims.Value)
)
,2)



----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #442503
Posted Monday, January 14, 2008 9:42 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:52 AM
Points: 7,026, Visits: 6,196
Matt,

You're da bomb! Thanks, that worked perfectly.

Don't know what I'd ever do without you. @=)



Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #442515
Posted Monday, January 14, 2008 9:45 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 15, 2013 1:11 PM
Points: 138, Visits: 698
Another option (especially if you've got a report with many expressions that could result in divide by zero situations is to use a Custom Code function.

In the Code tab/window of Report Properties, enter something like the following:

Public Function DivideBy(ByVal Exp1, ByVal Exp2)
If Exp2 = 0 Then
DivideBy = 0
Else : DivideBy = Exp1 / Exp2
End If
End Function

Then insert the expression
=code.DivideBy(Field!ToBeDivided.Value,Field!DividingBy.Value)
into any cell that has the potential for divide by zero problems.


toolman
Numbers 6:24-26
Post #442520
Posted Monday, January 14, 2008 9:52 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:52 AM
Points: 7,026, Visits: 6,196
Now there's a nifty piece of code I need to book mark. Thanks, toolman!

Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #442532
Posted Wednesday, August 27, 2008 1:08 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 21, 2010 3:25 AM
Points: 23, Visits: 93
Exactly what I was searching for. Thanks a lot ! ;)

Christophe


Christophe
Post #559385
Posted Thursday, May 26, 2011 4:30 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 17, 2012 10:45 AM
Points: 1, Visits: 7
You just saved me hours of work.

Thanks!
Post #1115919
Posted Thursday, May 26, 2011 6:13 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, January 16, 2014 12:03 PM
Points: 21,376, Visits: 9,594
If RS is a pretty big PITA in that regard. The problem with that function is that you need to copy it everywhere and it slows down execution. I wish that the IIF could short-circuit and not throw this useless error. More to the point I think everyone using RS has had this issue!
Post #1115960
Posted Monday, November 07, 2011 12:19 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 07, 2011 12:17 PM
Points: 1, Visits: 20
Try this.

= Fields!A.Value / IIF(Fields!B.Value=0,1,Fields!B.Value)

Post #1201635
Posted Tuesday, January 17, 2012 10:00 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 06, 2013 10:33 AM
Points: 2, Visits: 95
I have this expression and haven't been able to eliminate the divide by zero error. I also tried creating custom code but the examples I've seen as far as where to put the code.divideby don't seem to work with this expression.
The issue is with the part that is bolded
Can anyone help with this?

=((Sum(Fields!field3.Value) / Sum(Fields!field1.Value) * 10 + Sum(Fields!field4.Value) / Sum(Fields!field1.Value) * 10 + Sum(Fields!field5.Value) / Sum(Fields!field1.Value) * 10 + Sum(Fields!field6.Value) / Sum(Fields!field1.Value) * 10) - (Sum(Fields!field7.Value) / Sum(Fields!field2.Value) * 10 + Sum(Fields!field8.Value) / Sum(Fields!field2.Value) * 10 + Sum(Fields!field9.Value) / Sum(Fields!field2.Value) * 10 + Sum(Fields!field10.Value) / Sum(Fields!field2.Value) * 10)) / (Sum(Fields!field7.Value) / Sum(Fields!field2.Value) * 10 + Sum(Fields!field8.Value) / Sum(Fields!field2.Value) * 10 + Sum(Fields!field9.Value) / Sum(Fields!field2.Value) * 10 + Sum(Fields!field10.Value) / Sum(Fields!field2.Value) * 10)
Post #1237349
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse