COGNOS PowerHouse developer on retainer: QUIZ DEPTPAY report WITHIN DEPTNO produces three split subtotals for department 4 after SORT BY removed — records not physically sorted — CA PowerHouse 4GL developer on monthly retainer

CA PowerHouse 4GL 9.2 • AIX 7.1 • QUIZ DEPTPAY.QZM • WITHIN DEPTNO • SORT BY requirement • TRACE option • DFILE physical order • retainer billing

At Coastal Transit Authority, the monthly payroll reconciliation report had started producing nonsensical department totals. The payroll manager tried to verify department 4’s gross pay for September and found it listed three separate times in the QUIZ report: $68,200 near the top, $41,500 in the middle, and $22,800 near the bottom. The correct total was $132,500. Other departments had the same problem — split across two or three separate subtotal lines instead of appearing as a single consolidated figure. The CA PowerHouse retainer developer was called in.

The root cause was a one-line change made six weeks earlier: SORT BY DEPTNO had been removed from the QUIZ report to speed up the run. Without that sort, the WITHIN clause lost its guarantee that all department records would arrive consecutively. The result was mathematically fragmented subtotals — each group’s numbers correct on their own, but none representing an entire department.

The performance optimization that changed the output semantics

The DEPTPAY.QZM report had run unchanged for eleven years. In August, a developer reviewing report run times noted that DEPTPAY was taking four minutes on the monthly payroll run. The underlying PowerHouse DFILE EMPLOYEE had grown to 3,847 records as the transit authority expanded over two decades. The developer removed SORT BY DEPTNO on the grounds that the EMPLOYEE DFILE had originally been loaded in department order and was “basically sorted already.” The report run time dropped from 4m 12s to 3m 44s — a 28-second improvement.

The original QUIZ report structure (before the change) was:

ACCESS EMPLOYEE
SORT BY DEPTNO
WITHIN DEPTNO
   PRINT DEPTNO EMPNAME GROSSPAY
   TOTAL GROSSPAY
   COUNT
END

After the change:

ACCESS EMPLOYEE
* SORT BY DEPTNO   ← removed 2026-08-14 for performance
WITHIN DEPTNO
   PRINT DEPTNO EMPNAME GROSSPAY
   TOTAL GROSSPAY
   COUNT
END

The problem is fundamental to how PowerHouse QUIZ WITHIN works. WITHIN is a break-on-change clause, not a group-all-matching clause. It fires a subtotal whenever the value of its key field changes from one record to the next in the order that records are processed. Without SORT BY DEPTNO, records are processed in their physical order in the DFILE — the order in which they happen to reside on disk.

DFILE physical order and why it drifts from logical order

When the EMPLOYEE DFILE was first loaded in 2015, records were added in department order: all department 1 employees, then all department 2 employees, and so on. At that point, the physical order matched the logical order and WITHIN DEPTNO would have worked without SORT BY. But over eleven years of maintenance activity, the physical order drifted:

• New employees hired into department 4 had their records appended to the end of the DFILE, not inserted after the existing department 4 records.
• Deleted records left gaps that were reused when new employees were added. PowerHouse DFILE flat-file storage reuses the physical slot of a deleted record for the next new record, regardless of the new record’s DEPTNO value.
• Department reorganizations moved existing employees from one department to another by updating the DEPTNO field in-place — the physical record location stayed the same, but the department changed.

After eleven years, the EMPLOYEE DFILE’s 3,847 records were interleaved across all 32 departments with no correlation between physical position and DEPTNO value. Department 4 had 47 employees whose records happened to occupy three physically-separated clusters within the DFILE:

Physical DFILE record clusters for DEPTNO='4':
  Records 1–312 in DFILE:     18 dept-4 employees (original 2015 load)
  Records 847–1103 in DFILE:  21 dept-4 employees (2018–2021 hires, reused slots)
  Records 2247–2389 in DFILE:  8 dept-4 employees (2022–2024 hires, appended)

Department 3 records appear at positions 201–305 (cluster 1)
  and at positions 1044–1098 (cluster 2)
Department 7 records appear between dept-4 clusters 1 and 2

With SORT BY DEPTNO, PowerHouse sorted all 3,847 records before passing them to WITHIN DEPTNO. All 47 department-4 records were consecutive in the sort output. WITHIN DEPTNO fired exactly once for department 4 and produced one correct subtotal of $132,500.

Without SORT BY DEPTNO, WITHIN DEPTNO fired on every value change in the physical record sequence:

Physical processing sequence (partial):
  ...
  Record 305: DEPTNO='3' GROSSPAY=4,250  (last dept-3 record in cluster 1)
  Record 306: DEPTNO='4' GROSSPAY=3,800  ← WITHIN fires: dept-3 subtotal
  ...
  Record 312: DEPTNO='4' GROSSPAY=4,100  (last dept-4 record in cluster 1)
  Record 313: DEPTNO='7' GROSSPAY=5,200  ← WITHIN fires: dept-4 subtotal #1 = $68,200
  ...
  Record 846: DEPTNO='7' GROSSPAY=4,600  (last dept-7 record between clusters)
  Record 847: DEPTNO='4' GROSSPAY=3,950  ← WITHIN fires: dept-7 subtotal
  ...
  Record 1103: DEPTNO='4' GROSSPAY=3,700 (last dept-4 record in cluster 2)
  Record 1104: DEPTNO='9' GROSSPAY=4,800 ← WITHIN fires: dept-4 subtotal #2 = $41,500
  ...
  Record 2247: DEPTNO='4' GROSSPAY=4,200 ← WITHIN fires: dept-9 subtotal
  ...
  Record 2389: DEPTNO='4' GROSSPAY=3,600 (last dept-4 record in cluster 3)
  Record 2390: DEPTNO='11' GROSSPAY=5,100 ← WITHIN fires: dept-4 subtotal #3 = $22,800

