Divide by zero error

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 )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" ))
darwin wrote:
> 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 )|||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" wrote:
> 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 )|||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.
"Michaema@.gmail.com" wrote:
> 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" ))
> darwin wrote:
> > 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 )
>|||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|||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))|||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|||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!!
"Alien2_51" wrote:
> 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" wrote:
> > 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 )|||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" wrote:
> 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
>|||> =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 wrote:
> 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" wrote:
> > 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
> >
> >|||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" wrote:
> > =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 wrote:
> > 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" wrote:
> >
> > > 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
> > >
> > >
>|||Yeah, no problem...I do it all the time. Sometimes its easier when
someone else looks at it :)
Abe
DJanson wrote:
> 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" wrote:
> > > =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 wrote:
> > > 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" wrote:
> > >
> > > > 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
> > > >
> > > >
> >
> >|||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" <darwin@.discussions.microsoft.com> wrote in message
news:7755D6FB-A5BD-41E4-BBBA-213907758B72@.microsoft.com...
> 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 )|||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|||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
"Clayton Groom" wrote:
> 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
>|||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.