SSRS IIF Returning #ERROR

Question

I have an expression in my SSRS report that calculates a field Total across multiple groups:

=IIf(SUM(Fields!sales_Dollars.Value) > 0 ,(SUM(Fields!Total_Defective__.Value)/IIF(SUM(Fields!sales_Dollars.Value) <= 0, 1, SUM(Fields!sales_Dollars.Value))),0)

I had to alter it by adding groups within the statement:

=SUM(IIf(SUM(Fields!sales_Dollars.Value,"Item_Process_Group") > 0 ,(SUM(Fields!Total_Defective__.Value,"Item_Process_Group")/IIF(SUM(Fields!sales_Dollars.Value,"Item_Process_Group") <= 0, 1, SUM(Fields!sales_Dollars.Value,"Item_Process_Group"))),0))

The problem is that there is a 0 in the denominator of one of the rows in the calc that is not in the group specified in the clause. This causes an #error. (at least I believe this is the cause) is there a way to slip in a check for zero in Fields!sales_Dollars.Value with no group? sort of like how it does it in the first code example. I need the group names in the bottom portion to pull the correct values.

enter image description here

Randall Randall · Accepted Answer · 2015-04-28T21:25:57

The first thing I like to do when I look at these is simplify them, in this case there is a redundancy to be removed. There is no way that

 SUM(Fields!sales_Dollars.Value) > 0

and

SUM(Fields!sales_Dollars.Value) <= 0

Could be true at the same time, which renders your second Iif statement always evaluating to false if the first one is true, if the first one is false the second Iif is skipped and a value of 0 is returned. So, we can set it to the false value drop the second Iif to end up with:

=IIf(
  SUM(Fields!sales_Dollars.Value) > 0 
    ,(SUM(Fields!Total_Defective__.Value)/SUM(Fields!sales_Dollars.Value))
    ,0)

Now the question is do you want to do something different if sales dollar value is zero. If that's the case we replace the zero with what the equation would have been if the second Iif statement were ever evaluated:

=IIf(
  SUM(Fields!sales_Dollars.Value) > 0 
    ,(SUM(Fields!Total_Defective__.Value)/SUM(Fields!sales_Dollars.Value))
    ,SUM(Fields!Total_Defective__.Value))

Hopefully that fixes the syntax problem you were experiencing, with the first statement.

SSRS IIF Returning #ERROR

1 Answers