IIF Statement Issue

Hi there.
There's an iif statement that I'm struggling with on Reporting Services.
This statement is on a calulated field. The iif statement is as follows:
=iif(x=0, 0, ((y-z)/x)*100).
In other words, if x is zero then 0 must be the output. Else the above
formula must be the output. The syntax is perfect, and so is the logic (i
think...). When I run the report it gives me a warning message that it cannot
"divide by zero". What is obviously happening is that x is zero, but instead
of displaying zero (as it is instructed to do so), it's going straight to the
formula!
Can anyone please tell me what's happening, and how I can make it work?
Thanks in advance.
(By the way, this has to be in Reporting Services and not sql)
--
Regards,You might have some cases where x is null which would not be getting handled
in your expression. add another iif test to check for nulls
"crmLearner" <crmLearner@.discussions.microsoft.com> wrote in message
news:1F6784D8-CBD8-494A-81DB-098E9E922CB4@.microsoft.com...
> Hi there.
> There's an iif statement that I'm struggling with on Reporting Services.
> This statement is on a calulated field. The iif statement is as follows:
> =iif(x=0, 0, ((y-z)/x)*100).
> In other words, if x is zero then 0 must be the output. Else the above
> formula must be the output. The syntax is perfect, and so is the logic (i
> think...). When I run the report it gives me a warning message that it
> cannot
> "divide by zero". What is obviously happening is that x is zero, but
> instead
> of displaying zero (as it is instructed to do so), it's going straight to
> the
> formula!
> Can anyone please tell me what's happening, and how I can make it work?
> Thanks in advance.
> (By the way, this has to be in Reporting Services and not sql)
> --
> Regards,|||Wow, i can't believe i didn't think of that...i'll give it a try.
Thanks!
--
Regards,
"Dean" wrote:
> You might have some cases where x is null which would not be getting handled
> in your expression. add another iif test to check for nulls
> "crmLearner" <crmLearner@.discussions.microsoft.com> wrote in message
> news:1F6784D8-CBD8-494A-81DB-098E9E922CB4@.microsoft.com...
> > Hi there.
> >
> > There's an iif statement that I'm struggling with on Reporting Services.
> > This statement is on a calulated field. The iif statement is as follows:
> > =iif(x=0, 0, ((y-z)/x)*100).
> >
> > In other words, if x is zero then 0 must be the output. Else the above
> > formula must be the output. The syntax is perfect, and so is the logic (i
> > think...). When I run the report it gives me a warning message that it
> > cannot
> > "divide by zero". What is obviously happening is that x is zero, but
> > instead
> > of displaying zero (as it is instructed to do so), it's going straight to
> > the
> > formula!
> >
> > Can anyone please tell me what's happening, and how I can make it work?
> > Thanks in advance.
> >
> > (By the way, this has to be in Reporting Services and not sql)
> > --
> > Regards,
>
>|||another thing to check for is empty strings, depending on how the data was
entered there may be "spaces" in the fields which are not 0 nor are they
null.
"crmLearner" <crmLearner@.discussions.microsoft.com> wrote in message
news:55531FF7-63F6-44C5-9F31-83A2C32B5BFA@.microsoft.com...
> Wow, i can't believe i didn't think of that...i'll give it a try.
> Thanks!
> --
> Regards,
>
> "Dean" wrote:
>> You might have some cases where x is null which would not be getting
>> handled
>> in your expression. add another iif test to check for nulls
>> "crmLearner" <crmLearner@.discussions.microsoft.com> wrote in message
>> news:1F6784D8-CBD8-494A-81DB-098E9E922CB4@.microsoft.com...
>> > Hi there.
>> >
>> > There's an iif statement that I'm struggling with on Reporting
>> > Services.
>> > This statement is on a calulated field. The iif statement is as
>> > follows:
>> > =iif(x=0, 0, ((y-z)/x)*100).
>> >
>> > In other words, if x is zero then 0 must be the output. Else the above
>> > formula must be the output. The syntax is perfect, and so is the logic
>> > (i
>> > think...). When I run the report it gives me a warning message that it
>> > cannot
>> > "divide by zero". What is obviously happening is that x is zero, but
>> > instead
>> > of displaying zero (as it is instructed to do so), it's going straight
>> > to
>> > the
>> > formula!
>> >
>> > Can anyone please tell me what's happening, and how I can make it work?
>> > Thanks in advance.
>> >
>> > (By the way, this has to be in Reporting Services and not sql)
>> > --
>> > Regards,
>>|||The parameters to the IIF function are all evaluated in order to call the
function. So in your case the CLR calculates three values to be passed as
parameters...
(x=0)
,(0)
,((y-z)/x)*100)
...and would then have internal logic that says if the first parameter is
true return the second parameter else the third parameter. But that logic
won't get fired as your third parameter is giving a divided by zero exception
when x is zero.
You could write a code function to go in the Report Properties' Code tab...
Function SafeDividePercent (ByVal numerator As Integer, ByVal denominator As
Integer) As Object
If denominator = 0 Then
Return 0
Else
Return ( numerator / denominator) *100.0
End If
End Function
... and then use it like this for a text box's Expression.
=Code.SafeDividePercent( Fields!y.Value - Fields!z.Value, Fields!x.Value)
Note that I've assumed your variables are integers but that the result isn't.
Someone may have another way of dealing with it.
Hope that helps,
Andrew|||Please ignore my previous answer - it is completely and utterly WRONG!
You CAN safely use IIF() to prevent a divide by zero.
That'll teach me to try before I post.
Now, where did I put that hair shirt?
Sorry for any confusion,
Andrew