Defining Report Variables

  • Article

To manipulate data- and display-calculated results in a report, you can use report variables. Using report variables, you can calculate values and then use those values to calculate subsequent values.

To define a report variable

  1. Open or create a report.

  2. From the Report menu, choose Variables.

  3. In the Report Variables dialog box, select the Variables box and type a name for the variable.

  4. In the Value to store box, type a field name or any other expression.

  5. If appropriate, select a calculation option.

  6. If appropriate, in the Initial value box, type an expression that sets the initial value.

  7. Choose OK.

    You can use the variable in any expression you enter in the report.

To count all the Canadian entries in the Company table, use this expression and select Count as the calculation option.

IIF(country="Canada",1,0)

The following example shows three variables for a simple time sheet:

To store this value Create this variable Using this expression
Time employee arrived 
tArrive
 
hour_in + (min_in / 60)
Time employee left 
tLeave
 
hour_out + (min_out / 60)
Total time employee was present 
tDayTotal
 
tLeave - tArrive

You can use the tDayTotal variable in a variety of other calculations, such as the number of hours worked in a week, a month, or a year; the average number of hours worked each day; and so on.

For examples of report variables, see the reports Percent.frx and Invoice.frx in the Visual FoxPro ...\Samples\Solution\Reports directory.

Reordering Report Variables

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. In the previous time sheet example, tArrive and tLeave must precede tDayTotal.

To change the order of report variables

  1. From the Report menu, choose Variables.
  2. In the Variable box, drag the button to the left of the variable to rearrange the order.
  3. Choose OK.

Setting a Variable's Initial Value

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.

To set a variable's initial value

  1. From the Report menu, choose Variables.
  2. In the Variable box, select the variable you want to set.
  3. In the Initial value box, enter the value.
  4. Choose OK.

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.

You can change the value of a calculation by specifying when the variable is reset. By default, Visual FoxPro resets the report variables at the end of the report.

To reset a variable at the end of a report, a page, or a column

  1. From the Report menu, choose Variables.
  2. In the Reset at box, choose an option.
  3. Choose OK.

To reset a variable on entry or exit of any band

  1. In the Report Designer, open the report.
  2. Double-click the bar for the report band.
  3. In the Run expression area of the band's dialog box, choose the dialog button at the end of the On entry or On exit box.
  4. Enter an expression to reset the variable each time the band is entered or exited.

See Also

Using Expressions and Functions in Field Controls | Formatting Field Controls | Creating Reports and Labels | SELECT - SQL | Selecting, Moving, and Resizing Report Controls