3.1Formula editor (Crystal/Basic syntax)
The essentials in 30 seconds
- A formula = a missing value: total incl. tax, margin, full name, calculated category — created in the formula editor, it appears as a field to place in any section.
- Two syntaxes, one choice: Crystal (historic, the best documented) or Basic (close to VB) — same result, but only one per report to stay consistent.
- Check, type, convert: always check the syntax before use and watch the returned type (ToText, ToNumber) — mixing text and numbers without conversion makes the formula fail.
Running mini-case — Farès computes his totals incl. tax without a spreadsheet
Farès, management controller in Algiers, retypes the tax-inclusive amounts of his invoices every month into a spreadsheet next to his Crystal report — a source of errors and wasted time. He opens the formula editor, inserts the field from the list and writes {Commandes.MontantHT} * 1.19 (Algerian VAT): the calculated field appears in the explorer, he places it in the Details section, checks the syntax, and the total incl. tax computes itself on every refresh. No more double entry: the report produces its own missing value.
This same case returns in the lesson, the diagram, the script and the 48h challenge.
1. The formula editor: fields, operators, functions
A formula is a calculation or logic that YOU create to produce a value that does not exist as such in the database: a tax-inclusive amount, a margin, a full name, a category. You create it in the formula editor (Formula Workshop); the formula field then appears in the explorer and is placed in a section like any other field. The editor helps: a list of fields to insert (written {table.field}), a list of functions by category (math, strings, dates, conditions) and operators. Farès picks from there instead of typing everything by hand — fewer typos in field names.
The building blocks of a formula: field references in braces ({Commandes.MontantHT}), operators (arithmetic + - * /, comparison = <> > < >= <=, logical And Or Not, concatenation + or &), built-in functions (Round, UpperCase, Year, If…Then…Else…) and literals (numbers like 1.19, text in quotes, dates in # like #2026-01-01#). Example: total incl. tax = {Commandes.MontantHT} * 1.19; a label = {Client.Nom} + " " + {Client.Prenom}.
2. Two syntaxes, one check, one type
Crystal Reports offers TWO SYNTAXES to choose from: Crystal syntax (historic, specific to the tool, the most widespread in the documentation — the formula value is the last evaluated expression) and Basic syntax (close to Visual Basic, you assign the result to “formula = …”). They do the same thing: by default, Crystal syntax, because there are more examples for it and other people's reports are easier to read. And stick with it throughout the report.
Two reflexes before using a formula. First CHECK the syntax (check button): the editor flags missing parentheses and mistakes. Then watch the TYPE: a formula returns a precise type (number, text, date, boolean); mixing text and numbers without conversion causes errors — convert with ToText, ToNumber. A formula can then be displayed, totalled, used in a filter or in formatting — and formulas combine with each other.
🔎 Concrete example — Total incl. tax and full name, the first two formulas
Farès writes his two basic formulas in Crystal syntax.
Total incl. tax (number): {Commandes.MontantHT} * 1.19
Full name (text): {Client.Nom} + " " + {Client.Prenom}
→ check the syntax with the button, place each field in the wanted section.Reading: one calculation and one concatenation already cover most everyday needs.
Lesson: inserting fields from the list avoids typos.
Section vocabulary
- Formula
- Calculation or logic created by the user to produce a value missing from the database (total incl. tax, margin, first + last name, category). Created in the editor, placed like a field.
Simple example: Farès's total incl. tax: {Commandes.MontantHT} * 1.19.
What it is not: manually retyping tax-inclusive totals in a spreadsheet on the side.
Reflex: as soon as a value is missing, build it as a formula.
- Formula editor (Formula Workshop)
- The workshop for writing formulas: lists of fields ({table.field}), functions by category and operators, with syntax checking.
Simple example: Farès inserts {Client.Nom} from the list there.
What it is not: typing field names by hand, a source of mistakes.
Reflex: always insert from the lists provided.
- Crystal vs Basic syntax
- Two languages to choose from for the same formula: Crystal (historic, the most widespread) and Basic (close to Visual Basic, “formula = …”).
Simple example: Farès writes everything in Crystal to stay consistent.
What it is not: mixing both syntaxes within the same report.
Reflex: Crystal by default, a single syntax per report.
- Building blocks of a formula
- Field references ({table.field}), operators (arithmetic, comparison, And/Or/Not, concatenation), built-in functions, literals (numbers, “text”, #dates#).
Simple example: {Client.Nom} + " " + {Client.Prenom}.
What it is not: a calculation copied by hand outside the report.
Reflex: assemble block by block, then check.
- Formula type
- A formula returns a precise type (number, text, date, boolean); mixing types without conversion causes errors.
Simple example: ToText({Commandes.Montant}) to display a number in a label.
What it is not: adding text and a number hoping it will “work out”.
Reflex: convert explicitly with ToText, ToNumber.
In practice — Write a first formula
- Open the formula editor and create a simple calculation (e.g. total incl. tax = {MontantHT} * 1.19), inserting the field from the list.
- Try a text concatenation (e.g. {Client.Nom} + " " + {Client.Prenom}) and browse the functions by category (math, text, dates).
- CHECK the syntax (check button): fix the reported errors before using the formula.
- Place the formula field (now in the explorer) in a section like a normal field; watch the TYPE (convert with ToText/ToNumber if needed).
Ready-to-say script — explain your formula to the chief accountant (30 s)
“Tax-inclusive totals are no longer retyped: I created a calculated field — amount excl. tax multiplied by 1.19 — directly in the report, in Crystal syntax, checked by the editor. It recalculates itself on every refresh, with no double entry and no copying errors.”
Say at the closing meeting; show the formula and the check button.
Copy-paste template — first formulas (Crystal syntax)
// Total incl. tax (number)
{Commandes.MontantHT} * 1.19
// Full name (text)
{Client.Nom} + " " + {Client.Prenom}
// Margin (number)
{Ventes.PrixVente} - {Ventes.Cout}
// Basic equivalent: formula = {Commandes.MontantHT} * 1.19Freelancer
Two formulas (one calculation, one concatenation) automate what you used to retype by hand.
SME in Algiers
Farès computes his 19% totals incl. tax in the report: no more parallel spreadsheet.
Project team
Impose a single Crystal syntax in the report so everyone can re-read each other's formulas.
Beginner
This week: one calculation formula + one text formula, syntax checked with the button.
✓ Best practices
- Insert fields from the editor list rather than typing them (zero typos).
- Choose Crystal syntax by default and stick with it throughout the report.
- Systematically check the syntax before using a formula, and watch its type.
✗ Common pitfalls
- Mixing Crystal and Basic syntaxes in the same report: nobody can follow it.
- Typing field names by hand instead of inserting them (invisible mistakes).
- Adding text and numbers without conversion: the formula fails instead of “working it out”.
Key takeaways
- A FORMULA produces a value missing from the database (total incl. tax, margin, first + last name, category); created in the editor, it is placed like a field.
- Two SYNTAXES (Crystal, the most widespread / Basic, close to VB): stick to ONE for consistency.
- Building blocks: {table.field} fields, operators, functions by category, literals (numbers, “text”, #dates#).
- ALWAYS CHECK the syntax and watch the TYPE (ToText, ToNumber to convert).
48h challenge — apply tomorrow
Create 1 calculation formula (total incl. tax or margin) + 1 text formula (full name), syntax checked, placed in your real report.
- I create a calculated field instead of retyping by hand.
- I write in a single syntax (Crystal by default).
- I check the syntax and I convert types explicitly.
Frequently asked questions
Crystal or Basic syntax: which to choose, and how to get started?
Both syntaxes produce the SAME result; the choice is a matter of consistency and familiarity. CRYSTAL syntax is the historic language, specific to Crystal Reports — the most WIDESPREAD: most documentation and existing reports use it, and the formula value is the last evaluated expression. BASIC syntax is close to Visual Basic (“formula = …”), more familiar if you know VB/VBA. By default, choose Crystal (more examples, easier reading of other people's reports) and stay CONSISTENT throughout the report. To get started: start SIMPLE (one arithmetic calculation, one concatenation), insert fields from the list, browse functions by category, and CHECK the syntax systematically — the editor flags missing parentheses and mistakes.
What are formulas actually for, and which common calculations can I build?
Formulas produce values and logic missing from the database, and are used everywhere: CALCULATE derived values (total incl. tax = excl. tax × rate, margin = sale − cost, discount, quantity × price, percentage); COMPOSE text (first + last name, address on one line, “Invoice No. ” + number); CATEGORIZE with If…Then…Else (3-4) to group on later (3-5); feed FILTERS (mind the server side, 2-3); drive CONDITIONAL FORMATTING (3-2) and section SUPPRESSION (2-5); handle DATES (year, month, gaps); handle NULL VALUES. Typical management calculations for Farès: 19% totals incl. tax, margin per line (sale − cost), line total (quantity × unit price), share in % (part / total × 100).
Where do you create a calculated value (total incl. tax, margin) in Crystal Reports?