January 14, 2008 at 9:18 am
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.
January 14, 2008 at 9:28 am
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?
January 14, 2008 at 9:42 am
Matt,
You're da bomb! Thanks, that worked perfectly.
Don't know what I'd ever do without you. @=)
January 14, 2008 at 9:45 am
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.
[font="Comic Sans MS"]toolman[/font]
[font="Arial Narrow"]Numbers 6:24-26[/font]
January 14, 2008 at 9:52 am
Now there's a nifty piece of code I need to book mark. Thanks, toolman!
August 27, 2008 at 1:08 am
Exactly what I was searching for. Thanks a lot ! 😉
Christophe
Christophe
May 26, 2011 at 4:30 pm
You just saved me hours of work.
Thanks!
May 26, 2011 at 6:13 pm
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!
November 7, 2011 at 12:19 pm
Try this.
= Fields!A.Value / IIF(Fields!B.Value=0,1,Fields!B.Value)
🙂
January 17, 2012 at 10:00 am
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)
January 17, 2012 at 10:04 am
SSRS is annoying with that.
Use this code to do the division (rename as you wish, this is not Ms Access after all! ;-)).
PUBLIC FUNCTION NDZ(Numerator, Denominator, DZResult)
IF Denominator = 0 THEN
RETURN DZResult
ELSE
RETURN Numerator / Denominator
END IF
END FUNCTION
January 22, 2012 at 1:38 pm
My problem was to display zero if the value of the denominator is zero, else the division of the two fields.
So what I used is mentioned below,
iff(Field2=0,0,Field1/Field2)
I worked.. Hope it does the same for you all... 🙂
January 26, 2012 at 1:03 pm
@penelson
re:
=((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)
shouldn't the operand before Sum(Fileds!field7.Value) be a '+' sign?
January 27, 2012 at 4:40 am
All,
I received my answer to this question a long time ago. If you need assistance with a similar problem, please start a new thread as you are more likely to get responses to your question if you post in a thread that doesn't have pages of answers or a thread which says "Problem Solved" in the third or fourth post.
Not a lot of people are going to read beyond my comment of "problem solved."
This is just a friendly public service announcement.
March 18, 2013 at 8:52 am
=IIf(
IsNothing(Sum(Fields!divisor.Value)) Or Sum(Fields!divisor.Value) = 0.0,
"",
Sum(Fields!value.Value) / IIf(IsNothing(Sum(Fields!divisor.Value)) Or Sum(Fields!divisor.Value) = 0.0, 1, Sum(Fields!divisor.Value))
)
I just wanted to add a fact:
If you have a division, that does not allow you to use the divisor "1" instead of "Nothing" you can still use the suggested solutions in this tread:
What you have to do is use your conditions twice: first your "old" way: IIf(Sum(Fields!divisor.Value) = 0.0, "", ...
Then in the dvision: ...Value) / IIf(IsNothing(Sum(Fields!divisor.Value)) Or Sum(Fields!divisor.Value) = 0.0, 1, Sum(...
As you can see, you still have a divsion by 1 implemented but it can never be reached as you ruled it out before.
That makes the renderer and you happy 😉
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply