Iif statement to prevent divide by zero?

Hi- I'm trying to create a calculated field that is the percentage difference
between two database fields. To prevent a divide by zero, I tried making it:
= Iif( Fields!dsPrice.Value <> 0, (Fields!eePrice.Value -
Fields!dsPrice.Value) / Fields!dsPrice.Value, 1)
This should provide the % diff, or in the case that dsPrice is 0, 1 (100%).
When I try to run the report, however, it comes back as a divide by zero for
fields where dsPrice = 0. Does reporting services evaluate both portions of
the Iif, then output one? How do I avoid this divide by zero error?
Thanks in advance!
Peter L.iif always evaluates both sides. try using the short circuit operator
'andalso' or 'orelse' in a function and add it to the code and call it from
the expression.|||If the correct zero value is 100%, you can just move the pieces around like
this:
= Iif(Fields!dsPrice.Value = 0, 1, Fields!eePrice.Value ) /
Iif(Fields!eePrice.Value = 0, 1, Fields!dsPrice.Value)
That way, the division doesn't happen at all until the values are replaced.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"plandry@.newsgroups.nospam"
<plandrynewsgroupsnospam@.discussions.microsoft.com> wrote in message
news:4B6AE1CA-72DB-4DAE-8E63-147AF61BDC21@.microsoft.com...
> Hi- I'm trying to create a calculated field that is the percentage
> difference
> between two database fields. To prevent a divide by zero, I tried making
> it:
> = Iif( Fields!dsPrice.Value <> 0, (Fields!eePrice.Value -
> Fields!dsPrice.Value) / Fields!dsPrice.Value, 1)
> This should provide the % diff, or in the case that dsPrice is 0, 1
> (100%).
> When I try to run the report, however, it comes back as a divide by zero
> for
> fields where dsPrice = 0. Does reporting services evaluate both portions
> of
> the Iif, then output one? How do I avoid this divide by zero error?
> Thanks in advance!
> Peter L.|||Whoops, I think that should have been more like this:
= Iif(Fields!dsPrice.Value = 0, 1, Fields!eePrice.Value ) /
Iif(Fields!dsPrice.Value = 0, 1, Fields!dsPrice.Value)
Anyway, you get the idea!! :-)
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
news:eGkBJLo8EHA.1264@.TK2MSFTNGP12.phx.gbl...
> If the correct zero value is 100%, you can just move the pieces around
> like this:
> = Iif(Fields!dsPrice.Value = 0, 1, Fields!eePrice.Value ) /
> Iif(Fields!eePrice.Value = 0, 1, Fields!dsPrice.Value)
> That way, the division doesn't happen at all until the values are
> replaced.
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "plandry@.newsgroups.nospam"
> <plandrynewsgroupsnospam@.discussions.microsoft.com> wrote in message
> news:4B6AE1CA-72DB-4DAE-8E63-147AF61BDC21@.microsoft.com...
>> Hi- I'm trying to create a calculated field that is the percentage
>> difference
>> between two database fields. To prevent a divide by zero, I tried making
>> it:
>> = Iif( Fields!dsPrice.Value <> 0, (Fields!eePrice.Value -
>> Fields!dsPrice.Value) / Fields!dsPrice.Value, 1)
>> This should provide the % diff, or in the case that dsPrice is 0, 1
>> (100%).
>> When I try to run the report, however, it comes back as a divide by zero
>> for
>> fields where dsPrice = 0. Does reporting services evaluate both portions
>> of
>> the Iif, then output one? How do I avoid this divide by zero error?
>> Thanks in advance!
>> Peter L.
>|||That did the trick... Thanks a bunch!
I will file that away in the "ninja reporting tricks" :)
"Jeff A. Stucker" wrote:
> Whoops, I think that should have been more like this:
> = Iif(Fields!dsPrice.Value = 0, 1, Fields!eePrice.Value ) /
> Iif(Fields!dsPrice.Value = 0, 1, Fields!dsPrice.Value)
> Anyway, you get the idea!! :-)
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
> news:eGkBJLo8EHA.1264@.TK2MSFTNGP12.phx.gbl...
> > If the correct zero value is 100%, you can just move the pieces around
> > like this:
> >
> > = Iif(Fields!dsPrice.Value = 0, 1, Fields!eePrice.Value ) /
> > Iif(Fields!eePrice.Value = 0, 1, Fields!dsPrice.Value)
> >
> > That way, the division doesn't happen at all until the values are
> > replaced.
> >
> > --
> > Cheers,
> >
> > '(' Jeff A. Stucker
> > \
> >
> > Business Intelligence
> > www.criadvantage.com
> > ---
> > "plandry@.newsgroups.nospam"
> > <plandrynewsgroupsnospam@.discussions.microsoft.com> wrote in message
> > news:4B6AE1CA-72DB-4DAE-8E63-147AF61BDC21@.microsoft.com...
> >> Hi- I'm trying to create a calculated field that is the percentage
> >> difference
> >> between two database fields. To prevent a divide by zero, I tried making
> >> it:
> >> = Iif( Fields!dsPrice.Value <> 0, (Fields!eePrice.Value -
> >> Fields!dsPrice.Value) / Fields!dsPrice.Value, 1)
> >> This should provide the % diff, or in the case that dsPrice is 0, 1
> >> (100%).
> >> When I try to run the report, however, it comes back as a divide by zero
> >> for
> >> fields where dsPrice = 0. Does reporting services evaluate both portions
> >> of
> >> the Iif, then output one? How do I avoid this divide by zero error?
> >>
> >> Thanks in advance!
> >> Peter L.
> >
> >
>
>