rschaeferhig

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?

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

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

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?

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))

=SUM(Fields!B.Value) / IIF(SUM(Fields!A.Value) <= 0, SUM(Fields!B.Value), SUM(Fields!A.Value))

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.