Level 6 · In practice

6.1Real-life cases

Goal: see how the skills from levels 0 to 5 chain together on real reports.

Estimated time: 14 min · If you only have 5 min: read “The essentials in 30s” + the diagram.

The essentials in 30 seconds

Assemble: clean connection and joins, nested groups, totals at the right level, {?Mois} parameter pushed server-side, Top N and chart.
Order: a running balance is only correct when sorted by date and reset on change of Customer group.
Cross-check: grand total verified against a known figure, pagination controlled, export matched to use (PDF for archive, Excel for data).

Farès thread — two monthly closes in Algiers

Farès, a management controller in Algiers, reviews his two standout projects of the quarter: the monthly sales-by-region-and-customer statement requested by management (subtotals, ranking, chart, PDF export), and the account statement with cumulative balance requested by accounting (interactive on screen, exportable). Both cases below replay his sequence: connect cleanly, group, total at the right level, filter server-side, format, cross-check, export.

Each case follows the Context → Steps → Outcome flow: replay it on your own statements.

Putting the building blocks end to end

The previous levels laid the building blocks (connection, groups, totals, parameters, formulas, formatting, export); here we assemble them under real conditions: it is the chaining of the right reflexes that separates a correct, fast statement from a wrong or unreadable report. Two cases walked through from A to Z.

Case 1 — A sales-by-region statement, from A to Z

Context: Management wants a monthly sales statement by region and by customer, with subtotals, a ranking and a chart, exportable to PDF. Risk: wrong totals or an unreadable statement.

Déroulé :

Connect to the database and set clean joins, without duplicates (N0).
Group by Region then by Customer (nested groups) and sort lines by date (N2.1).
Insert subtotals at the right level: by customer, by region, grand total (N2.2).
Filter on the period via a {?Mois} parameter, pushed server-side (N2.3, N2.4).
Add a Top 10 customers (Group Sort / Top N) and a bar chart of sales by region (N3.5, N4.2).
Cross-check the grand total against a known figure, then export to faithful PDF (N5.4, N5.1).

Result: A structured, correct (verified totals) and readable statement, distributable every month by simply changing the parameter.

Case 2 — An account statement with a running balance

Context: Accounting needs a per-customer statement showing, for each transaction, the cumulative balance — exportable and interactive on screen. Challenge: a wrong running total if the sort order or the reset is misconfigured.

Déroulé :

Group by Customer and sort transactions by date (order is vital for a running total, N2.1).
Create a running total field (Sum) reset on change of Customer group (N3.3).
Colour negative balances red with conditional formatting (N3.2).
Hide details for a summary view + drill-down to explore a customer (N2.5, N4.5).
Add a “debtor balance” alert beyond a threshold (N4.5).
Check pagination and export (PDF for the archive, Excel “data only” for analysis, N5.1).

Result: A correct statement (running balance right because sorted and properly reset), readable and usable both on screen and on paper.

Common misinterpretation: “copying the cases as is”. No. The cases show a production order (connect, structure, total, filter server-side, cross-check, export), not a recipe to copy: reuse the order, adapt tables, groups and parameters to your own statement.

Text version of the diagram (SEO / screen readers / translation): Full chain: 1) Connect (clean joins without duplicates, N0). 2) Structure (nested Region-then-Customer groups, sort by date, N2.1). 3) Total (subtotals by customer and by region, grand total, N2.2; running total reset by Customer, N3.3; Top N, N3.5). 4) Filter ({?Mois} parameter built into server-side selection, N2.3/N2.4; nulls handled, N3.1). 5) Enhance (conditional formatting N3.2, chart N4.2, drill-down and alert N4.5). 6) Cross-check and distribute (check against a known figure N5.4, pagination and PDF/Excel export N5.1). Lesson: a correct, fast statement comes from the chaining, not from a single trick.

Ready-to-say script — scoping a statement before building it (30 s)

“Our need: [sales by region / statement with balance]. Our joins: [tables, clean, without duplicates]. Our groups: [Region then Customer, sorted by date]. Our totals: [subtotals in group footer, grand total]. Our filter: [{?Mois} server-side]. We cross-check before distributing.”

Copy-paste template — statement scoping sheet

STATEMENT [sales by region / customer statement] :
- Joins: [tables + links, grain checked, no duplicates]
- Groups: [Region then Customer] + sort [date]
- Totals: [subtotal in group footer / grand total in report footer / running total reset by Customer]
- Filter: {Field.Date} In {?Mois} (server-side, SQL checked)
- Cross-check: [grand total vs known figure] + export [PDF archive / Excel data]

Small business / tight budget

One small case replayed small: one statement, one {?Mois} parameter, totals cross-checked against accounting, PDF export.

Algeria SME

Adapt to local realities: test on the real database volume, plan the Excel “data only” export for accounting.

Remote / hybrid

Double traceability at a distance: verified SQL, cross-check figure and report version noted and shared.

Cross-cultural

Spell out formats before transposing a case: dates, numbers and headings must speak to the audience.

Caution: Generic reminder: a wrong statement costs more than no statement — check joins, total levels and server-side filtering, cross-check every total against a known value before distributing, and never hard-code database credentials.

48h challenge — apply tomorrow

Take 1 real statement and check its 3 critical points: joins without duplicates, totals at the right level, server-side filter. Cross-check the grand total.

Section exit checklist

I can chain connection, groups, totals, server filter and export on a real statement.
I have a replayable scoping sheet (joins, groups, totals, filter, cross-check).
I have done the 48h challenge or scheduled its date.

Self-assessment 1-5: are my totals cross-checked before distribution?is my filter pushed server-side?

Next : Recurring problems (playbook) (V3) →

Check your understanding

Farès builds the sales-by-region statement. Which sequence follows the guide?