From sample posted, I tried the following but results in #error. Any
suggestion?
Here is the actual expression:
= Iif(Fields!cprice.Value - Fields!ccommission.Value = 0,0, (100 *
Fields!ccommission.Value/( Fields!cprice.Value- Fields!Average_Cost.Value)))I've had the same issue. To get around it, I've written a custom function
and then return the value to the report.
"Knolls" wrote:
> From sample posted, I tried the following but results in #error. Any
> suggestion?
> Here is the actual expression:
> = Iif(Fields!cprice.Value - Fields!ccommission.Value = 0,0, (100 *
> Fields!ccommission.Value/( Fields!cprice.Value- Fields!Average_Cost.Value)))|||this should work
= Iif(Fields!cprice.Value - Fields!ccommission.Value = 0,0, (100 *
Fields!ccommission.Value/iif(( Fields!cprice.Value-
Fields!Average_Cost.Value)=0,1,( Fields!cprice.Value-
Fields!Average_Cost.Value))))
"Knolls" wrote:
> From sample posted, I tried the following but results in #error. Any
> suggestion?
> Here is the actual expression:
> = Iif(Fields!cprice.Value - Fields!ccommission.Value = 0,0, (100 *
> Fields!ccommission.Value/( Fields!cprice.Value- Fields!Average_Cost.Value)))
Showing posts with label iif. Show all posts
Showing posts with label iif. Show all posts
Friday, March 9, 2012
Divided by zero
=IIF( Fields!DAILYRUNRT.Value > 0, Fields!DAILYRUNRT.Value /
Fields!GOL_DAILYRUNRT.Value * 100, 0)
Why this is throwing Divided by zero error
please help meBecause GOL_DAILYRUNRT.Value is zero? Perhaps you should put
IIF( Fields!GOL_DAILYRUNRT.Value > 0, Fields!DAILYRUNRT.Value /
Fields!GOL_DAILYRUNRT.Value * 100, 0)?
MC
"PrasantH" <PrasantH@.discussions.microsoft.com> wrote in message
news:AD3D844C-C52A-4205-84FE-3315AC41484E@.microsoft.com...
> =IIF( Fields!DAILYRUNRT.Value > 0, Fields!DAILYRUNRT.Value /
> Fields!GOL_DAILYRUNRT.Value * 100, 0)
> Why this is throwing Divided by zero error
> please help me|||As you are dividing by Fields!GOL_DAILYRUNRT.Value; I think you should
check if that is greater than zero.
Try this:
=IIF( Fields!GOL_DAILYRUNRT.Value = 0 ,0,Fields!DAILYRUNRT.Value/ IIF(
Fields!GOL_DAILYRUNRT.Value = 0,1, Fields!GOL_DAILYRUNRT.Value)
In any case, IIF is a function call and therefore all arguments are
evaluated before the function is invoked( which causes the division by zero
in expression).
"PrasantH" <PrasantH@.discussions.microsoft.com> wrote in message
news:AD3D844C-C52A-4205-84FE-3315AC41484E@.microsoft.com...
> =IIF( Fields!DAILYRUNRT.Value > 0, Fields!DAILYRUNRT.Value /
> Fields!GOL_DAILYRUNRT.Value * 100, 0)
> Why this is throwing Divided by zero error
> please help me|||You will need another ) at the end:
=IIF( Fields!GOL_DAILYRUNRT.Value = 0 ,0,Fields!DAILYRUNRT.Value/ IIF(
Fields!GOL_DAILYRUNRT.Value = 0,1, Fields!GOL_DAILYRUNRT.Value))
I have tried your expression as it is a more elegant solution to this
nagging problem than I have been using. Of course in my tests this morning I
could not get the simple expression IIF(FieldB.Value = 0, 0, FieldA.Value /
FieldB.Value) to fail. It returned zero instead of #error, NaN, infinity....
It seems as though RS evealuate both sides of the IIF statement sometimes
and only one side on other occassions. There seems to be no consistency at
all that I can figure out, however, I will definitely try your expression the
next time I need to code around this issue. Thanks for the posting.
"RA" wrote:
> As you are dividing by Fields!GOL_DAILYRUNRT.Value; I think you should
> check if that is greater than zero.
> Try this:
> =IIF( Fields!GOL_DAILYRUNRT.Value = 0 ,0,Fields!DAILYRUNRT.Value/ IIF(
> Fields!GOL_DAILYRUNRT.Value = 0,1, Fields!GOL_DAILYRUNRT.Value)
> In any case, IIF is a function call and therefore all arguments are
> evaluated before the function is invoked( which causes the division by zero
> in expression).
> "PrasantH" <PrasantH@.discussions.microsoft.com> wrote in message
> news:AD3D844C-C52A-4205-84FE-3315AC41484E@.microsoft.com...
> > =IIF( Fields!DAILYRUNRT.Value > 0, Fields!DAILYRUNRT.Value /
> > Fields!GOL_DAILYRUNRT.Value * 100, 0)
> > Why this is throwing Divided by zero error
> > please help me
>
>|||Note: IIF() is a function call. Therefore, all arguments get evaluated by
the CLR before the function is called. See also:
http://msdn.microsoft.com/library/en-us/vblr7/html/vafctiif.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"B. Mark McKinney" <BMarkMcKinney@.discussions.microsoft.com> wrote in
message news:31975EF8-B448-4748-85DD-FBFFF54DB634@.microsoft.com...
> You will need another ) at the end:
> =IIF( Fields!GOL_DAILYRUNRT.Value = 0 ,0,Fields!DAILYRUNRT.Value/ IIF(
> Fields!GOL_DAILYRUNRT.Value = 0,1, Fields!GOL_DAILYRUNRT.Value))
> I have tried your expression as it is a more elegant solution to this
> nagging problem than I have been using. Of course in my tests this morning
I
> could not get the simple expression IIF(FieldB.Value = 0, 0, FieldA.Value
/
> FieldB.Value) to fail. It returned zero instead of #error, NaN,
infinity....
> It seems as though RS evealuate both sides of the IIF statement sometimes
> and only one side on other occassions. There seems to be no consistency at
> all that I can figure out, however, I will definitely try your expression
the
> next time I need to code around this issue. Thanks for the posting.
>
> "RA" wrote:
> > As you are dividing by Fields!GOL_DAILYRUNRT.Value; I think you should
> > check if that is greater than zero.
> >
> > Try this:
> > =IIF( Fields!GOL_DAILYRUNRT.Value = 0 ,0,Fields!DAILYRUNRT.Value/ IIF(
> > Fields!GOL_DAILYRUNRT.Value = 0,1, Fields!GOL_DAILYRUNRT.Value)
> >
> > In any case, IIF is a function call and therefore all arguments are
> > evaluated before the function is invoked( which causes the division by
zero
> > in expression).
> >
> > "PrasantH" <PrasantH@.discussions.microsoft.com> wrote in message
> > news:AD3D844C-C52A-4205-84FE-3315AC41484E@.microsoft.com...
> > > =IIF( Fields!DAILYRUNRT.Value > 0, Fields!DAILYRUNRT.Value /
> > > Fields!GOL_DAILYRUNRT.Value * 100, 0)
> > > Why this is throwing Divided by zero error
> > > please help me
> >
> >
> >|||Consider using CUSTOM CODE (look it up in the help docs) that can
globally solve this for your whole report with a function like this:
Function SafeDivide(byval onumer as Object, byval odenom as Object) as
Decimal
' author: jerry nixon
' purpose: divide and avoid div by zero errs
' version: 1.4
If onumer Is Nothing Then Return 0
If odenom Is Nothing Then Return 0
If isDbNull(onumer) Then Return 0
If isDbNull(odenom) Then Return 0
Dim inumer As Decimal
Dim idenom As Decimal
Try
inumer = Ctype(onumer, Decimal)
idenom = Ctype(odenom, Decimal)
Catch ex as Exception
Return 0
End Try
If Not isNumeric(inumer) Then Return 0
If Not isNumeric(idenom) Then Return 0
If idenom = 0 Then Return 0
Try
Return Decimal.Divide(inumer, idenom)
Catch ex As Exception
Return 0
End Try
End Function
Fields!GOL_DAILYRUNRT.Value * 100, 0)
Why this is throwing Divided by zero error
please help meBecause GOL_DAILYRUNRT.Value is zero? Perhaps you should put
IIF( Fields!GOL_DAILYRUNRT.Value > 0, Fields!DAILYRUNRT.Value /
Fields!GOL_DAILYRUNRT.Value * 100, 0)?
MC
"PrasantH" <PrasantH@.discussions.microsoft.com> wrote in message
news:AD3D844C-C52A-4205-84FE-3315AC41484E@.microsoft.com...
> =IIF( Fields!DAILYRUNRT.Value > 0, Fields!DAILYRUNRT.Value /
> Fields!GOL_DAILYRUNRT.Value * 100, 0)
> Why this is throwing Divided by zero error
> please help me|||As you are dividing by Fields!GOL_DAILYRUNRT.Value; I think you should
check if that is greater than zero.
Try this:
=IIF( Fields!GOL_DAILYRUNRT.Value = 0 ,0,Fields!DAILYRUNRT.Value/ IIF(
Fields!GOL_DAILYRUNRT.Value = 0,1, Fields!GOL_DAILYRUNRT.Value)
In any case, IIF is a function call and therefore all arguments are
evaluated before the function is invoked( which causes the division by zero
in expression).
"PrasantH" <PrasantH@.discussions.microsoft.com> wrote in message
news:AD3D844C-C52A-4205-84FE-3315AC41484E@.microsoft.com...
> =IIF( Fields!DAILYRUNRT.Value > 0, Fields!DAILYRUNRT.Value /
> Fields!GOL_DAILYRUNRT.Value * 100, 0)
> Why this is throwing Divided by zero error
> please help me|||You will need another ) at the end:
=IIF( Fields!GOL_DAILYRUNRT.Value = 0 ,0,Fields!DAILYRUNRT.Value/ IIF(
Fields!GOL_DAILYRUNRT.Value = 0,1, Fields!GOL_DAILYRUNRT.Value))
I have tried your expression as it is a more elegant solution to this
nagging problem than I have been using. Of course in my tests this morning I
could not get the simple expression IIF(FieldB.Value = 0, 0, FieldA.Value /
FieldB.Value) to fail. It returned zero instead of #error, NaN, infinity....
It seems as though RS evealuate both sides of the IIF statement sometimes
and only one side on other occassions. There seems to be no consistency at
all that I can figure out, however, I will definitely try your expression the
next time I need to code around this issue. Thanks for the posting.
"RA" wrote:
> As you are dividing by Fields!GOL_DAILYRUNRT.Value; I think you should
> check if that is greater than zero.
> Try this:
> =IIF( Fields!GOL_DAILYRUNRT.Value = 0 ,0,Fields!DAILYRUNRT.Value/ IIF(
> Fields!GOL_DAILYRUNRT.Value = 0,1, Fields!GOL_DAILYRUNRT.Value)
> In any case, IIF is a function call and therefore all arguments are
> evaluated before the function is invoked( which causes the division by zero
> in expression).
> "PrasantH" <PrasantH@.discussions.microsoft.com> wrote in message
> news:AD3D844C-C52A-4205-84FE-3315AC41484E@.microsoft.com...
> > =IIF( Fields!DAILYRUNRT.Value > 0, Fields!DAILYRUNRT.Value /
> > Fields!GOL_DAILYRUNRT.Value * 100, 0)
> > Why this is throwing Divided by zero error
> > please help me
>
>|||Note: IIF() is a function call. Therefore, all arguments get evaluated by
the CLR before the function is called. See also:
http://msdn.microsoft.com/library/en-us/vblr7/html/vafctiif.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"B. Mark McKinney" <BMarkMcKinney@.discussions.microsoft.com> wrote in
message news:31975EF8-B448-4748-85DD-FBFFF54DB634@.microsoft.com...
> You will need another ) at the end:
> =IIF( Fields!GOL_DAILYRUNRT.Value = 0 ,0,Fields!DAILYRUNRT.Value/ IIF(
> Fields!GOL_DAILYRUNRT.Value = 0,1, Fields!GOL_DAILYRUNRT.Value))
> I have tried your expression as it is a more elegant solution to this
> nagging problem than I have been using. Of course in my tests this morning
I
> could not get the simple expression IIF(FieldB.Value = 0, 0, FieldA.Value
/
> FieldB.Value) to fail. It returned zero instead of #error, NaN,
infinity....
> It seems as though RS evealuate both sides of the IIF statement sometimes
> and only one side on other occassions. There seems to be no consistency at
> all that I can figure out, however, I will definitely try your expression
the
> next time I need to code around this issue. Thanks for the posting.
>
> "RA" wrote:
> > As you are dividing by Fields!GOL_DAILYRUNRT.Value; I think you should
> > check if that is greater than zero.
> >
> > Try this:
> > =IIF( Fields!GOL_DAILYRUNRT.Value = 0 ,0,Fields!DAILYRUNRT.Value/ IIF(
> > Fields!GOL_DAILYRUNRT.Value = 0,1, Fields!GOL_DAILYRUNRT.Value)
> >
> > In any case, IIF is a function call and therefore all arguments are
> > evaluated before the function is invoked( which causes the division by
zero
> > in expression).
> >
> > "PrasantH" <PrasantH@.discussions.microsoft.com> wrote in message
> > news:AD3D844C-C52A-4205-84FE-3315AC41484E@.microsoft.com...
> > > =IIF( Fields!DAILYRUNRT.Value > 0, Fields!DAILYRUNRT.Value /
> > > Fields!GOL_DAILYRUNRT.Value * 100, 0)
> > > Why this is throwing Divided by zero error
> > > please help me
> >
> >
> >|||Consider using CUSTOM CODE (look it up in the help docs) that can
globally solve this for your whole report with a function like this:
Function SafeDivide(byval onumer as Object, byval odenom as Object) as
Decimal
' author: jerry nixon
' purpose: divide and avoid div by zero errs
' version: 1.4
If onumer Is Nothing Then Return 0
If odenom Is Nothing Then Return 0
If isDbNull(onumer) Then Return 0
If isDbNull(odenom) Then Return 0
Dim inumer As Decimal
Dim idenom As Decimal
Try
inumer = Ctype(onumer, Decimal)
idenom = Ctype(odenom, Decimal)
Catch ex as Exception
Return 0
End Try
If Not isNumeric(inumer) Then Return 0
If Not isNumeric(idenom) Then Return 0
If idenom = 0 Then Return 0
Try
Return Decimal.Divide(inumer, idenom)
Catch ex As Exception
Return 0
End Try
End Function
Divide by Zero error
I am still getting this error. This is my formula:
=iif(ReportItems!BUDMTD_2.Value=0,0,ReportItems!MTDChange_2.value/ReportItems!BUDMTD_2.Value)
This works in another report but not here.IIF is a function call. Therefore all arguments get evaluated before IIF is
called and you see a division by zero.
Possible solutions can be:
1) Avoid 0 in divisor (consider
=iif(ReportItems!BUDMTD_2.Value=0,0,ReportItems!MTDChange_2.value /
iif(ReportItems!BUDMTD_2.Value = 0, 1,ReportItems!BUDMTD_2.Value ))
2) Write VB function SafeDivide (A,B) and call it when needed (search
"SafeDivide" in this group)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"David" <David@.discussions.microsoft.com> wrote in message
news:2FC621DF-8C78-480F-A9E6-512A438B75B5@.microsoft.com...
>I am still getting this error. This is my formula:
> =iif(ReportItems!BUDMTD_2.Value=0,0,ReportItems!MTDChange_2.value/ReportItems!BUDMTD_2.Value)
> This works in another report but not here.|||David,
just in case it helps...I got so tired of writing statements like this
and trying to test them (because you finish up writing a lot of them)
that I wrote my division routines into a custom assembly and called the
custom assembly...the custom assembly then gives me much more control
over how I deal with bad data in calculations...
Peter
www.peternolan.com
=iif(ReportItems!BUDMTD_2.Value=0,0,ReportItems!MTDChange_2.value/ReportItems!BUDMTD_2.Value)
This works in another report but not here.IIF is a function call. Therefore all arguments get evaluated before IIF is
called and you see a division by zero.
Possible solutions can be:
1) Avoid 0 in divisor (consider
=iif(ReportItems!BUDMTD_2.Value=0,0,ReportItems!MTDChange_2.value /
iif(ReportItems!BUDMTD_2.Value = 0, 1,ReportItems!BUDMTD_2.Value ))
2) Write VB function SafeDivide (A,B) and call it when needed (search
"SafeDivide" in this group)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"David" <David@.discussions.microsoft.com> wrote in message
news:2FC621DF-8C78-480F-A9E6-512A438B75B5@.microsoft.com...
>I am still getting this error. This is my formula:
> =iif(ReportItems!BUDMTD_2.Value=0,0,ReportItems!MTDChange_2.value/ReportItems!BUDMTD_2.Value)
> This works in another report but not here.|||David,
just in case it helps...I got so tired of writing statements like this
and trying to test them (because you finish up writing a lot of them)
that I wrote my division routines into a custom assembly and called the
custom assembly...the custom assembly then gives me much more control
over how I deal with bad data in calculations...
Peter
www.peternolan.com
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.
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.
Wednesday, March 7, 2012
Divide By 0
I am having some issues with expressions as I am attempting to perform the
following:
iif(budget=0,0,actual/budget)
it seems that regardless of the expression all clauses are evaluated.
The actual expression is as follows:
=iif( Sum(Fields!BudgetMTD.Value, "DepartmentDetGrp")=0,0,
Sum(Fields!ActualMTD.Value, "DepartmentDetGrp")/ Sum(Fields!BudgetMTD.Value,
"DepartmentDetGrp"))
Any suggestions to get around this?IIF is a function call which evaluates all arguments - hence the division by
zero.
Change the call to use the following pattern to avoid the issue:
=IIF(budget=0, 0, actual / IIF(budget = 0, 1, budget))
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"David Fuller" <DavidFuller@.discussions.microsoft.com> wrote in message
news:14E1BFB1-3D84-449F-819C-3C1C5DBD9288@.microsoft.com...
>I am having some issues with expressions as I am attempting to perform the
> following:
> iif(budget=0,0,actual/budget)
> it seems that regardless of the expression all clauses are evaluated.
> The actual expression is as follows:
> =iif( Sum(Fields!BudgetMTD.Value, "DepartmentDetGrp")=0,0,
> Sum(Fields!ActualMTD.Value, "DepartmentDetGrp")/
> Sum(Fields!BudgetMTD.Value,
> "DepartmentDetGrp"))
> Any suggestions to get around this?
following:
iif(budget=0,0,actual/budget)
it seems that regardless of the expression all clauses are evaluated.
The actual expression is as follows:
=iif( Sum(Fields!BudgetMTD.Value, "DepartmentDetGrp")=0,0,
Sum(Fields!ActualMTD.Value, "DepartmentDetGrp")/ Sum(Fields!BudgetMTD.Value,
"DepartmentDetGrp"))
Any suggestions to get around this?IIF is a function call which evaluates all arguments - hence the division by
zero.
Change the call to use the following pattern to avoid the issue:
=IIF(budget=0, 0, actual / IIF(budget = 0, 1, budget))
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"David Fuller" <DavidFuller@.discussions.microsoft.com> wrote in message
news:14E1BFB1-3D84-449F-819C-3C1C5DBD9288@.microsoft.com...
>I am having some issues with expressions as I am attempting to perform the
> following:
> iif(budget=0,0,actual/budget)
> it seems that regardless of the expression all clauses are evaluated.
> The actual expression is as follows:
> =iif( Sum(Fields!BudgetMTD.Value, "DepartmentDetGrp")=0,0,
> Sum(Fields!ActualMTD.Value, "DepartmentDetGrp")/
> Sum(Fields!BudgetMTD.Value,
> "DepartmentDetGrp"))
> Any suggestions to get around this?
Divde by Zero error
I to am getting the above error when trying to action the following
calculation.
=iif(CostValue=0,0,Profit/CostValue)
I have tried a number of the other solutions posted and these do not seem to
work in my instance.
Have tried to use Nz function but this is not included in Reporting
Services, also tried ISERROR this too failed.
Help me Obi Wan - you're my only hope....Try this:
=iif(Fields!CostValue.Value = 0, 0, Fields!Profit.Value /
iif(Fields!CostValue.Value = 0, 1, Fields!CostValue.Value))
Keep in mind that iif is a function call and therefore all arguments get
evaluated.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jules_Anime" <JulesAnime@.discussions.microsoft.com> wrote in message
news:0E7C5AA8-C96C-4CAE-97B5-67BF4BDF3C71@.microsoft.com...
> I to am getting the above error when trying to action the following
> calculation.
> =iif(CostValue=0,0,Profit/CostValue)
> I have tried a number of the other solutions posted and these do not seem
to
> work in my instance.
> Have tried to use Nz function but this is not included in Reporting
> Services, also tried ISERROR this too failed.
> Help me Obi Wan - you're my only hope....|||What is your data source? I trap divide by zero errors using a function in
SQL Server before the data is delivered to the report.
/*
Created by Vince Plaza
Last revised by Vince Plaza
Last revised on 6/29/2003
The divide by zero trap looks for a denominator of zero and skips the
division operation.
It also serves to round results to a desired number of decimal places.
*/
CREATE FUNCTION dbo.fnRPTS_DivideByZeroTrap (@.NUMERATOR AS FLOAT,
@.DENOMINATOR AS FLOAT, @.ROUND AS INT)
RETURNS FLOAT AS
BEGIN
DECLARE @.OUTPUT AS FLOAT
IF @.DENOMINATOR = 0
SELECT @.OUTPUT = 0
ELSE IF @.ROUND = -1 --Dont Round
SELECT @.OUTPUT = @.NUMERATOR/@.DENOMINATOR
ELSE
SELECT @.OUTPUT = ROUND((@.NUMERATOR*1.0)/ (@.DENOMINATOR*1.0),@.ROUND)
RETURN @.OUTPUT
END
"Jules_Anime" wrote:
> I to am getting the above error when trying to action the following
> calculation.
> =iif(CostValue=0,0,Profit/CostValue)
> I have tried a number of the other solutions posted and these do not seem to
> work in my instance.
> Have tried to use Nz function but this is not included in Reporting
> Services, also tried ISERROR this too failed.
> Help me Obi Wan - you're my only hope....|||Thanks Robert. This seemed to work fine.
Although I thought I had already followed this path, maybe I was "Lost in
Translation"
"Robert Bruckner [MSFT]" wrote:
> Try this:
> =iif(Fields!CostValue.Value = 0, 0, Fields!Profit.Value /
> iif(Fields!CostValue.Value = 0, 1, Fields!CostValue.Value))
> Keep in mind that iif is a function call and therefore all arguments get
> evaluated.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Jules_Anime" <JulesAnime@.discussions.microsoft.com> wrote in message
> news:0E7C5AA8-C96C-4CAE-97B5-67BF4BDF3C71@.microsoft.com...
> > I to am getting the above error when trying to action the following
> > calculation.
> >
> > =iif(CostValue=0,0,Profit/CostValue)
> >
> > I have tried a number of the other solutions posted and these do not seem
> to
> > work in my instance.
> >
> > Have tried to use Nz function but this is not included in Reporting
> > Services, also tried ISERROR this too failed.
> >
> > Help me Obi Wan - you're my only hope....
>
>|||Thks..I like the neatness of this solution, I think I went with a case
statment in the original SQL.
It ment however that I had to summarise the view firstly, and then report
from the summarised data.
Cheers.
"vmp_pdx" wrote:
> What is your data source? I trap divide by zero errors using a function in
> SQL Server before the data is delivered to the report.
> /*
> Created by Vince Plaza
> Last revised by Vince Plaza
> Last revised on 6/29/2003
> The divide by zero trap looks for a denominator of zero and skips the
> division operation.
> It also serves to round results to a desired number of decimal places.
> */
> CREATE FUNCTION dbo.fnRPTS_DivideByZeroTrap (@.NUMERATOR AS FLOAT,
> @.DENOMINATOR AS FLOAT, @.ROUND AS INT)
> RETURNS FLOAT AS
> BEGIN
> DECLARE @.OUTPUT AS FLOAT
> IF @.DENOMINATOR = 0
> SELECT @.OUTPUT = 0
> ELSE IF @.ROUND = -1 --Dont Round
> SELECT @.OUTPUT = @.NUMERATOR/@.DENOMINATOR
> ELSE
> SELECT @.OUTPUT = ROUND((@.NUMERATOR*1.0)/ (@.DENOMINATOR*1.0),@.ROUND)
> RETURN @.OUTPUT
> END
>
> "Jules_Anime" wrote:
> > I to am getting the above error when trying to action the following
> > calculation.
> >
> > =iif(CostValue=0,0,Profit/CostValue)
> >
> > I have tried a number of the other solutions posted and these do not seem to
> > work in my instance.
> >
> > Have tried to use Nz function but this is not included in Reporting
> > Services, also tried ISERROR this too failed.
> >
> > Help me Obi Wan - you're my only hope....
calculation.
=iif(CostValue=0,0,Profit/CostValue)
I have tried a number of the other solutions posted and these do not seem to
work in my instance.
Have tried to use Nz function but this is not included in Reporting
Services, also tried ISERROR this too failed.
Help me Obi Wan - you're my only hope....Try this:
=iif(Fields!CostValue.Value = 0, 0, Fields!Profit.Value /
iif(Fields!CostValue.Value = 0, 1, Fields!CostValue.Value))
Keep in mind that iif is a function call and therefore all arguments get
evaluated.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jules_Anime" <JulesAnime@.discussions.microsoft.com> wrote in message
news:0E7C5AA8-C96C-4CAE-97B5-67BF4BDF3C71@.microsoft.com...
> I to am getting the above error when trying to action the following
> calculation.
> =iif(CostValue=0,0,Profit/CostValue)
> I have tried a number of the other solutions posted and these do not seem
to
> work in my instance.
> Have tried to use Nz function but this is not included in Reporting
> Services, also tried ISERROR this too failed.
> Help me Obi Wan - you're my only hope....|||What is your data source? I trap divide by zero errors using a function in
SQL Server before the data is delivered to the report.
/*
Created by Vince Plaza
Last revised by Vince Plaza
Last revised on 6/29/2003
The divide by zero trap looks for a denominator of zero and skips the
division operation.
It also serves to round results to a desired number of decimal places.
*/
CREATE FUNCTION dbo.fnRPTS_DivideByZeroTrap (@.NUMERATOR AS FLOAT,
@.DENOMINATOR AS FLOAT, @.ROUND AS INT)
RETURNS FLOAT AS
BEGIN
DECLARE @.OUTPUT AS FLOAT
IF @.DENOMINATOR = 0
SELECT @.OUTPUT = 0
ELSE IF @.ROUND = -1 --Dont Round
SELECT @.OUTPUT = @.NUMERATOR/@.DENOMINATOR
ELSE
SELECT @.OUTPUT = ROUND((@.NUMERATOR*1.0)/ (@.DENOMINATOR*1.0),@.ROUND)
RETURN @.OUTPUT
END
"Jules_Anime" wrote:
> I to am getting the above error when trying to action the following
> calculation.
> =iif(CostValue=0,0,Profit/CostValue)
> I have tried a number of the other solutions posted and these do not seem to
> work in my instance.
> Have tried to use Nz function but this is not included in Reporting
> Services, also tried ISERROR this too failed.
> Help me Obi Wan - you're my only hope....|||Thanks Robert. This seemed to work fine.
Although I thought I had already followed this path, maybe I was "Lost in
Translation"
"Robert Bruckner [MSFT]" wrote:
> Try this:
> =iif(Fields!CostValue.Value = 0, 0, Fields!Profit.Value /
> iif(Fields!CostValue.Value = 0, 1, Fields!CostValue.Value))
> Keep in mind that iif is a function call and therefore all arguments get
> evaluated.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Jules_Anime" <JulesAnime@.discussions.microsoft.com> wrote in message
> news:0E7C5AA8-C96C-4CAE-97B5-67BF4BDF3C71@.microsoft.com...
> > I to am getting the above error when trying to action the following
> > calculation.
> >
> > =iif(CostValue=0,0,Profit/CostValue)
> >
> > I have tried a number of the other solutions posted and these do not seem
> to
> > work in my instance.
> >
> > Have tried to use Nz function but this is not included in Reporting
> > Services, also tried ISERROR this too failed.
> >
> > Help me Obi Wan - you're my only hope....
>
>|||Thks..I like the neatness of this solution, I think I went with a case
statment in the original SQL.
It ment however that I had to summarise the view firstly, and then report
from the summarised data.
Cheers.
"vmp_pdx" wrote:
> What is your data source? I trap divide by zero errors using a function in
> SQL Server before the data is delivered to the report.
> /*
> Created by Vince Plaza
> Last revised by Vince Plaza
> Last revised on 6/29/2003
> The divide by zero trap looks for a denominator of zero and skips the
> division operation.
> It also serves to round results to a desired number of decimal places.
> */
> CREATE FUNCTION dbo.fnRPTS_DivideByZeroTrap (@.NUMERATOR AS FLOAT,
> @.DENOMINATOR AS FLOAT, @.ROUND AS INT)
> RETURNS FLOAT AS
> BEGIN
> DECLARE @.OUTPUT AS FLOAT
> IF @.DENOMINATOR = 0
> SELECT @.OUTPUT = 0
> ELSE IF @.ROUND = -1 --Dont Round
> SELECT @.OUTPUT = @.NUMERATOR/@.DENOMINATOR
> ELSE
> SELECT @.OUTPUT = ROUND((@.NUMERATOR*1.0)/ (@.DENOMINATOR*1.0),@.ROUND)
> RETURN @.OUTPUT
> END
>
> "Jules_Anime" wrote:
> > I to am getting the above error when trying to action the following
> > calculation.
> >
> > =iif(CostValue=0,0,Profit/CostValue)
> >
> > I have tried a number of the other solutions posted and these do not seem to
> > work in my instance.
> >
> > Have tried to use Nz function but this is not included in Reporting
> > Services, also tried ISERROR this too failed.
> >
> > Help me Obi Wan - you're my only hope....
Subscribe to:
Posts (Atom)