IIF Grrrr

I'm using an IIF statement to prevent the #Error when my formula is trying to divide by zero. IIF(FieldA=0,0,FieldB/FieldA).
It is still giving me an #Error for the fields where denominator is zero--works great for all the others, but that defeats the purpose of the IIF.
I've tried sanity checks where I have even changed it to IIF(FieldA=0,0,"NO") or IIF(FieldA=0,0,20/0) and it knows to put a zero then.
I know I have used this successfully elsewhere--only difference that is new to me is that FieldA is a ReportItems field and FieldB is a sum(Fields!). Don't know if that matters--rather than lose my sanity on a Monday I thought I would see if this is a good forum for my crazy questions.
Thanks in advance!
From http://www.developmentnow.com/g/115_2007_4_0_0_0/sql-server-reporting-services.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.comElle,
I usually like to put a function in the Report level (Report
properties-->Code tab) code to handle divide by zero. It looks something
like this:
Public Function CalcAvg(dblNum As Double, dblDen As Double) as Object
if (dblDen = nothing) or (dblDen = 0)
CalcAvg = 0
else
CalcAvg = dblNum / dblDen
end if
End Function
From within my report, I call it like this
=Code.CalcAvg(Fields!field1.value, Fields!field2.value)
Hope this helps
--
Andy Potter
blog : http://sqlreportingservices.spaces.live.com
info@.(NOSPAM)lakeclaireenterprises.com
"Elle" <nospam@.developmentnow.com> wrote in message
news:967b2d74-9c26-4a85-bd01-45c75dbfdb82@.developmentnow.com...
> I'm using an IIF statement to prevent the #Error when my formula is trying
> to divide by zero. IIF(FieldA=0,0,FieldB/FieldA).
> It is still giving me an #Error for the fields where denominator is
> zero--works great for all the others, but that defeats the purpose of the
> IIF.
> I've tried sanity checks where I have even changed it to
> IIF(FieldA=0,0,"NO") or IIF(FieldA=0,0,20/0) and it knows to put a zero
> then.
> I know I have used this successfully elsewhere--only difference that is
> new to me is that FieldA is a ReportItems field and FieldB is a
> sum(Fields!). Don't know if that matters--rather than lose my sanity on a
> Monday I thought I would see if this is a good forum for my crazy
> questions.
> Thanks in advance!
> From
> http://www.developmentnow.com/g/115_2007_4_0_0_0/sql-server-reporting-services.htm
> Posted via DevelopmentNow.com Groups
> http://www.developmentnow.com|||Thanks Andy. Somehow I thought it would be more straightforward this way but it is probably something I should be doing at the report level. I'll give it a try. Do you happen to know if one way can be touted as more efficient than the other? Is the function streamlining the whole process a little better too? Just curious.
Thanks,
ELL
From http://www.developmentnow.com/groups/viewthread.aspx?newsgroupid=115&threadid=95299
Posted via DevelopmentNow.com Group
http://www.developmentnow.com|||From a pure maintenance/debugging standpoint, it is far more efficient. From
a performance standpoint I don't know of any significant difference.
What I really like to do is put functions like this in an assembly and then
simply refer to the assembly in all my reports. That gives me the full .NET
language (VB.NET or C#) and lots of code reuse across multiple reports for
common functions like this.
--
Andy Potter
blog : http://sqlreportingservices.spaces.live.com
info@.(NOSPAM)lakeclaireenterprises.com
"Elle" <nospam@.developmentnow.com> wrote in message
news:5132d019-b8ad-4444-937c-0b7335ee7515@.developmentnow.com...
> Thanks Andy. Somehow I thought it would be more straightforward this way
> but it is probably something I should be doing at the report level. I'll
> give it a try. Do you happen to know if one way can be touted as more
> efficient than the other? Is the function streamlining the whole process
> a little better too? Just curious.
> Thanks,
> ELLE
> From
> http://www.developmentnow.com/groups/viewthread.aspx?newsgroupid=115&threadid=952992
> Posted via DevelopmentNow.com Groups
> http://www.developmentnow.com|||On Apr 2, 5:59 pm, Elle<nos...@.developmentnow.com> wrote:
> I'm using an IIF statement to prevent the #Error when my formula is trying to divide by zero. IIF(FieldA=0,0,FieldB/FieldA).
> It is still giving me an #Error for the fields where denominator is zero--works great for all the others, but that defeats the purpose of the IIF.
> I've tried sanity checks where I have even changed it to IIF(FieldA=0,0,"NO") or IIF(FieldA=0,0,20/0) and it knows to put a zero then.
> I know I have used this successfully elsewhere--only difference that is new to me is that FieldA is a ReportItems field and FieldB is a sum(Fields!). Don't know if that matters--rather than lose my sanity on a Monday I thought I would see if this is a good forum for my crazy questions.
> Thanks in advance!
> Fromhttp://www.developmentnow.com/g/115_2007_4_0_0_0/sql-server-reporting...
> Posted via DevelopmentNow.com Groupshttp://www.developmentnow.com
Hi Elle/All,
when I studied RS in great detail to see how I could get the best out
of it I determined that (in my opinion) as much processing and
calculation as possible should be placed into a custom assembly and
called....although this adds a little to run time the centralisation
of all code into one place that can be called by all reports seems
very much worth it...now we are a year down the track I feel this
choice has been validated many times over and we have put vast amounts
of code into the custom assembly and all but eliminated any code
inside a report....
Just my 2 cents worth!
Best Regards
Peter
www.peternolan.com|||All the MS environments I've worked with require an IIF statement to evaluate
the entire statement before evaluating the "IF clause", rather than
evaluating the "IF clause" and proceding to the relevant branch. So, in the
case of the "divide by zero" example, the IIF statement still has to attempt
to perform the FieldB/FieldA calculation in order to process the IIF
statement, rather than evaluating whether or not FieldA is > 0 and proceding
to the first branch rather than the second. Andy's suggested code has the
great advantage of skipping the divide-by-zero branch altogether.
"Elle" wrote:
> I'm using an IIF statement to prevent the #Error when my formula is trying to divide by zero. IIF(FieldA=0,0,FieldB/FieldA).
> It is still giving me an #Error for the fields where denominator is zero--works great for all the others, but that defeats the purpose of the IIF.
> I've tried sanity checks where I have even changed it to IIF(FieldA=0,0,"NO") or IIF(FieldA=0,0,20/0) and it knows to put a zero then.
> I know I have used this successfully elsewhere--only difference that is new to me is that FieldA is a ReportItems field and FieldB is a sum(Fields!). Don't know if that matters--rather than lose my sanity on a Monday I thought I would see if this is a good forum for my crazy questions.
> Thanks in advance!
> From http://www.developmentnow.com/g/115_2007_4_0_0_0/sql-server-reporting-services.htm
> Posted via DevelopmentNow.com Groups
> http://www.developmentnow.com
>|||Just to advise, the reason for the problem is the when IIF is
evaluated, BOTH the 'true part' and the 'false part' are always
evaluated - so either part can cause such an error, even if (in a
particular case) it is not the part that would be returned.|||On Apr 4, 9:05 am, "Parker" <psm...@.iquest.net> wrote:
> Just to advise, the reason for the problem is the when IIF is
> evaluated, BOTH the 'true part' and the 'false part' are always
> evaluated - so either part can cause such an error, even if (in a
> particular case) it is not the part that would be returned.
For this particular case, the following should work without error:
IIF(FieldA=0, 0, FieldB / IIF(FieldA=0, 1, FieldA))
If FieldA is 0, the 'false part' will evaluate to FieldB/1 instead of
FieldB/0 which was causing the error.|||Thanks all. Very very helpful forum.
ELLE
From http://www.developmentnow.com/groups/viewthread.aspx?newsgroupid=115&threadid=95299
Posted via DevelopmentNow.com Group
http://www.developmentnow.com