MS SQL Server Reporting Services - Reportng Services #Error in IIF

Author

Reportng Services #Error in IIF

gogulaa@gmail.com

2005-11-29, 3:23 am

I have this very weird problem arising when I try to use the following
function in Reporting Services 2000:

IIf ( 4=4 , 0 , 4/0) - Displays #Error
IIf ( 4=4 , 0 , 4/1) - Displays 0

Can somone please explain why this occurs and any work around for it.
In the Actual scenario I'm doing something like this:

IIf ( x=0, 0, y/x)

When x is 0 it gives #Error
When it is not it displays the value of y/x

Thanks a lot

Sumit Pilankar

2005-11-29, 7:23 am

Hi,

Actually the reporting services evaluates all values irrespective of whether
that value is reached or not. Even i found this weird.

In your case, it tries to evaluate 4/0 which produces an error and hence the
result.

Bye
Sumit Pilankar
<gogulaa@gmail.com> wrote in message
news:1133251786.777196.173580@z14g2000cwz.googlegroups.com...
>I have this very weird problem arising when I try to use the following
> function in Reporting Services 2000:
>
> IIf ( 4=4 , 0 , 4/0) - Displays #Error
> IIf ( 4=4 , 0 , 4/1) - Displays 0
>
> Can somone please explain why this occurs and any work around for it.
> In the Actual scenario I'm doing something like this:
>
> IIf ( x=0, 0, y/x)
>
> When x is 0 it gives #Error
> When it is not it displays the value of y/x
>
> Thanks a lot
>

FredG

2005-11-29, 9:24 am

You are receiving this error because the 4/0 is an illegal math function.
Cannot divide by zero.

"gogulaa@gmail.com" wrote:

> I have this very weird problem arising when I try to use the following
> function in Reporting Services 2000:
>
> IIf ( 4=4 , 0 , 4/0) - Displays #Error
> IIf ( 4=4 , 0 , 4/1) - Displays 0
>
> Can somone please explain why this occurs and any work around for it.
> In the Actual scenario I'm doing something like this:
>
> IIf ( x=0, 0, y/x)
>
> When x is 0 it gives #Error
> When it is not it displays the value of y/x
>
> Thanks a lot
>
>

Dale Grimsley

2005-11-29, 9:24 am

The way we get around it is to create a function in the report and call that
passing the X and Y parameters.

Dale

<gogulaa@gmail.com> wrote in message
news:1133251786.777196.173580@z14g2000cwz.googlegroups.com...
>I have this very weird problem arising when I try to use the following
> function in Reporting Services 2000:
>
> IIf ( 4=4 , 0 , 4/0) - Displays #Error
> IIf ( 4=4 , 0 , 4/1) - Displays 0
>
> Can somone please explain why this occurs and any work around for it.
> In the Actual scenario I'm doing something like this:
>
> IIf ( x=0, 0, y/x)
>
> When x is 0 it gives #Error
> When it is not it displays the value of y/x
>
> Thanks a lot
>

toolman

2005-11-29, 8:24 pm

Not the first time I've seen this with SQLRS and don't know why it
happens as your logic is sound.
Try something like
=IIF( x != 0, y/x, 0) or maybe
=IIF( x= 0, x, y/x) or even
=IIF( (x=0) = "True", 0, y/x)
and see if one of those helps.
Or for some adventure, try inserting this custom code under report
properties then just use the custom function populated with your x and
y values. "=DivideBy(y,x)" This has never failed me.

Public function DivideBy (exp1,exp2)
If exp2 = 0
DivideBy = 0
else
DivideBy = exp1/exp2
end if
end function

gogulaa@gmail.com wrote:
> I have this very weird problem arising when I try to use the following
> function in Reporting Services 2000:
>
> IIf ( 4=4 , 0 , 4/0) - Displays #Error
> IIf ( 4=4 , 0 , 4/1) - Displays 0
>
> Can somone please explain why this occurs and any work around for it.
> In the Actual scenario I'm doing something like this:
>
> IIf ( x=0, 0, y/x)
>
> When x is 0 it gives #Error
> When it is not it displays the value of y/x
>
> Thanks a lot

Sponsored Links