Generating A Running Total

If you need to include a running total in a report, you can easily do that using the COMPUTE command. The COMPUTE command creates new fields based on the table results.  It uses syntax similar to the DEFINE command, but is issued after the verb phrase in a report request. Typically COMPUTE is used to calculate numeric fields using an arithmetic or logical expression. For more information, refer to the Advanced CIRS Reporting materials or to your FOCUS documentation.

Sample Report:

This report shows the total hours paid to hourly rate employees by pay period for payments issued in the current fiscal year.  The total hours paid for the fiscal year is also displayed per a computed field named RUN_TOT.  

 

ph:ssa       ph:wname     ph:payperiod     hours      run_TOT

------       --------     ------------     -----      -------

000-00-0000  bear, pj          2004/06     54.70        54.70

                               2004/07     47.00       101.70

                               2004/08     57.30       159.00

 

The commands used to compute the running total are shown below in bold.  Note that only fields retrieved per the verb and sort phrase are used in the COMPUTE command.   

 

EX ph

TABLE FILE ph

sum ph:hourspaid

by ph:ssa

by ph:wname

BY ph:payperiod

if ph:rollcode eq 3

COMPUTE RUN_TOT/P10.2 = IF PH:SSA EQ LAST PH:SSA

                        THEN ph:hourspaid + last RUN_TOT

                        ELSE PH:hourspaid;

end

Usage Notes:

  • The name of the computed field (e.g., RUN_TOT) can be up to 66 characters long and can include any combination of letters, digits, and underscores.

  • The default format of the computed field is D12.2.  You can optionally supply a numeric format with or without edit options. In the example above, P10.2 was used instead of the default.

  • Computed fields are calculated on the report request results after all records have been selected, sorted, and summed. Each field used in the expression must be part of the resulting table.

  • Computed fields can be used in subsequent screening statements, but cannot be used as sort fields.

  • Computed fields are only available for the specified report request.  They do not remain active for the duration of the FOCUS session.

  • WHERE and IF phrases can be placed before or after the COMPUTE command.

 

Related Topic: Differences Between Define and Compute