Creating Running Totals

What Is a Running Total?

A running total is a cumulative sum: for each record, the value in the "running total" column is the sum of a designated field for all records from the start of the file (or from the start of the current group) up to and including that record. So the first record's running total equals its own value; the second record's running total equals the first plus the second; and so on. That requires carrying state—an accumulator—from one record to the next. DFSORT control statements (SORT, SUM, INREC, OUTREC, OUTFIL) do not have a variable that represents "sum so far." They can only reference the current record (and in some cases group context like WHEN=GROUP). So a true per-record running total cannot be done with control statements alone; you need either a user exit that maintains the accumulator or a separate program.

Group Subtotals: What DFSORT Can Do

What DFSORT can do without an exit or program is a subtotal at the end of each group. Sort by the key that defines the group (e.g. department, account). Then use OUTFIL SECTIONS=(position,length,format) with that key and TRAILER1=, TRAILER2=, or TRAILER3= that includes TOTAL=(input_position,length,format,edit_mask). At the end of each section (group), DFSORT writes a trailer line that contains the sum of the specified field for that group. So you get one line per group with the group total—not a running total in every record, but a subtotal per group. That is often enough for reports that need "Department 1000 total: 5000" at the end of each department. You can also include COUNT=, MIN=, MAX=, AVG= in the same trailer. Use NODETAIL if you want only those trailer lines and no detail records.

Example: Group Subtotal at End of Each Department

Input: records with department in 1–4 and amount in 20–27 (PD). Sorted by department. Output: same records, plus at the end of each department a trailer line "DEPT xxxx TOTAL: nnnnnn."

1
2
3
4
  SORT FIELDS=(1,4,CH,A)
  OUTFIL FNAMES=OUT,
    SECTIONS=(1,4,CH,
      TRAILER3=(1,6,C'DEPT ',7,4,1,1,X,12,20,TOTAL=(20,8,PD,M11)))

Each section is defined by 1–4 (department). TRAILER3 writes one line per department: "DEPT ", the department value, a blank, and the total of the amount field (20,8,PD) with edit mask M11. So you get a group subtotal at the end of each department. This is not a running total (cumulative from record 1), but a subtotal per group.

Per-Record Running Total: User Exit

To put a running total in every record, you need to maintain an accumulator across records. A DFSORT user exit (E15 for input processing or E35 for output processing) is called once per record. The exit can keep a static or passed save area that holds the running sum. For each record: read the amount (or whatever field) from the record, add it to the accumulator, format the accumulator (e.g. with edit mask), and overlay it into a fixed position in the record. Then return the record to DFSORT. The next time the exit is called, the accumulator still has the previous sum, so you get a cumulative total. The exit must handle the data format (e.g. packed decimal) and overflow if the running total exceeds the output field size. E15 runs before sort; E35 runs after sort. For a running total that depends on sort order, use E35 so the total is computed in sorted order. Exit programming is beyond the scope of this page; see DFSORT application programming and exit documentation.

Per-Record Running Total: COBOL Program

An alternative is a COBOL program that reads the sorted file (produced by a previous SORT step or inline SORT) and writes an output file. For each input record, the program adds the amount to a working-storage accumulator, then writes the record with the accumulator value in the running-total position. So the output file has the same layout as the input plus a running total in a designated column. The program can also reset the accumulator at control breaks (e.g. when department changes) so you get a "running total within each department." Many shops prefer this to an exit because the logic is visible in one place and easier to test and maintain.

Running Total That Resets at Control Break

Sometimes you want a running total that resets at each new group (e.g. running total within each department, so the first record of each department has running total equal to its own amount). An exit can do this by comparing the current record's key to the previous; when the key changes, reset the accumulator to zero (or to the current record's value). A COBOL program does the same: keep the previous key in working storage; when the key changes, reset the running total before adding the current amount. Either way, you need state (accumulator and optionally previous key) and logic that runs per record—hence exit or program.

Strategy Summary

Explain It Like I'm Five

Imagine you're adding your pocket money each day: Day 1 you have 5, so "total so far" is 5. Day 2 you get 3, so "total so far" is 8. Day 3 you get 2, so "total so far" is 10. That's a running total—each day you need to remember the previous total and add today's amount. DFSORT doesn't have a "memory" for that, so you need a helper: either a tiny program that remembers the total and adds each new amount, or a special plug-in (exit) that does the same. What DFSORT can do by itself is say at the end of each week: "This week's total is 10"—that's a subtotal per group, not a running total every day.

Exercises

1. Input has amount in 20–27 (PD). You want a trailer line at the end of each department (key in 1–4) with "TOTAL: " and the department total. Write the OUTFIL SECTIONS= and TRAILER3= (without the full BUILD, just the idea).
2. Why can't you get a per-record running total (column = sum of all amounts so far) with SUM or OUTFIL BUILD only?
3. You need a running total that resets when region (1–3) changes. Would you use an exit or a program, and what would you reset?