Divide by zero error

[darwin]

Hi all. I am succesfully catching divide by zero errors using IIF
statements, all of the other expressions work fine except for this one below.
The proc that feeds the report does return 0's if there is no data. As of
now both fields have a value of zero, as time goes by there will be data. I
have other expressions that are all 0 and do not have any issues. I have
tried isNull, isNothing and the IIF. There are NO errors when I switch from
Layout to Preview, but the field has a ' #Error ' in preview mode. I am
sure it is somthing simple and obvious, but I am not seeing it. Can anyone
point me in the right direction? Thanks.
=IIF (Sum(Fields!lyYtdTotalWomen.Value,"proc" ) > 0 ,
Sum(Fields!lyYtdWomensClothing.Value,"proc") /
Sum(Fields!YtdTotalWomen.Value,"proc" ) , 0 )

[Mich...@gmail.com]

Try:
=iif((Sum(Fields!lyYtdWomensClothing.Value,"proc") /
Sum(Fields!YtdTotalWomen.Value,"proc" )) is
nothing,0,Sum(Fields!lyYtdWomensClothing.Value,"proc") /
Sum(Fields!YtdTotalWomen.Value,"proc" ))

[Alien2_51]

Are you sure lyYtdWomensClothing is not returning zero...? Try using this
Sum(Fields!lyYtdTotalWomen.Value,"proc" ) > 0 AND
Sum(Fields!lyYtdWomensClothing.Value,"proc" ) > 0
for your IIF expression.

[darwin]

nope, get a new error ... don't understand that one, the dataset is defined
on all fields in the expression.

h:\visual studio projects\paqs\PAQS Sales and Production.rdl The value
expression for the textbox ‘textbox170’ refers to the field ‘YtdTotalWomen’.
Report item expressions can only refer to fields within the current data set
scope or, if inside an aggregate, the specified data set scope.

[Abe]

I was under the impressiong that if you divide by 0 that Iif(a=0, 0,
b/a) was not enough. It will still try to evaluate both the true and
false parts before it picks which one to use. This has worked for me
in the past,

Iif(a=0, 0, b/Iif(a=0,1,a))
b/1 will never be the result but this will always evaluate.

I just deployed a custom assembly so I didn't have to write it like
this everyplace i wanted to use division.

Abe

[Mich...@gmail.com]

I think including the "proc" was causing some kind of error when it is
the expression being evaluated in an iif, try this and if that doesnt
work im out of ideas, i did have this same problem earlier, and the
format of this snippet below solved it.
=iif((Sum(Fields!lyYtdWomensClothing.Value) /
Sum(Fields!YtdTotalWomen.Value)) =
nothing,0,Sum(Fields!lyYtdWomensClothing.Value) /
Sum(Fields!YtdTotalWomen.Value))

[rsw8n]

I ran into the same situation. To solve it I set the textbox to hidden
if the divisor was zero. The true and false parts were always
evaluated giving me the error otherwise.

Roger

[darwin]

If I remove the "proc" data set I get errors, there is more than one data set
in the report.

h:\visual studio projects\paqs\PAQS Sales and Production.rdl The value

expression for the textbox ‘textbox170’ uses an aggregate expression without
a scope. A scope is required for all aggregates used outside of a data
region unless the report contains exactly one data set.

if I use this expression, there are no errors.. except for the " #Error "
message displayed on the preview page.
=iif( Sum(Fields!lyYtdTotalWomen.Value,"proc")
=0,0 , Sum(Fields!lyYtdWomensClothing.Value,"proc") /
Sum(Fields!lyYtdTotalWomen.Value,"proc")
)

Using Abe's Iif(a=0, 0, b/Iif(a=0,1,a)) formula, everthing is working and
happy! Thanks Greatly!!

[DJanson]

I'm having similar problems with IIF and 'divide by zero' and liked your
solution below, but it still tells me it's dividing by zero. I'm also
retrieving the data from a stored procedure and have just the one dataset.
I even went as far as 'cast'ing all the data fields into the same datatype,
but that didn't help. Has anyone gotten the following IIF statement to work?

Here's my IIF statement, maybe I missed something.............

