The Report That Sums the Wrong Grain
A wholesaler prints a monthly sales report grouped by customer. The report is built on a query that joins Orders to OrderLines. Each order has several lines. The report shows a 'Total order value' column by summing line amounts, then a grand total at the bottom. A manager notices the grand total is far larger than the sum of the customer subtotals, and one customer's figure looks double what accounts expect. The developer added a footer total and a group total in the report designer.
1. Explain why the grand total does not equal the sum of the customer subtotals, and how to correct the report.
Read the worked answer
The query is at line grain (one row per order line), but the report treats rows as if they were orders. Summing line amounts gives the correct order value only if each line is counted once per order. If the join duplicates lines - for example an extra join that multiplies rows - the same line is counted more than once, inflating both the customer figure and the grand total. The grand total and subtotals also disagree because they may be computed over different scopes (e.g. grand total over the raw query, subtotal over a grouped expression). The fix is to aggregate at the right grain: group by order (and customer) and sum lines within each order, then sum those order totals for the customer and grand totals. Verify by comparing a single order's line sum to its reported total.
2. How should the developer test the report before releasing it?
Read the worked answer
Pick one customer with a known number of orders and lines, and hand-calculate the expected order values, customer subtotal, and grand total. Compare each level of the report to the manual figures. Check that the number of detail rows matches the number of order lines, and that the count of orders matches the source. Then check the grand total equals the sum of the printed customer subtotals. Any mismatch points to a grain or scope error. Testing at each level (line, order, customer, grand total) catches the duplication that a single spot-check would miss.