How to Sum the Averages of Report Groups

• Microsoft FoxPro for Windows, version 2.6a

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.