Can someone explain why I am receiving this error...

'The Value expression for the textbox 'textbox2' contains an error: Attempted to divide by zero.'

on this expression...

=IIF(First(Fields!V2.Value,"MyDataSet")<>0,Last(Fields!V2.Value,"MyDataSet")/First(Fields!V2.Value,"MyDataSet"),0)

In this case, both values are equal to 0 but the check should avoid the actual division when the divisor is 0, should it not?

Thanks in advance for your assistance.

Anthony Sullivan

• Edited by Jinchun ChenMicrosoft employee, Moderator Friday, February 20, 2009 3:32 AM The expression shows up as a scroll bar.

For some reason, in my browser your expression just shows up as a scroll bar, so I'll paste it here:

=IIF(First(Fields!V2.Value, "MyDataSet") <> 0, Last(Fields!V2.Value, "MyDataSet")/First(Fields!V2.Value, "MyDataSet"), 0)

Can I ask what version of RS you are running, and where you are viewing the report when it generates that error (InReport Builder,inReport Designer (VS), oron theReporting Server)? I tried reproducing this in Report Builder and the viewer correctly handles this case.

Thanks,

Paul

I'm developing in VS 2005.

This is my RS version.

Microsoft SQL Server Reporting Services Designers
Version 9.00.1399.00

I see this error when I select the preview tab, enter the parameters for the report and click 'View Report'.

Thanks for your response.

Anthony

Hi Anthony,

* the following is for SSRS 2005.

In theory you are right... your check for 0 should prevent the divisor statement from being evaluated.

However, (and the following might need to be confirmed)... I believe in SSRS it parses and checks the expression from Right -> Left.

This means that it reads and checks the following:

Last(Fields!V2.Value,"MyDataSet")/First(Fields!V2.Value,"MyDataSet")

Before checking your condition:

First(Fields!V2.Value,"MyDataSet")<>0

And thus generates the error before even hitting your "check for 0" condition.


I found the best way around this was to use a custom code function requiring 2 steps:

1st) Create a custom code function.
- go to the menu option "Report -> Report Properties"
- go to the "Code tab.
- add code similar to the following in the "Custom Code" section:

Public Shared Function VarPercent(ByVal ValueA As Decimal, ByVal ValueB As Decimal) As Decimal
If (ValueB = 0 OR ValueA = 0) Then
Return 0
End If
Return (ValueA / ValueB )
End Function

2nd) Use the custom code function in the report.
- Use the following expression in fields where you're doing divisons (following is for a % field):

=IIF(IsNothing(Fields!MyValueA.Value),0,
IIF(IsNothing(Fields!MyValueB.Value),0,
code.VarPercent(Fields!MyValueA.Value,Fields!MyValueB.Value)
)
)

The above samples A) checks for non-null values in the expression setting nulls to 0 otherwise B) it calls the function that checks for zeros and performs the divison if no zeros exist.

Yes, it is extra work for what should initially calculate correctly if it wasn't for the way SSRS checks/parses expressions, but once you have the above setup it is rather easy to use and cut and copy into other places.

Hope this helps.
Scott

• Proposed as answer by ScottKaysee Thursday, February 19, 2009 1:13 AM
• Edited by ScottKaysee Thursday, February 19, 2009 1:19 AM another typo :)
• Marked as answer by Jinchun ChenMicrosoft employee, Moderator Friday, February 20, 2009 3:50 AM

Please note that iif() is a function and therefore all parameters will be evaluated before calling the function. One solution has been posted by Scott. A working solution for A/Busing only iif is:

=iif(B=0, 0, A / iif(B=0, 1, B))

hth,
Gerald

see also this thread: attempt to divide by zero

• Proposed as answer by actinium.ga Thursday, February 19, 2009 8:32 AM
• Marked as answer by Jinchun ChenMicrosoft employee, Moderator Friday, February 20, 2009 3:51 AM

Please note that iif() is a function and therefore all parameters will be evaluated before calling the function. One solution has been posted by Scott. A working solution for A/B using only iif is:

=iif(B=0, 0, A / iif(B=0, 1, B))

hth,
Gerald

see also this thread: attempt to divide by zero

My case was similar to this and I had to do something slightly more.

I'm using SSRS 2005 at this momen, if and in the above scenario quoted by Gerald, I'm passing a DECIMAL(18,2) for both my "A" and "B" values.  Because of that, I did the following to make my report not throw and #ERROR, but instead, a zero.

=iif(B = 0, 0, iif(A = 0, 0, A) / iif(B = 0, 0, B))

For some reason, SSRS (2005 anyway) does not like a divide by zero with any decimals anywhere in the equation, it seems, which is why (I guess) I'm technically changing the "A" decimal to an int???

Don't know, but glad it works.  ;-)

Please note that iif() is a function and therefore all parameters will be evaluated before calling the function. One solution has been posted by Scott. A working solution for A/B using only iif is:

=iif(B=0, 0, A / iif(B=0, 1, B))

hth,
Gerald

see also this thread: attempt to divide by zero

My case was similar to this and I had to do something slightly more.

I'm using SSRS 2005 at this momen, if and in the above scenario quoted by Gerald, I'm passing a DECIMAL(18,2) for both my "A" and "B" values.  Because of that, I did the following to make my report not throw and #ERROR, but instead, a zero.

=iif(B = 0, 0, iif(A = 0, 0, A) / iif(B = 0, 0, B))

For some reason, SSRS (2005 anyway) does not like a divide by zero with any decimals anywhere in the equation, it seems, which is why (I guess) I'm technically changing the "A" decimal to an int???

Don't know, but glad it works.  ;-)

Ran into even another snafu with this.  So this is what I had to use.

=iif(B = 0, 0, iif(B = 0, 0, A) / iif(B = 0, 0, B))

Now this works for all the cases I've run into so far.  :-D

I think the cause of that ERROR would have been because both A and B were decimals but your zeros and 1 were integers.

I presume the following (using Gerald's example) should work because you're always using the same data types:

=iif(B=0.0, 0.0, A / iif(B = 0.0, 1.0, B))

Cheers.

 

=iif(B=0, 0, A / iif(B=0, 1, B)) this helped me.

Thanks