Field Calculations

Field Calculations can be set on the Field Calculations window.  You may also reset the calculation on any group band created in the report, at the end of each page, or at the end of the report.  For example, if you have a field which is summing the total amount of optional fees per individual, you would reset every time the ID field changes (sums optional fees for individual, then sets it to zero and resumes for next individual).

To set a field calculation:

1. Double click the field to open the Report Expression window.
2. Click the Calculate button to open the Calculation window.

3. Select the appropriate field calculation options:

• Nothing - No calculations are made on this variable.
• Count - Counts the number of times a variable is printed per group, page, column, or report (depending on your selection in the Reset box). The calculation is based on the number of times the variable occurs, not on the variable's value.
• Sum - Sums the variable values for a group, page, column, or report (depending on your selection in the Reset box).
• Average - Computes the arithmetic mean (average) of the variable values within a group, page, column, or report (depending on your selection in the Reset box).
• Lowest - Displays the lowest value that occurred in that variable for a group, page, column, or report (depending on your selection in the Reset box). Places in the variable the value of the first record in the group. When a lower value is encountered, the variable's value changes accordingly.
• Highest - Displays the highest value that occurred in that variable for a group, page, column, or report (depending on your selection in the Reset box). Places in the variable the value of the first record in the group. When a higher value is encountered, the variable's value changes accordingly.
• Std. Deviation - Returns the square root of the variance for the variable values within a group, page, column, or report (depending on your selection in the Reset box).
• Variance - Measures the degree to which individual field values vary from the average of all values in the group, page, column, or report (depending on your Reset selection).

4. Select the Reset option (point at which the variable is reset to its initial value, e.g. when the course number changes, at the end of the report, etc.).
5. Click the OK button to close the Calculations window.
6. Click the OK button on the Report Expression window to close the window and return to the report template.

Notes

• The most common settings are the ‘Count’ and ‘Sum’ options. The other choices are unreliable at best.  Even the count and sum can be tricky if improperly used.
• Calculations work best on reports where there is only one line of information in the report cursor per group (e.g. CEU Reporting, Mailing Labels, Deadbeat Reporting).
• Calculations can also be performed with report variables.

Report Variables

Variables are used to compare and manipulate data for display in a report.  Variables are not saved to the database--their values are stored in the computer's memory while the report is running.  After you exit the report, the variables are erased from memory.

You can use variables to:

• Generate record counts in a report (e.g. how many individuals took courses during a given semester).
• Generate numbered lists (e.g. 1. First person taking course, 2. Second person, etc.).
• Calculate values based on report data (e.g. add the payment amount to the total paid IF the pay type is Cash).
• Create shorthand representations of long expressions (e.g. variable Due represents the function gtdue(rgcrse,rgid) and variable Paid represents gtpaid(rgcrse,rgid)).
• Calculate values then use those values to calculate subsequent values (e.g. Due-Paid).

To create a variable:

1. Select Report > Variables to open the Variables window.  On this window, you can add new variables, change or delete existing variables or change the order in which the variables are evaluated in a report.

The Variable Definition dialog offers these options:

2. Variables: Name of the variable.
3. Value to store: Field/Expression which should be stored/calculated in this variable.
4. Initial value: Initial value of the variable BEFORE any calculations are performed
5. Release after report: Clears the report variable from memory after the report is printed.  If not checked, the variable remains in memory until you exit Student Manager.
6. Reset at: Specifies the point at which the variable is reset to its initial value.  End of Report is the default.  Other options include End of Page, End of Column and groups you have created in the report.
7. Calculate:  Specify a calculation that the variable performs. The variable begins calculating with its initial value, and continues until it is reset.

Notes

• Report variables are evaluated in the order that they appear in the list and can affect the value of expressions that use them. For example, if variable 1 is used to define the value of variable 2, variable 1 must appear before variable 2.
• If you use a variable in calculations, be sure that you initialize the variable with a non-zero value to avoid a division-by-zero error. If you don't specify a value, Visual FoxPro assigns a default value of 0.
• If you reorder the groups in your report, your report variables might not be resetting on the correct field. For example, if your report has two groups, the first grouping by country and the second grouping by date, and you switch the order of the groups, the variables still reset according to the original positions of the groups.