[Solved] Averages on Report

[Nocton]

I have a report where each record shows 4 handicaps as numbers - HomeHcap1, HomeHcap2, HomeHcap3, HomeHcap4 - for the home team and similarly for the away team. I wish to create a field on the report showing the average handicap of the team. A function as:

Code: Select all

AVERAGE([HomeHcap1];[HomeHcap2];[HomeHcap3];[HomeHcap4])

does the job nicely. However, I also wish to get the average for the situation when one of the handicap fields is empty. This function returns zero and the empty field on the report shows 'NaN'. Is there a simple way to get round this problem?

Regards, Nocton

[chrisb]

Hello Nocton,

Originally I did not respond to this post because you did not say that you used Report Builder.
Also the average function has never worked.
Your comments however have prompted me to look again and I now see that the function 'AVERAGE' is available and working.
The problem though as you have discovered is that it does not handle NULL values.

I believe the code below will perform the function of averaging whilst also dealing with null values.
I have populated it with your field names.

1. Copy the code.
2. Open report for edit.
3. Drag out a text box in the detail pane.
4. If necessary hit F4 to show the properties pane.
5. Hit 'Data' tab.
6. Data Field Type = Field or Formula.
7. Click in the text box to right of Data Field
8. Press (Ctrl+V) to paste the code.
9. Hit 'Enter'
10. Execute report.

Code: Select all

(IF(ISNUMBER([HomeHcap1]);[HomeHcap1];0)+IF(ISNUMBER([HomeHcap2]);[HomeHcap2];0)+IF(ISNUMBER([HomeHcap3]);[HomeHcap3];0)+IF(ISNUMBER([HomeHcap4]);[HomeHcap4];0))/(ISNUMBER([HomeHcap1])+ISNUMBER([HomeHcap2])+ISNUMBER([HomeHcap3])+ISNUMBER([HomeHcap4]))

[Nocton]

Many thanks, chrisb. These user defined functions are a bit cumbersome, but very effective. Your solution works fine.