2.3Record selection (filters)
The essentials in 30 seconds
- Select Expert = field + operator + value: equal, greater than, between, contains, in a list — combined with AND/OR.
- Filtering also changes totals: only kept records count — the scope decides the figure.
- Server first: simple conditions on base fields → WHERE pushed to the database — check via Show SQL Query.
Running mini-case — Farès and the report mixing two years
Farès's report totals the whole history: management wants the current year, North region, significant amounts. Farès opens the Select Expert: order date between 01/01 and 12/31 AND region = North. Totals change at once — normal: the filter also shrinks the summaries' scope. Then he checks Show SQL Query: the condition is in the WHERE clause, so the database returns only what is needed. He avoids the reverse trap: filtering on a complex formula that would have fetched the whole table before sorting locally. In 2.4, this selection will get a year parameter picked at runtime; in 2.5, the summary version will reuse this filtered scope.
Thread continued from 2.2: Farès's totals move to a filtered scope — and continues in 2.4 and 2.5.
1. Filtering with the Select Expert
A report rarely shows all data: record selection filters. The Select Expert: pick a field, an operator (is equal to, is greater than, is between, contains, is in a list…) and a value. Combine several conditions (AND/OR). Only kept records appear and count in totals — filtering changes results. Under the hood, selection becomes a selection formula (Record Selection Formula), editable, ideally converted into a WHERE clause sent to the database. Farès filters: current year AND North region.
2. Server-side vs client-side (performance)
A CRUCIAL performance point: server-side filtering (the database returns only the filtered rows — fast, light) versus client-side filtering (Crystal fetches ALL rows then filters locally — slow and heavy on large volumes). Some conditions — complex Crystal formulas, functions that cannot translate to SQL — cannot be pushed to the database and force local filtering. The rule: simple, direct filters on database fields stay server-side. Farès phrases his conditions on the date and region fields, not on calculations, to keep an efficient WHERE clause.
3. Logic and interactivity
Mind the logic of combined conditions: AND narrows (both at once → fewer results), OR broadens (either → more results), and parentheses matter as soon as you mix them. For several values of one field, prefer “is in the list”. Check the generated SQL (“Show SQL Query”) to confirm the filter is pushed to the database. And for a filter the user picks at runtime (“which year?”), use parameters (section 2.4) combined with selection — Farès will get there to stop editing the report every year.
🔎 Concrete example — 🔎 Hands-on example — A filter pushed to the database
Farès's report, filtered on year and region, with server check:
{Commandes.DateCommande} in Date(2026, 1, 1) to Date(2026, 12, 31)
and {Clients.Region} = "North"
Show SQL Query → the condition appears in the WHERE clause: server-side filtering, fast.Reading: simple selection formula on base fields, translated into WHERE.
Lesson: avoid complex formulas in the filter so it does not switch to client-side.
Section vocabulary
- Record selection
- Keeping only records matching a condition (year, region, threshold) via the Select Expert; filters the display AND totals.
Simple example: Farès keeps only 2026 orders from the North region.
What it is not: sorting, which orders without excluding.
Reflex: wanted scope = explicit selection.
- Select Expert
- The filtering tool: field + operator (equal, between, contains, in a list…) + value, with AND/OR combinations.
Simple example: Farès combines date between two bounds AND region equal to North.
What it is not: the calculation formula editor (level 3).
Reflex: filtering = field + operator + value.
- Selection formula
- The filter translated into a formula (Record Selection Formula), editable, ideally converted into a SQL WHERE clause.
Simple example: Farès's selection becomes a formula on date and region, visible in Show SQL Query.
What it is not: a calculation formula shown in a section.
Reflex: filter = formula checkable in the SQL.
- Server-side / client-side
- Server: the database filters via WHERE (fast); client: Crystal fetches everything then filters locally (slow on large volumes).
Simple example: Farès's simple filter stays server-side; a complex formula would switch it to local.
What it is not: two equivalent ways with no performance impact.
Reflex: simple on a base field = server.
In practice — Hands-on — Filter records
- Open the Select Expert: field + operator (equal, between, greater than…) + value.
- Combine several conditions if needed (AND/OR), minding logic (AND narrows, OR broadens; parentheses).
- Favour SIMPLE conditions on database fields for SERVER-SIDE filtering; avoid complex formulas.
- Check the generated SQL (“Show SQL Query”); verify totals reflect the filter.
Ready-to-say script — justifying the filtered scope (30 s)
“This report covers the current year, North region: the filter excludes the rest, so totals are at this scope — no longer the overall. The filter runs database-side, checked in the SQL: we fetch only what is needed, it is fast even on the full history. If you want another year, we will use a parameter.”
Say while showing Farès's selection formula, then the WHERE clause in Show SQL Query.
Copy-paste template — valid selection formulas
{Commandes.DateCommande} in Date(2026, 1, 1) to Date(2026, 12, 31)
{Commandes.DateCommande} in Date(2026, 1, 1) to Date(2026, 12, 31) and {Clients.Region} = "North"
{Commandes.Montant} > 1000
{Clients.Ville} in ["Algiers", "Oran"]Amount threshold
Farès keeps only orders above 1,000: the filter tightens the totals' scope.
Two wilayas
Algiers OR Oran: Farès uses “is in the list” rather than an impossible AND on one field.
A slow filter
Filter placed on a complex formula: everything is fetched then sorted locally — Farès goes back to base fields.
Forgotten parentheses
Ambiguous “A AND B OR C”: Farès groups explicitly “(A OR B) AND C” for the wanted result.
✓ Best practices
- Phrase simple conditions on base fields (server-side).
- For several values of one field, use “is in the list”.
- Check the generated WHERE clause to confirm server-side filtering.
✗ Common pitfalls
- Filtering on a complex formula: forces local filtering (slow).
- Confusing AND/OR (“City = Algiers AND City = Oran” returns nothing).
- Mixing AND/OR without parentheses (ambiguous result).
Key takeaways
- The Select Expert filters rows (field + operator + value), combinable (AND/OR). It filters the display AND totals.
- CRUCIAL: SERVER-SIDE filtering (pushed to the database, fast) vs CLIENT-SIDE (everything fetched then filtered, slow).
- SIMPLE conditions on base fields = server-side; complex formulas force local. Check “Show SQL Query”.
- Mind LOGIC (AND narrows, OR broadens; parentheses). For a runtime-picked filter, use PARAMETERS (2.4).
48h challenge — apply tomorrow
On your real report: set a 2-condition filter (period AND category), check the WHERE clause in Show SQL Query, try an “in the list”, then re-read your totals at the new scope.
- I can filter (field + operator + value, AND/OR) via the Select Expert.
- I can keep the filter server-side (simple conditions, WHERE checked).
- I re-read my totals at the filtered scope.
Frequently asked questions
Why server-side filtering, and how to ensure it?
Server-side, the condition is translated into a SQL WHERE clause and run by the database, which returns only useful records: fast, light. Client-side, Crystal fetches ALL rows then filters locally — disastrous on large volumes (time, memory, network). What blocks server-side: filters on complex Crystal formulas or non-translatable functions. To guarantee server-side: phrase simple, direct conditions on database fields (Date between X and Y, Region = “North”), avoid filtering on formula fields, and CHECK “Show SQL Query” — if your condition is in the WHERE clause, it is pushed to the database. It is a major performance lever.
How to combine AND/OR without mistakes?
AND narrows (all conditions required → fewer results); OR broadens (at least one → more results). Traps: (1) confusing AND/OR — “City = Algiers AND City = Oran” returns nothing (one city cannot be both); you needed OR, or better “is in the list [Algiers, Oran]”; (2) mixing AND/OR without parentheses (“A AND B OR C” is ambiguous). Reason in plain language, group with explicit parentheses “(Region = North OR Region = South) AND Amount > 1000”, prefer “in the list” for alternative values, and test the result count.
Which filtering to favour for performance?