Same total. Wrong records.

A new report is being checked before it replaces an existing one. Both show 2 open service requests. The new report leaves out one request that should count and includes one that should not.

This example uses sample data. The checks draw on practical experience investigating reporting and data issues.

Start with the requirement

The requirement is to count requests currently marked Open. Both reports read the same four service requests from the same saved copy of the data (a source snapshot), with one row per request.

The previous status is an earlier recorded value. It can match the current status, but it does not establish what the status is now. For REQ-1045, NULL means no current status is recorded.

Compare the requests behind the total

Both reports return 2, using different fields:

• Existing report, current status: REQ-1042 and REQ-1043.
• New report, previous status: REQ-1042 and REQ-1045.

Both reports include REQ-1042. The new report counts REQ-1045 instead of REQ-1043.

REQ-1043 is currently Open, so it belongs in the count. The new report leaves it out because its previous status was Closed. REQ-1045 gets counted because its earlier status was Open, even though its current status is missing.

One wrong field causes both differences. The omitted request and the incorrectly included request cancel each other out in the total: 2 − 1 + 1 = 2. All four records remain in the source; the new report selects the wrong records.

Changing the new report’s filter to current_status = 'Open' returns the required records. The total stays at 2. Checking the number alone would miss both the mistake and the correction.

SELECT COUNT(*) AS open_requests
FROM sample_requests
WHERE current_status = 'Open';

SELECT COUNT(*) AS open_requests
FROM sample_requests
WHERE previous_status = 'Open';

The missing status still needs attention

Correcting the report does not fill in REQ-1045’s current status. The earlier Open value is not enough to establish whether the work is still open. A source-data owner needs to check the request’s history or another authoritative record.

The source contains 2 Open requests, 1 Closed request, and 1 with no current status. A report owner could choose to display “Status missing: 1” beside the Open count while the source is investigated. That would expose the gap without assuming the request is Open or Closed. This reporting decision is separate from fixing the field mapping.

If that decision is pending, document the question and who will resolve it. “TBD” belongs in the decision notes unless the business has actually defined it as a request status.

Before approving a new report

First, confirm what the number is supposed to mean. An old report is a useful baseline, but it may contain an error or reflect a definition that has changed. Ask the report owner what decision the number supports. “Requests marked Open” and “work that still needs attention” may need different rules.

1. Match the conditions. Use the same source snapshot, reporting scope, and parameters. Include relevant permissions, refresh times, date cutoffs, and time zones.
2. Compare totals, IDs, and how often each ID appears. If a request has two activity records, a join can count it twice. A list of unique IDs would hide that extra row. Establish what one count represents before using DISTINCT.
3. Resolve differences against the required rule. Document which records should change and why. A matching old total does not settle the question, especially when the requirement has changed.

Four rows test one mapping error. A real report also needs cases for no matching requests, empty input, missing or unexpected status values, and repeated rows. Where dates matter, check records just before, at, and after the cutoff. Case and spaces can affect matching too; verify the behavior of the database you are using.

Keep the query or report definition with the comparison, including its version, parameters, and source snapshot. Follow any views, joins, or report-level filters that affect the result. Someone else should be able to reproduce the check. JodyInk’s work on making report logic inspectable, currently in development, addresses that source trail.

Document one number

Use the worksheet to document the number, its rule, the source, the comparison, unresolved questions, and acceptance. It includes this completed example and blank fields. Start with a report you can inspect and a number whose meaning you can confirm with its owner.