Generating Audit Reports

What Is an Audit Report?

An audit report is a human-readable summary of data or of changes to data. It might list every transaction processed, every record that was added or deleted between two files, or totals and counts by category. The report usually has a title, column headers, detail lines, and one or more footer lines with totals or counts. DFSORT does not have a dedicated "audit report" verb; you build the report by sorting the data and then using OUTFIL with report options: HEADER1, HEADER2, TRAILER1, TRAILER2, and optionally SECTIONS and TRAILER3 for grouped output.

Building the Report with OUTFIL

Use one OUTFIL that writes to the report DD (FNAMES=). In that OUTFIL specify: BUILD= or FIELDS= (or the equivalent) to define each detail line from the input record; HEADER1= for one or more lines at the start of the report (e.g. report title and run date); HEADER2= for lines that repeat at the top of each page (e.g. column headers); TRAILER1= for lines at the end of the report (e.g. "Total records: n"); and optionally TRAILER2= for a page footer (e.g. page number). Use LINES=n to set the number of lines per page so HEADER2 and TRAILER2 repeat correctly. REMOVECC is often used so the output file does not contain printer control characters if the report will be viewed online.

Record Count in the Trailer

Audit reports often end with a line like "Total records: 12345". In OUTFIL TRAILER1 you can include a count of how many detail records were written. The exact keyword is product-dependent: DFSORT and ICETOOL may use COUNT= with a format and length, e.g. COUNT=(M3,LENGTH=10) to output the count in a 10-byte field. You combine that with a label: 1:\'Total records: \',COUNT=(...). Check your product manual for the precise syntax. If COUNT= is not available, an alternative is to run a separate step that counts records and writes a one-line file, then concatenate or use ICETOOL to merge that line as the last line of the report.

Sort Order and SECTIONS

The order of the report is the sort order. So you SORT FIELDS=(...) first (e.g. by user ID, then by date). If you want the report grouped by a key (e.g. one section per user), use SECTIONS=(position,length,format) on the OUTFIL. For each distinct value of that key you get a section. You can define HEADER3= to print a line at the start of each section (e.g. the user ID) and TRAILER3= to print a line at the end of each section (e.g. "Records for user: n" or a subtotal). That gives you a control-break style audit: sections with optional section-level counts or totals.

Audit of Changes (Before vs After)

When the audit is "what changed between two files," you first produce a stream of changes using file comparison (e.g. JOINKEYS with UNPAIRED,ONLY to get adds and deletes, or a join that outputs matched pairs with both versions for change detection). That stream becomes the input to the report step. Sort that input if needed (e.g. by key or by change type), then use OUTFIL to format it as a report: HEADER1 "Change Audit Report", HEADER2 with columns like Key, Change Type, Old Value, New Value, detail lines from the compare output, and TRAILER1 with total count of changes. So the audit content (adds, deletes, changes) comes from the comparison step; the report layout (title, headers, footers) comes from OUTFIL.

Example: simple audit report layout

1
2
3
4
5
6
7
8
  SORT FIELDS=(1,20,CH,A)
  OUTFIL FNAMES=AUDITRPT,
    HEADER1=(1:'AUDIT REPORT',40:DATE=(MD4/),60:TIME/),
    HEADER2=(1:'KEY',22:'AMOUNT',35:'DATE'/),
    BUILD=(1,20,25,10,PD,EDIT=(IIIIIIIIII.TT),38,8),
    TRAILER1=(1:'Total records: ',COUNT=(M3,LENGTH=10)),
    LINES=60,
    REMOVECC

This sorts by the key at 1,20. HEADER1 prints a title and run date/time. HEADER2 prints column headers. BUILD defines the detail line (key, amount with edit mask, date). TRAILER1 prints a label and the record count. LINES=60 sets 60 lines per page. REMOVECC strips carriage control. The exact COUNT= and DATE= formats may vary by product; see your manual.

Multiple Outputs: Report and Summary File

You can write both a formatted report and a summary dataset from the same step. Use two OUTFILs: one with FNAMES=REPORT and the HEADER/TRAILER/BUILD report layout, and another with FNAMES=SUMMARY and a different BUILD= or INCLUDE= (e.g. one summary record per section using SECTIONS, or a single record with totals). Both read the same sorted data; one produces the human-readable audit report, the other produces a machine-readable summary for downstream use or for restart.

Best Practices

• Sort by the order you want the report (and by the SECTIONS key if you use control breaks).
• Use REMOVECC if the report will be viewed online or in an editor so printer control characters do not appear.
• Document the report layout (column positions, meanings) so auditors and operators can interpret it.
• For change audits, document how you produced the change stream (JOINKEYS options, which file is F1 vs F2) so the report is reproducible.
• Validate the trailer count against expectations (e.g. compare to a separate count step) to catch logic errors.

Explain It Like I'm Five

An audit report is like a list your teacher makes: at the top it says "Class list" and the date, then each row is one kid's name and maybe their score, and at the bottom it says "Total: 25 students." DFSORT makes that list for data: you tell it what to put in the title (HEADER1), what to put at the top of each page (HEADER2), what one line per record looks like (BUILD), and what to put at the end (TRAILER1 with the count). If you want a section for each class, you use SECTIONS so each class gets its own little block with a subtitle and a small total.

Exercises

1. List the OUTFIL keywords you would use for: report title, column headers, one detail line per record, and "Total: n" at the end.
2. You have sorted data by user ID (1,8). You want a section header with the user ID and a section trailer with the count of records for that user. What SECTIONS and HEADER3/TRAILER3 might you use?
3. You already have a file of "change" records (adds and deletes from a JOINKEYS step). How do you turn that into an audit report with a title and a total count?
4. Why is sort order important before building an audit report?