=iif((Fields!WNMHRS.Value=0),0.00,(
Fields!WJCHRS.Value/iif(Fields!WNMHRS.Value=0),1,Fields!WNMHRS.Value))

Thanks

[Abe]

> =iif((Fields!WNMHRS.Value=0),0.00,(
>
Fields!WJCHRS.Value/iif(Fields!WNMHRS.Value=0),1,Fields!WNMHRS.Value))
>

Should be
=iif((Fields!WNMHRS.Value=0),0.00,
Fields!WJCHRS.Value/iif(Fields!WNMHRS.Value=0,1,Fields!WNMHRS.Value))
I just took up the parenthesis in the front of the False part of the
first iff and removed an extra one in the middle of the second iif. I
think that should be ok...

Abe

[DJanson]

Hope I didn't look too stupid, I really tried LOTS of combinations using
parentheses and not using parentheses. I'm not used to seeing problems
putting parens around the conditional part of the statement, but guess that's
an issue here. Thanks.

[Abe]

Yeah, no problem...I do it all the time. Sometimes its easier when
someone else looks at it :)

Abe

[Mark]

I found a strange way to correct. Depending on your data accuracy
requirements, this may not work for you.
There is another post with instructions to hide the field if the divisor is
zero. This is the only way I have found if you need to show a zero on a
report. I am sure the math experts will not be happy with this one, but here
it is. Make sure to calculate your data & see how much inaccuracies it will
introduce. Use at your own risk!

Basically the both sides of the IIF statement are evaluated. Even though the
IIF should see the criteria & return "0", it doesn't.
I have found if you add a small number to the divisor, you don't see the
error.

=IIF (Sum(Fields!lyYtdTotalWomen.Value,"proc" ) > 0 ,

Sum(Fields!lyYtdWomensClothing.Value,"proc") /(
Sum(Fields!YtdTotalWomen.Value,"proc" ) + .0000000001 , 0 )

By adding .0000000001 you will not see the error, and assuming you are
calculating to 2 decimals, your accuracy should not be affected.
I have ran numerous scenarios & have not seen an accuracy problem when
rounding to 2 decimal points. I realize this is not the ideal solution as
it introduces inaccuracy. Hopefully in future versions there is another way
to handle.

Regards,
MB

"darwin" <dar...@discussions.microsoft.com> wrote in message
news:7755D6FB-A5BD-41E4...@microsoft.com...

[Clayton Groom]

I finally got tired of writing cryptic IIF() expressions in order to handle
divide by zero conditions and wrote the following report function that can be
added to any report. it simplifies the appearance of the code, and lets you
specify an alternate value such as zero or nothing when divide by zero
occurs. I hope this is helpful...

' Handle divide by zero gracefully
' simpler than trying to use IIF()
Public Function CalcRatio(ByVal Numerator As Object, ByVal Denominator As
object, ByVal DivZeroDefault As Object) As Object
If Denominator <> 0 Then
Return Numerator/Denominator
Else
Return DivZeroDefault
End If
End Function

Steps:

From the menu, choose “Report”, “Report Properties”.
Click on the “Code” tab and past the above code into the window.
Click on “OK”

To use the function, you have to reference the “code” collection in an
expression:

=code.CalcRatio( Fields!PYGrossProfit.Value, Fields!PYSales.Value, Nothing)

Or if you want a zero instead of a blank:

=code.CalcRatio( Fields!PYGrossProfit.Value, Fields!PYSales.Value, 0)

Enjoy!
--
Clayton Groom
Covenant Technology Parnters, LLC

[Carl Henthorn]

I realize this thread is almost 2 years old, but I hope someone can help out.
I have inserted the code below into my RS 2005 report,and get an error on
line 0.
"There is an error on line 0 of custom code: [BC30203] Identifier expected"
can anyone help?
thanks in advance

[Andrew Hayes]

I'm very surprised no-one saw the problem with this:

=IIF (Sum(Fields!lyYtdTotalWomen.Value,"proc" ) > 0 ,
Sum(Fields!lyYtdWomensClothing.Value,"proc") /
Sum(Fields!YtdTotalWomen.Value,"proc" ) , 0 )

The IIF is checking lyYtdTotalWomen.Value, while the value used for division
is YtdTotalWomen.Value. Note the missing ly prefix. That makes them 2
different fields.