IIF in SSRS returning an error

1
votes

I have an SSRS report that is throwing and #ERROR# in the field.

I am comparing Down Time to Total time...DownTime/TotalTime, to get a percentage of downtime over all time.

The original code was:

=Sum(Fields!PrevYear_Total_Down_Time.Value)
/
Sum(Fields!PrevYear_Total_Time.Value)

This threw an #ERROR# which I assumed was due to "Sum(Fields!PrevYear_Total_Time.Value)" being zero or null, so I adjusted the code to an IIF statement:

=IIF(Sum (Fields!PrevYear_Total_Time.Value)=0,0,
     IIF(Sum(Fields!PrevYear_Total_Down_Time.Value)=0,0,
         Sum(Fields!PrevYear_Total_Down_Time.Value)/Sum(Fields!PrevYear_Total_Time.Value)
         )
     )

If I replace the second nested IIF statement with a number, it works and puts whatever number I designate, But, that is NOT what I wanted. I want to test for zero, or null, and if the Total Downtime is null or 0, return 0.

2
reporting-services
Possible duplicate of How to handle IIF or Switch divide by zero giving #ERROR? – Chris Latta

2 Answers

0
votes

Solved it by writing a code snippet that passes the two values then returns the formatted value.

0
votes

The other way is to put another IIF in the denominator to account for the zero:

=IIF(Sum(Fields!PrevYear_Total_Time.Value) = 0, 0, Sum(Fields!PrevYear_Total_Down_Time.Value))
 /
 IIF(Sum(Fields!PrevYear_Total_Time.Value) = 0, 1, Sum(Fields!PrevYear_Total_Time.Value))