How to Sum the Averages of Report Groups

ID: Q125741

The information in this article applies to:

SUMMARY

For a sum or any other calculation to be performed on a calculated field that is located in a report group footer, you must use user-defined functions (UDFs). This article shows by example how to set up a report to have a calculated field in a page footer that performs a calculation on another calculated field that is also in the group footer.

For example, say you wanted to calculate the average of a field of your table based on a group. To produce the average, you would add a calculated field or a calculated report variable to the group footer. If you then wanted to put a sum of all the group averages into a page footer or Summary Band of your report, you must use Report UDFs to do the calculations.

MORE INFORMATION

The report created below uses the CUSTOMER table that shipped with FoxPro. The CUSTOMER table should be located in the \tutorial directory.

1. Group the data. Create a Data Grouping on the STATE field.

2. Add a field in the group footer that calls a UDF. Use the ab tool to add 

   a field to the group footer of the group created in step 1. In the
   "Report Expression" dialog box, type SumAve() in the Expression field.
   The SumAve() line tells the Report Writer to execute the function
   named SumAve, and display the value returned from the function for
   each group.
3. Add a calculated report variable to the variable list. From the Report 
   menu, choose the Variables... bar. Select the Add... button in the
   Report Variables dialog box. Add the following to the appropriate fields
   of the "Variable Definition" dialog box:

   Variable Name : Ave
   Value to Store: <fieldname> where fieldname is the name of the field
                   you want to average. This example uses YTDPURCH.
   Initial Value:  0
   Reset        :  <datagrouping>  Where datagrouping is the name of the
                   data group created in step 1.

   Choose the Average radio button under the Calculate section, and click
   the OK button.
4. Add a dummy variable to the variable list. The variable created in this 
   step will be used in the UDF to store the results of the summary
   calculation. First, create a report variable following the procedure
   outlined in the previous step. But this time add the following to the
   appropriate fields of the "Variable Definition" dialog box:

   Variable Name : Avetotal
   Value to Store: Avetotal
   Initial Value : 0
   Reset         : End of Report

   Choose the Nothing radio button under the Calculate section, and click
   the OK button.
5. Add a field in the page footer, and type the name of the variable 
   created in the previous step into the report expression. Add a field in
   the page footer, and type Avetotal into the Expression... field. The
   value of Avetotal will now print at the bottom of each page.
6. Create a new program that has the same name as that given in step 2. 
   Choose New|Program from the File Menu. Save the file as SUMAVE.PRG.
7. Add the code that performs the summary calculation to the program. Here 
   is where the code that performs the sum or any other calculation is
   written. This example adds (sums) all of the group averages, so you want
   to add to the variable Avetotal the value of the variable Ave and return
   the value Ave to the report.

   Avetotal=Avetotal+Ave
   Return Ave
8. Save and run the report.

Additional reference words: FoxWin 2.60a KBCategory: kbtool kbcode KBSubcategory: FxtoolRwriter

Last Reviewed: June 27, 1995