IIF statement issue

Here is the iif statement I have in the report:
=iif ((Sum(Fields!EXTENDED_MARGIN.Value) = 0) or
(SUM(Fields!TY_QTY.Value)=0), 0,
SUM(Fields!EXTENDED_MARGIN.Value)/SUM(Fields!TY_QTY.Value))
If I put in just the basic iif statment evaluation returning a 1 for true
and a 2 for false, it works fine. If I put in the expression I have for
false it will work fine unless one of the fields is a 0 and the other has a
number, then I get an error (which is understandable). I put them together
and I get the same thing as if I just put the false expression in, an error
if one number is a 0 and the other is an actual number.
Can anyone shed some light on this please?Not sure if there is another work around, however, I have run into this
problem many times and have solved it in several different ways. The probelm
that I see is that the IIF statement is calculating both the true and false
parts of the statement. Thus, you have a divide bt zero whenever
Fields!TY_Qty.Value = 0 even though you are trying to trap this condition in
the first part of the expression. Note that testing for
Fields!Extended_margin.value = 0 does not need to be done.
The first way to applies if you are using a stored proc to return the
dataset. If this is the case then you can in many cases return a field that
is already populated with
SUM(Fields!EXTENDED_MARGIN.Value)/SUM(Fields!TY_QTY.Value);.taking care to
handle the division by zero potential in your TSQL code.
The other way that I have handled the problem is by writing a procedure that
takes the two numbers and returns zero if SUM(Fields!TY_QTY.Value) = 0 or
else SUM(Fields!EXTENDED_MARGIN.Value)/SUM(Fields!TY_QTY.Value). Then instead
of using the IIF statement in an expression you call you procedure like this
=Code.MyProcedure( SUM(Fields!EXTENDED_MARGIN.Value),
SUM(Fields!TY_QTY.Value)).
Like I said, there may be other solutions but perhaps this will help.
"TBraun" wrote:
> Here is the iif statement I have in the report:
> =iif ((Sum(Fields!EXTENDED_MARGIN.Value) = 0) or
> (SUM(Fields!TY_QTY.Value)=0), 0,
> SUM(Fields!EXTENDED_MARGIN.Value)/SUM(Fields!TY_QTY.Value))
> If I put in just the basic iif statment evaluation returning a 1 for true
> and a 2 for false, it works fine. If I put in the expression I have for
> false it will work fine unless one of the fields is a 0 and the other has a
> number, then I get an error (which is understandable). I put them together
> and I get the same thing as if I just put the false expression in, an error
> if one number is a 0 and the other is an actual number.
> Can anyone shed some light on this please?|||Mark,
Thanks. I used your second suggestion and wrote a custom function and that
worked just fine. Appreciate the help!
"B. Mark McKinney" wrote:
> Not sure if there is another work around, however, I have run into this
> problem many times and have solved it in several different ways. The probelm
> that I see is that the IIF statement is calculating both the true and false
> parts of the statement. Thus, you have a divide bt zero whenever
> Fields!TY_Qty.Value = 0 even though you are trying to trap this condition in
> the first part of the expression. Note that testing for
> Fields!Extended_margin.value = 0 does not need to be done.
> The first way to applies if you are using a stored proc to return the
> dataset. If this is the case then you can in many cases return a field that
> is already populated with
> SUM(Fields!EXTENDED_MARGIN.Value)/SUM(Fields!TY_QTY.Value);.taking care to
> handle the division by zero potential in your TSQL code.
> The other way that I have handled the problem is by writing a procedure that
> takes the two numbers and returns zero if SUM(Fields!TY_QTY.Value) = 0 or
> else SUM(Fields!EXTENDED_MARGIN.Value)/SUM(Fields!TY_QTY.Value). Then instead
> of using the IIF statement in an expression you call you procedure like this
> =Code.MyProcedure( SUM(Fields!EXTENDED_MARGIN.Value),
> SUM(Fields!TY_QTY.Value)).
> Like I said, there may be other solutions but perhaps this will help.
> "TBraun" wrote:
> > Here is the iif statement I have in the report:
> >
> > =iif ((Sum(Fields!EXTENDED_MARGIN.Value) = 0) or
> > (SUM(Fields!TY_QTY.Value)=0), 0,
> > SUM(Fields!EXTENDED_MARGIN.Value)/SUM(Fields!TY_QTY.Value))
> >
> > If I put in just the basic iif statment evaluation returning a 1 for true
> > and a 2 for false, it works fine. If I put in the expression I have for
> > false it will work fine unless one of the fields is a 0 and the other has a
> > number, then I get an error (which is understandable). I put them together
> > and I get the same thing as if I just put the false expression in, an error
> > if one number is a 0 and the other is an actual number.
> >
> > Can anyone shed some light on this please?