sql server reporting services: Hi,

Do you guys see any problem with the following IIF statement, as it is
giving #Error on the report rather than 0

=IIF( Fields!CSales.Value = 1, IIF( Fields!PrYrM1.Value>0, (Fields!M1.Value-
Fields!PrYrM1.Value)/ Fields!PrYrM1.Value ,0) ,0)

Thanks

I believe that all parts of an IIF are evaluated whether or not they
are invoked, so you may be getting a 'divide by zero' error at
execution time.

So how do you avoid a divide by zero?

[quoted text, click to view]

To avoid the issue where both the true and false parts of the IFF get
evaluated and return an error when dividing by zero you can do this:

if you are trying to find a/b:

=Iif(Fields!b.value != 0, a/iif(fields!b.value = 0,1,b), 0)

What happens is that when the true (b not 0) will get evaluated when b
= 0 it will never result in the error. I recommend you write a custom
assembly or write a function in the Code section of the report instead
of writing this everytime you have to divide.

Abe

[quoted text, click to view]