4.1Subreports (linked & unlinked)
The essentials in 30 seconds
- A subreport = a complete report inside the main one: its own source, fields, formatting — the solution when a simple join is not enough (different sources, multiple one-to-many relationships).
- Linked or unlinked changes everything: unlinked = the same data shown once (global recap); linked = matching data per link field, re-executed for each main-report value.
- Farès's reflex: place a linked subreport at group level (one execution per group), never in the Details of a large report — and prefer the join when the tables are in the same database.
Threaded mini-case — Farès and the customer statement with invoices
Farès, a controller in Algiers, must produce the monthly customer statement: the main report lists customers from the management database, but the detailed invoices come from another extract that a join would inflate with duplicates (two one-to-many relationships: orders AND payments). He inserts a subreport linked on customer ID, placed in the Customer group header: for each customer, the subreport shows THAT customer's invoices, a single execution per customer. First milestone of the thread: Farès combines two sources without duplicates and without killing performance.
This same case returns in the lesson, the diagram, the script and the 48h challenge — and continues in 4.2, 4.3, 4.4 and 4.5.
1. A report inside a report: when the subreport is required
Level 4 opens the expert features. First among them: subreports — a complete report (its own source, fields, formatting) inserted into a section of the “main” report. As with Farès's statement, its strength is combining data that does NOT lend itself to a simple join: different sources (two databases, a database + a file), complex or multiple one-to-many relationships that would create duplicates, related information to present separately (for each customer, their latest invoices).
Decision rule: when the tables are in the same database and link cleanly through a key, a join remains simpler and faster — it is the default choice. The subreport is the tool for cases where the join is unsuitable: non-joinable sources, join duplicates, independent logic or aggregation. Insert it via “Insert → Subreport” (new report or import of an existing one), then define the links.
2. Linked vs unlinked: the link field does everything
Key distinction. An unlinked subreport is independent: it shows the same data wherever it sits — for example a global summary shown once in a report footer. A linked subreport is connected to the main report by a link field: for each main-report record or group, it shows the matching data — the orders of THIS customer, like each of Farès's customers' invoices.
Mechanism: in the “Links” step (Subreport Links), choose the main-report field (e.g. Customer.ID); Crystal Reports creates a parameter in the subreport and binds it to the matching field (e.g. Invoices.CustomerID) — the filter applies automatically, ideally server-side. The link field must exist on both sides. Classic pitfall: forgetting the link — the subreport then shows all its data, like an unlinked one.
3. Performance: placement decides the cost
A linked subreport re-executes for each link value of the main report. Placed in the Details section, it runs once PER row — disastrous on large volumes. Placed in a group header or footer, it runs only once per group — this is Farès's move: one execution per customer instead of one per invoice.
Further precautions: limit the subreport's data (server-side filter, useful fields only), avoid nested subreports (multiplied cost), consider an alternative (join, SQL command in 4.4, consolidated view) and test on a real volume. Subreports share data with the main report only with difficulty (shared variables = advanced).
🔎 Concrete example — 🔎 Concrete example — Each customer's invoices
Farès's statement: a customers main report, a linked and well-placed invoices subreport.
Main report: customer list (grouped by customer).
Subreport linked on customer ID → THIS customer's invoices.
Placed in the GROUP HEADER (one execution per customer, not per row).Reading: link by the common key, filter server-side, place at group level.
Lesson: related data in context, without duplicates or performance collapse.
Section vocabulary
- Subreport
- A COMPLETE report inserted into another (the main one): its own source, fields, formatting, displayed in a section of the main report.
Simple example: Farès shows invoices (another extract) in the customer statement without joining them.
What it is not: a mere extra section of the same report.
Reflex: non-joinable sources or join duplicates: think subreport.
- Linked subreport
- Connected to the main report by a LINK FIELD: shows the data matching each record or group, re-executed per link value.
Simple example: For each of Farès's customers, the subreport shows THAT customer's invoices.
What it is not: a global recap identical everywhere.
Reflex: matching data per record: define the link on both sides.
- Unlinked subreport
- Independent: shows the SAME data wherever it sits in the main report.
Simple example: Farès shows a global recap of unpaid invoices once in a report footer.
What it is not: a detail that follows each record.
Reflex: global recap shown once: unlinked in a report footer.
- Cost per execution
- A linked subreport executes once PER link value: Details = once per row (expensive), group = once per group.
Simple example: Farès goes from hundreds of executions (Details) to one per customer (group).
What it is not: a fixed cost independent of placement.
Reflex: linked + large volume: always move up to group level.
In practice — In practice — Insert a subreport
- Insert a subreport (“Insert → Subreport”): create a new report or import an existing one, with its own source and fields.
- Decide LINKED or UNLINKED: unlinked for a global recap (same data); linked for matching data per record or group.
- For a LINKED subreport, define the LINK FIELD (e.g. customer ID): it will show the data matching each main-report value.
- For PERFORMANCE, place a linked subreport in a GROUP header or footer (once per group) rather than in Details (once per row).
Ready-to-say script — justify the subreport to your manager (30 s)
“This is Farès: for the customer statement, I cannot join the invoices without creating duplicates — so I used a subreport linked by customer ID, placed at group level. One execution per customer instead of one per row: the statement stays accurate and opens in seconds.”
Say when asked why not “simply join”: show the total inflated by duplicates.
Copy-paste template — subreport scoping sheet
Need: [customer statement + invoices from another extract]
Choice: [join impossible → subreport]
Type: [linked on customer ID | unlinked — global recap]
Link: main [Client.ID] → subreport [Factures.ClientID], server-side filter [yes]
Placement: [Customer group header — one execution per customer]
Content: [useful fields only: invoice no., date, amount]Multi-source customer statement
Like Farès: main report on the management database, subreport linked on the other extract — one execution per customer.
Double one-to-many relationship
A customer's orders AND payments: one subreport per relationship to avoid row multiplication.
Global recap
Total unpaid shown once in a report footer: unlinked subreport, no link field.
Slow large volume
Move the subreport up to the group, filter server-side, consider a SQL command or a view (see 4.4).
✓ Best practices
- Place a linked subreport at GROUP level, not in Details.
- Filter the subreport server-side and include only useful fields.
- Prefer a join when the tables are in the same database.
✗ Common pitfalls
- A linked subreport in the Details of a large report (disastrous performance).
- Forgetting the link (the subreport then shows all its data).
- Chaining nested subreports (multiplied cost).
Key takeaways
- A SUBREPORT is a COMPLETE report inserted into another. Strength: combining hard-to-JOIN data — different sources, complex relationships, related information.
- KEY distinction: LINKED (link field — matching data per record/group) vs UNLINKED (independent, same data).
- PERFORMANCE: a linked one executes once PER link value. Details = very expensive; GROUP header/footer = far fewer executions.
- Insert via “Insert → Subreport” then define the LINKS. Test on a real volume.
48h challenge — apply tomorrow
On a real statement: insert a linked subreport (common key on both sides), place it at group level, limit its fields and compare the opening time with a placement in Details.
- I can insert a subreport and choose linked or unlinked as needed.
- I can define a link field present on both sides, filtered server-side.
- I place a linked subreport at group level and test on a real volume.
Frequently asked questions
When should I use a subreport instead of a join, and how do I avoid performance problems?
When the tables are in the SAME database and link cleanly through a key, a JOIN is simpler and faster — it is the default choice. Reserve the SUBREPORT for cases where the join is unsuitable: DIFFERENT non-joinable sources, multiple one-to-many relationships that would create duplicates, related data to present separately. Performance: a linked subreport re-executes for each link value — NEVER place it in the Details of a large report: put it in a GROUP header or footer, limit its data (server-side filter, useful fields), avoid nested subreports, consider a SQL command or a view (see 4.4), and test on a real volume.
How do I match a subreport's data to each main-report record?
By LINKING the subreport to the main report through a common FIELD (e.g. customer ID). For each main-report record or group, the main report passes a link value and the subreport filters its data on it. In practice: insert the subreport, then in the “Links” step choose the main-report field (Client.ID) and bind it to the matching subreport field (Factures.ClientID) — Crystal Reports creates the parameter and applies the filter automatically. Key points: key present on BOTH sides, server-side filter, placement (Details or group) that sets the execution frequency. Without a link, the subreport shows all its data.
Where should a linked subreport be placed to preserve performance?