[Solved] Averages on Report

Getting your data onto paper - or the web - Discussing the reports features of Base

[Solved] Averages on Report

Postby Nocton » Tue Nov 22, 2011 9:18 pm

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   Expand viewCollapse view
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
Last edited by Nocton on Fri Nov 25, 2011 12:26 pm, edited 1 time in total.
OpenOffice 4.2.0 on Windows 10
Nocton
Volunteer
 
Posts: 494
Joined: Fri Nov 05, 2010 10:27 am
Location: UK

Re: Averages on Report

Postby chrisb » Thu Nov 24, 2011 3:05 am

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   Expand viewCollapse view
(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]))
open office 4.1.6 & LibreOffice 6.0.7.3 (x64) using HSQL 1.8.10 (Embedded) and HSQL 2.4.1 (Split) on Windows 10
chrisb
 
Posts: 177
Joined: Mon Jun 07, 2010 4:16 pm

Re: Averages on Report

Postby Nocton » Fri Nov 25, 2011 12:25 pm

Many thanks, chrisb. These user defined functions are a bit cumbersome, but very effective. Your solution works fine.
OpenOffice 4.2.0 on Windows 10
Nocton
Volunteer
 
Posts: 494
Joined: Fri Nov 05, 2010 10:27 am
Location: UK


Return to Reporting

Who is online

Users browsing this forum: No registered users and 1 guest