2.2Totals, subtotals & summaries
The essentials in 30 seconds
- Summary = operation + level: Sum, Count, Average… inserted via “Insert Summary” — the chosen section decides the totalled scope.
- Group footer = subtotal, report footer = grand total: same Sum, two meanings — wrong level = meaningless total.
- Always cross-check: an inflated total often comes from join duplicates — check against a known figure.
Running mini-case — Farès and the total that did not add up
Farès's report is grouped (Region > Customer) but has no figures: management wants the total per customer, per region and the grand total. Farès inserts a Sum of the amount in the Customer Group Footer (customer subtotal) and the same Sum in the Report Footer (grand total). First check: the grand total exceeds the accounting figure by 12%. Cause: a one-to-many join duplicates rows and inflates the sum. Join fixed, total cross-checked: all three levels add up. In 2.3, Farès will filter on the year for totals on a stable scope; in 2.4, he will drive the period with a parameter; in 2.5, he will hide the detail for the management summary.
Thread continued from 2.1: Farès's grouped report gets its totals — and continues in 2.3, 2.4 and 2.5.
1. Summary fields
The great value of a management report is its totals: Crystal Reports calls them summary fields (Summary). You insert a summary on a field by picking an operation: Sum — the most common (total of amounts); Count — counting records; Average; Maximum / Minimum, and others. Via “Insert Summary”: pick the operation and the location (which group footer, or the report footer). Farès summarizes his orders' amount field: Sum for turnover, Count for the number of orders.
2. The level decides what is totalled
The ESSENTIAL point is the level (the section): a summary in the Group Footer = a subtotal (this group's total: THIS customer's orders); in the Report Footer = the grand total (all rows). With nested groups (Region > Customer), the Customer Group Footer gives the per-customer total, the Region Group Footer the per-region total: a pyramid of totals. Placing a summary at the wrong level gives a meaningless total — Farès checks each summary is in the right section before formatting (bold, line above, number format).
3. Right operation, watch for duplicates
The operation must match the need: Sum to add amounts, Count to count rows (not the sum) — the Sum/Count mix-up is the classic trap. A summary is formatted like a number (decimals, separators, symbol) and highlighted. Watch out: join duplicates (level 0) inflate sums when a one-to-many relationship duplicates amounts — the total “looks” plausible but is wrong. Farès's safeguard: always cross-check a total against a known value (accounting figure) before releasing the report.
Did you know?
An unchecked total is an opinion, not a figure.
🔎 Concrete example — 🔎 Hands-on example — Subtotal vs grand total
Farès's same Sum, two locations, two meanings:
Sum({Commandes.Montant}) in the Customer Group Footer → total for THIS customer.
Sum({Commandes.Montant}) in the Report Footer → grand total (all orders).
Count({Commandes.NumCommande}) in the Group Footer → number of orders for the customer.Reading: the summary's section decides the scope: group = subtotal, report = grand total.
Lesson: cross-check the grand total with a known figure to detect join duplicates.
Section vocabulary
- Summary field
- A total computed on a field: Sum, Count, Average, Max/Min — inserted via “Insert Summary”.
Simple example: Farès inserts a Sum of the orders' amount.
What it is not: a database field shown as is.
Reflex: total wanted = Insert Summary.
- Subtotal (Group Footer)
- A summary placed in the GROUP Footer: the group's total (e.g. total orders of THIS customer).
Simple example: Sum of the amount in Farès's Customer Group Footer.
What it is not: the grand total of the whole report.
Reflex: total per block = group footer.
- Grand total (Report Footer)
- A summary placed in the REPORT Footer: the total of all rows (Grand Total).
Simple example: Sum of the amount in Farès's Report Footer, cross-checked with accounting.
What it is not: a subtotal limited to one group.
Reflex: overall total = report footer.
- Summary operation
- The chosen calculation: Sum adds, Count counts rows, Average averages, Max/Min take the extreme.
Simple example: Farès takes Sum for turnover, Count for the number of orders.
What it is not: the summary's location in the report.
Reflex: question asked = operation (total → Sum, how many → Count).
In practice — Hands-on — Add totals and subtotals
- Select a numeric field (amount) and “Insert Summary”: pick the operation (Sum most often) and the location.
- Place a SUBTOTAL in the GROUP Footer (group total) and a GRAND TOTAL in the REPORT Footer.
- Format totals as numbers (decimals, separators, symbol) and highlight them (bold, line above).
- Check a total against a known value (to detect possible join duplicates inflating sums).
Ready-to-say script — presenting the totals pyramid (30 s)
“Each customer has its subtotal in the group footer, each region cumulates its customers, and the report footer gives the grand total — the three levels cross-check. I verified the grand total against accounting: it adds up. You can steer by customer, by region and overall, on controlled figures.”
Say while showing Farès's report: one customer group footer, one region footer, then the grand total.
Copy-paste template — summary pyramid
[Customer subtotal] Sum({Commandes.Montant}) → Customer Group Footer
[Region subtotal] Sum({Commandes.Montant}) → Region Group Footer
[Grand total] Sum({Commandes.Montant}) → Report Footer
[Counter] Count({Commandes.NumCommande}) → Customer Group Footer
[Control] compare the grand total with the known accounting figureAverage basket
Farès adds an Average of the amount per customer: the average basket completes the subtotal.
Largest order
Maximum amount per region: Farès spots the largest order of each area.
Number of active customers
Distinct Count of customers per region: Farès counts distinct customers, not orders.
A rate you must not sum
Summing rates or unit prices is meaningless: Farès uses the average, not the sum.
✓ Best practices
- Pick the operation from the question (total = Sum, counting = Count).
- Place the subtotal in the Group Footer, the grand total in the Report Footer.
- Always cross-check your totals against an expected figure.
✗ Common pitfalls
- A summary at the wrong level (meaningless total).
- Confusing Sum (adding) with Count (counting rows).
- Summing amounts duplicated by a one-to-many join.
Key takeaways
- TOTALS are SUMMARY fields: Sum (most common), Count, Average, Max/Min.
- ESSENTIAL: the LEVEL decides what is totalled. Group footer = subtotal; Report footer = grand total.
- The OPERATION must match the need (Count counts rows, not the sum). A summary is formatted and highlighted.
- Watch out: JOIN duplicates inflate sums → check a total against a known value.
48h challenge — apply tomorrow
On your grouped report: insert a Sum in the group footer and the same Sum in the report footer, add a Count, format (bold, line), then cross-check the grand total against a known figure.
- I can insert a summary (operation + location) via “Insert Summary”.
- I can place subtotal (group footer) and grand total (report footer).
- I cross-check my totals against a known value (anti-duplicates).
Frequently asked questions
How to get subtotals and a grand total, and avoid wrong totals?
The level (section) where you place the summary decides what it totals. Subtotal per group → GROUP Footer (totals the group); grand total → REPORT Footer (totals everything). Insert the field, “Insert Summary”, pick Sum and the location. Wrong totals mostly come from: (1) a wrong level (check the section); (2) join duplicates — a one-to-many relationship duplicates amounts and inflates sums (the total “looks” plausible but is wrong); (3) a wrong operation. The safeguard: always cross-check your totals with a known value.
Sum, Count, Average… how to pick the operation?
Reason from the question. “What is the total?” (amounts, quantities) → SUM. “How many items?” (rows) → COUNT. “How many distinct values?” → DISTINCT COUNT. “What average?” → AVERAGE. “The largest/smallest?” → MAX/MIN. Traps: confusing Sum and Count (total sales = Sum; number of sales = Count); summing a non-additive field (a rate, a unit price, an identifier is meaningless to add); counting duplicates. The right operation is the one that answers exactly the question asked.
Where to place a summary to get a per-group subtotal?