Somebody runs the late order report and the total is absurd. Not slightly off. Absurd, by an order of magnitude, in a way that makes the whole report unusable and quietly teaches everyone to ignore it.
The instinct is to suspect the joins. Usually they are fine.
Check the report against itself first
Before touching anything, look at whether the report contradicts its own columns. If the quantity column on a row shows a believable number and the value column for that same row is enormous, the joins are not multiplying rows. A fan-out inflates everything uniformly. This is one column disagreeing with another on the same row, which means the arithmetic is wrong, not the row set.
That single observation eliminates the most time-consuming theory in about two minutes.
The actual mechanism
Open value wants to be requirement minus what has already shipped, priced. Something like:
(OurReqQty - OurJobShippedQty - OurStockShippedQty) * DocUnitPrice
The formula that produces the absurd totals derives open quantity from the job and stock quantities instead:
((OurJobQty - OurJobShippedQty) + (OurStockQty - OurStockShippedQty)) * DocUnitPrice
Those agree right up until they do not. Job quantity is what was put into production, and it has no obligation to equal what the line required. When a job was built for far more than the order line asked for, the second formula treats the entire excess as open demand and prices it.
One release with a job quantity wildly above its requirement is enough to dominate a report covering hundreds of rows.
Finding the culprit row
You do not need to read every row. Query for releases where job quantity plus stock quantity exceeds required quantity. On a healthy dataset that returns almost nothing, and the handful it returns are your entire variance.
This is a satisfying check because it either confirms the diagnosis immediately or tells you to look somewhere else. If that query returns nothing and the report is still wrong, the problem is not this.
Fixing it properly
Correct the formula to derive open quantity from requirement minus shipped, and then confirm two things:
- The suspect row now reports a sane value. Recompute it by hand and match.
- No row goes negative. Over-shipment exists, and a formula that produces negative open value has just moved the error somewhere less visible.
The part worth generalizing
Two lessons carry beyond this one report.
Convert reports, do not copy them. When a legacy report is rebuilt as a BAQ report, the formulas come across with it, defects included. A conversion is the best opportunity you will ever get to audit them, and the worst thing you can do is faithfully reproduce a wrong answer in a new tool. Budget audit time into every report conversion.
A report nobody trusts is worse than no report. Once a number has been publicly wrong, the business routes around it permanently, and it keeps consuming maintenance while informing nothing. Either fix it and say so loudly, or retire it.