Diagnosis with the QUIZ TRACE option

The retainer developer added the TRACE option to DEPTPAY.QZM to confirm the diagnosis:

ACCESS EMPLOYEE
* SORT BY DEPTNO
WITHIN DEPTNO
   PRINT DEPTNO EMPNAME GROSSPAY
   TOTAL GROSSPAY
   COUNT
END
TRACE

The TRACE output printed a line for each record processed and each WITHIN break event. The output confirmed that WITHIN DEPTNO was firing 89 times across the run — far more than the 32 expected department breaks. Many of the breaks showed a department changing away and then returning later: DEPTNO changed from ‘4’ to ‘7’ at record 312, then from ‘7’ to ‘4’ at record 847. Each return to a previously-seen DEPTNO value started a new group rather than contributing to the original group’s subtotal.

The developer also ran a separate QUIZ report with no WITHIN clause and SORT BY DEPTNO to produce a raw listing of all records in department order. This confirmed that department 4 had 47 employees totaling $132,500 — matching the sum of the three split subtotals from the broken report ($68,200 + $41,500 + $22,800 = $132,500). The data was correct; only the report grouping was wrong.

Fix: restore SORT BY DEPTNO

The fix was a single line:

ACCESS EMPLOYEE
SORT BY DEPTNO       ← restored
WITHIN DEPTNO
   PRINT DEPTNO EMPNAME GROSSPAY
   TOTAL GROSSPAY
   COUNT
END

With SORT BY DEPTNO restored, PowerHouse sorts all EMPLOYEE records by DEPTNO before passing them to WITHIN. All 47 department-4 records arrive consecutively. WITHIN DEPTNO fires exactly once for department 4. The subtotal shows $132,500. All 32 department totals now appear in a single consolidated line each, and every total reconciles with the payroll system’s independent figures.

The 28-second performance savings from removing SORT BY was real — sorting 3,847 records across 32 key values adds CPU time. But the correct approach for performance was not to remove the sort; it was to either (1) accept the 28-second cost as necessary for correct output, or (2) reorganize the DFILE periodically using PDML COPY to restore physical sort order, or (3) rewrite the report to use a direct database query if the backend had migrated to DB2 or Oracle where an indexed sort would be negligible. The transit authority accepted option 1.

Post-incident: the transit authority’s PowerHouse change management process was updated to require a test run comparing before and after totals for any QUIZ report change that touches SORT BY, WITHIN, or LINK statements — the three PowerHouse constructs most likely to produce silent semantic changes that are not caught by a syntax check.

WITHIN vs. GROUP BY: the mental model mismatch

The most important thing to understand about PowerHouse QUIZ WITHIN is that it does not behave like SQL GROUP BY. SQL GROUP BY DEPTNO collects all rows with a given DEPTNO value into a single group, regardless of their order in the table. PowerHouse WITHIN DEPTNO breaks when the current record’s DEPTNO value differs from the previous record’s DEPTNO value. This is the same behavior as COBOL’s control break pattern (AT END OF DEPTNO in COBOL 85 report writer) or SAS’s FIRST.variable / LAST.variable in a DATA step.

All three approaches — QUIZ WITHIN, COBOL control break, and SAS FIRST./LAST. — require the data to be pre-sorted by the break key. The difference is that SQL GROUP BY handles unsorted data internally, which leads developers from SQL backgrounds to incorrectly assume WITHIN will do the same. It will not. SORT BY is not an optimization for WITHIN — it is a correctness requirement.

How HourTab helps COGNOS PowerHouse retainer developers

The diagnosis took 1.5 hours: 30 minutes identifying the SORT BY removal from the change log, 30 minutes running QUIZ TRACE to confirm the WITHIN break sequence, 30 minutes running the corrected report and reconciling all 32 department totals with the payroll manager. The fix itself was one line and two minutes. But the retainer developer also spent 45 minutes walking the transit authority’s development team through the difference between WITHIN (break-on-adjacent-change) and GROUP BY (group-all-matching) — a distinction that matters for every QUIZ report any of them would ever write. That explanation is billable retainer work, and it deserves a proper time entry.

In a HourTab log, the session appears as: “DEPTPAY.QZM WITHIN DEPTNO split subtotals: traced to SORT BY removal; QUIZ TRACE confirmed 89 WITHIN breaks vs. 32 expected; restored SORT BY DEPTNO; all 32 dept totals reconciled; team walkthrough of WITHIN vs GROUP BY semantics, 2.25h.” The transit authority’s finance director can read that entry and understand exactly what happened and why the developer’s time was justified — no follow-up emails required.

HourTab turns a time-tracker CSV into a public retainer-hours URL your client can bookmark — no login, no portal, no “how many hours do I have left?” emails. Free for one retainer. $9/mo for up to ten.

FAQ: COGNOS PowerHouse developer retainers

See also: FOCUS developer retainer · Easytrieve developer retainer · SAS developer retainer