Link to home
Start Free TrialLog in
Avatar of rschaeferhig
rschaeferhigFlag for United States of America

asked on

SSRS IIF statement not executing correctly

I have a Group Total row in which I need to calculate a column as a ratio of two of the other Total columns. I used "=IIF(sum(Fields!A.Value)<=0,0,sum(FIelds!B.Value)/sum(Fields!A.Value)". This general formula seems to work for some column pairs but for others it does the division even if Fields!A=0. I get a Divide by Zero error. If I change the formula to "=IIF(sum(Fields!A.Value)<=0,0,1) I get a 0 in the field.

THis is really wierd. Anyone ever see this? Is there a better way to do it?
Avatar of pollock_d
pollock_d

The IIF evaluates all the arguments since it is just a regular method call. That is why you get the Divide by Zero error
Avatar of rschaeferhig

ASKER

>>The IIF evaluates all the arguments since it is just a regular method call. That is why you get the Divide by Zero error

So how do I prevent a column total formula from throwing a divide by zero error if the denominator is zero? Do I have to write code? Is there a better formula to use?
I can't test this right now but you can give this a shot
=SUM(Fields!B.Value) / IIF(SUM(Fields!A.Value) <= 0, SUM(Fields!B.Value), SUM(Fields!A.Value))
ASKER CERTIFIED SOLUTION
Avatar of jgv
jgv

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial