4.4SQL Commands & multi-source reports
The essentials in 30 seconds
- The SQL command = writing your own query: instead of letting Crystal Reports generate it, you write SELECT, FROM, WHERE, JOIN — executed as-is like a “virtual table”.
- Maximum control and performance: exact joins, filters and aggregations pushed to the database — decisive on large volumes and for complex queries (subqueries, UNION).
- Farès's reflex: Crystal parameters in the query to filter server-side; subreports for different sources — and never a hard-coded credential in the command.
Threaded mini-case — Farès and the server-side top customers
Farès, a controller in Algiers, must output revenue by customer for the chosen year: through the interface, Crystal brings back all rows then aggregates — too slow on hundreds of thousands of orders. He adds a SQL command that filters by year via a parameter and aggregates in the database (GROUP BY), bringing back only totals. For the second source (a discounts file that cannot be joined), he keeps a linked subreport (see 4.1). Fourth milestone of the thread: Farès makes the database work, not the client workstation.
Continued from cases 4.1-4.3 — Farès moves from visuals to database-side performance.
1. The SQL command: writing instead of clicking
For advanced needs, the SQL command: instead of picking tables and letting Crystal Reports generate the query, you write your own query (SELECT … FROM … WHERE … JOIN …) which becomes the report source, executed as-is like a “virtual table”. You add it in the connection wizard.
Uses: total control (joins, filters, database-side calculations), complex queries impossible or painful via the interface (elaborate joins, subqueries, SQL groupings, UNION, database-specific functions), performance (push work to the database, optimized for that, and bring back only the result). Include Crystal parameters in it for interactive server-side filtering, like Farès's year parameter.
2. Multi-source reports: choose by source nature
Combine several origins depending on the case. Same database → classic joins or SQL command (fast: the database combines in one query). Different non-joinable sources (two databases, database + file) → subreports (see 4.1), often the best approach: each source in its own report, linked by a common field.
Crystal Reports also lets you add several connections in one report (multiple connections), but cross-source joins sometimes run client-side — expensive on large volumes, reserved for small cases. For complex, recurring or bulky combinations, the most robust option is often to consolidate upstream (database view, summary table, ETL) then point the report at a single source.
3. Skill and maintenance: the price of control
The SQL command requires SQL mastery: the query must be valid and optimized — a bad query yields a slow or wrong report — and it is code to maintain (document, version), unlike drag-and-drop. When in doubt, start with the interface; move to the SQL command only when hitting a limit.
This is where reporting meets database mastery — powerful, but demanding in skills and rigor. On security: read-only account, parameters instead of concatenation, no hard-coded password (see caution below).
🔎 Concrete example — 🔎 Concrete example — Filter server-side in SQL
Farès's top customers: a parameter in the query, aggregation in the database.
SELECT client, SUM(montant) AS total
FROM commandes
WHERE annee = {?Annee}
GROUP BY client
→ The database filters and aggregates; Crystal receives the minimum.Reading: parameterized WHERE + database-side GROUP BY = only totals travel.
Lesson: maximum control and performance on large volumes — valid, optimized, documented query.
Section vocabulary
- SQL Command
- Write YOUR OWN SQL query (SELECT, FROM, WHERE, JOIN) as the report source, executed as-is by the database.
Simple example: Farès writes the top customers by year instead of letting Crystal generate the query.
What it is not: picking tables by drag-and-drop.
Reflex: interface limit or slowness: switch to the SQL command.
- Server-side filtering
- Crystal parameters embedded in the query: the database filters and aggregates, Crystal receives only the needed result.
Simple example: The year chosen by Farès filters in the WHERE executed by the database.
What it is not: bring back all rows then filter in the report.
Reflex: large volume: parameterized WHERE + database-side aggregation.
- Multi-source reports
- Combine several origins: joins or SQL (same database), SUBREPORTS (different sources), multiple connections (client-side joins, expensive).
Simple example: Farès: SQL command for the orders database, subreport for the discounts file.
What it is not: mix everything in one multi-source connection on large volumes.
Reflex: different non-joinable sources: subreports first.
- Upstream consolidation
- Prepare data before Crystal (view, summary table, ETL) then point the report at a single source — the most robust for heavy cases.
Simple example: Farès has an annual sales view created for recurring reports.
What it is not: combine heterogeneous volumes client-side on each run.
Reflex: complex or recurring combination: consolidate once, report often.
In practice — In practice — Use a SQL command
- In the connection wizard, add a SQL COMMAND: write your query (SELECT … FROM … JOIN … WHERE …) — it becomes the report source.
- Embed Crystal PARAMETERS in the query for interactive server-side filtering.
- Check that the query is VALID and OPTIMIZED (database-side joins, filters, aggregations); test the result and performance.
- For DIFFERENT non-joinable sources, prefer SUBREPORTS (4.1) over multi-connection (expensive client-side joins).
Ready-to-say script — explain the SQL command to IT (30 s)
“This is Farès: for the annual top customers, I wrote a SQL command that filters by year and aggregates in the database — we bring back only totals instead of hundreds of thousands of rows. The query is tested, documented, and the report runs with a read-only account, no hard-coded password.”
Say to get database access approved: show the query, the account used and the measured gain.
Copy-paste template — parameterized SQL command (valid)
SELECT c.nom_client AS client,
SUM(l.montant) AS total
FROM clients AS c
INNER JOIN commandes AS l
ON l.id_client = c.id_client
WHERE l.annee = {?Annee}
GROUP BY c.nom_client
ORDER BY total DESCAnnual top customers
Like Farès: WHERE on the parameterized year + database-side GROUP BY — only totals travel.
Complex query (UNION)
Combine two compatible SELECTs with UNION in the command when the interface does not allow it.
Heterogeneous sources
Database on one side, file on the other: linked subreport rather than an expensive multi-connection.
Recurring bulky case
Request an upstream view or summary table: the report connects to one fast single source.
✓ Best practices
- Start with the interface; move to the SQL command if you hit a limit.
- Push joins, filters and aggregations to the database; leverage indexes.
- Consolidate upstream (view, table) for complex or bulky combinations.
✗ Common pitfalls
- A badly written SQL query (slow or wrong report).
- Multi-connections on large volumes (client-side joins).
- Forgetting that the SQL command is code to maintain.
Key takeaways
- The SQL COMMAND = writing YOUR OWN query as the source. Maximum CONTROL and PERFORMANCE: joins, filters, aggregations and subqueries DATABASE-side.
- Uses: COMPLEX queries (subqueries, UNION, aggregations, functions), total control, performance. Embedded PARAMETERS (server-side filtering).
- MULTI-SOURCE: joins or SQL (same database); SUBREPORTS for DIFFERENT sources; multi-connection possible but CLIENT-SIDE joins (expensive); consolidate upstream for heavy cases.
- Requires SQL MASTERY (valid, optimized query) and it is CODE to MAINTAIN.
48h challenge — apply tomorrow
Convert a slow report: rewrite its source as a SQL command (parameterized filter + database-side aggregation), measure the gain and document the query.
- I can write a valid, optimized SQL command with parameters.
- I can choose the multi-source approach by source nature.
- I work read-only, with no hard-coded credential, documented query.
Frequently asked questions
When should I write a SQL command instead of using the table picker?
The INTERFACE (tables + links, Crystal generates the query) is accessible, visual and easy to maintain: the default choice for most reports. Write a SQL COMMAND for: COMPLEX queries the interface handles poorly (subqueries, UNION, advanced aggregations, database-specific functions), maximum PERFORMANCE (push joins, filters and aggregations to the database — decisive on large volumes), precise CONTROL over the query. Condition: master SQL (valid, optimized, documented query). When in doubt, start with the interface and move to SQL when hitting a limit.
How do I combine data from several different sources?
By source nature. SAME database → JOINS or SQL command: the database combines in one query (fast). DIFFERENT non-joinable sources → linked SUBREPORTS (each source in its own report, linked by a common field; group-level performance) or multi-connection (Crystal combines CLIENT-SIDE — slow on large volumes, small cases only). For complex, recurring or bulky combinations, the most robust option is to CONSOLIDATE upstream (view, summary table, ETL) then point one report at a single source.
What does a SQL command offer over table selection?