I am getting an error when computing averages in summary line of a report. My expression looks like this:
=IIF(SUM(Fields!column3.Value) > 0, Format(DateAdd("s", (SUM(Fields!column4.Value) / SUM(Fields!column3.Value)), "00:00:00"), "HH:mm:ss"), "-")
(sorry for the super long line)
The expression works fine when the data looks something like this:
8311 87 0 0
8311 41 0 0
8311 80 0 0
8311 136 1 136
The idea is that column 3 is a 1 if column 2 is over 120, otherwise it is 0. Column 4 has value greater than 0 only if column 2 is greater that 120, in which case it is the same value as column 2. In this way, for the summary line of this group in reporting services, I am able determine the total number of rows that are greater than 120 (by using SUM on column 3). In the same way, I can determine the total number of the values in column 2 that are greater than 120 by summing column 4. From there, it should be a simple matter of dividing column 4 by column 3 to determine the average of column 2 values that are over 120.
Once I get the average, I want to convert it to a time string (e.g., "00:02:16"). If there is no value (that is, no column 2 values over 120 in the group, I want to display a "-".
This logic appears to work in cases with column 2 has values over 120. However, when column 3 and 4 are 0, like the below:
8310 108 0 0
the expression gives the dreaded #Error result when running the report. I am assuming that this is a divide by zero error, so I threw the IIF in the expression to check that the denominator in my average calculation is > 0.
Sadly, #Error persists for those groups that have 0's in columns 3 and 4. What am I missing?
