Tracing Reporting Bugs
I traced incorrect report values through a large reporting system that was still being built, then turned the checks I kept repeating into reusable SQL templates.
A system under development
In 2023, I joined a banking-related project that was building a new reporting system in IBM Cognos. Of roughly 50 reports, some were finished, some had been implemented but still had issues, and others had not been built yet.
The data behind them passed through several warehouse layers before reaching Cognos. IBM DataStage handled much of the processing, and around 300 tables were relevant to the reports. By the time a wrong value appeared on screen, its cause could be several steps upstream.
Tracing the data
Most investigations started with a ticket about a difference between an expected value and the value shown in a report. I would first reproduce the issue in Cognos and identify the query behind the affected part of the report.
Cognos generated large SQL queries from its reporting model. I extracted the relevant query and ran it directly against the database. From there, I traced the data backwards through the Cognos model and the warehouse layers until I found the first step where the value changed or disappeared. The cause could be in the report, the Cognos model, or an earlier transformation in DataStage.
Finding that point did not always mean I could fix it myself. I resolved issues where I could. For the rest, I documented the cause and passed it to the team responsible for that part of the system.
Reusing the checks
After a while, the same kinds of comparisons between reports, tables, and processing stages kept coming up. Writing those checks again for every investigation meant repeating a fair amount of SQL.
I collected those checks in a set of SQL templates instead. They gave me a consistent starting point for later tickets and made it quicker to narrow down which part of the processing chain needed a closer look.
Result
Over six months, I traced reporting errors across Cognos and the upstream data pipeline. The SQL templates kept being useful as reports changed and new issues appeared.