0.4Connecting to a data source
The essentials in 30 seconds
- Database Expert = entry point: connection type (native or ODBC/OLE DB, Excel/CSV file), parameters, then only the strictly necessary tables.
- Joins make accuracy: several tables = link on a common field (Clients.ID = Commandes.ClientID) — otherwise duplicates and wrong totals.
- 32/64-bit blocks everything: ODBC driver matched to the software, credentials not hard-coded, and filtering at the source for performance.
Running mini-case — Farès connects his sales without inflating totals
Farès, management controller in Algiers, connects his sales statement: via the Database Expert, he chooses his source (the company database, native driver), selects only Customers and Orders — not the 40 tables of the database. In the links wizard, he joins Clients.ID = Commandes.ClientID and checks the join type. First Preview: revenue looks doubled — he spots a badly handled one-to-many relationship, fixes it, then cross-checks the total against the known accounting figure. He also notes never to hard-code his password and to filter at the source. His data foundation is reliable.
Self-contained case for this section: it reappears in the lesson, the diagram, the script and the 48h challenge of section 0.4.
1. Choosing a source and tables
A report is only as good as its data: the first step is to connect to a source. Families: relational databases (SQL Server, Oracle, MySQL, SAP HANA…) with native drivers (dedicated driver, often faster) or via ODBC/OLE DB (universal standards); files (Excel, Access, CSV, XML); SAP sources. Via the connection wizard (Database Expert): choose the type, provide the parameters (server, database, credentials; or file path), then the tables to use — only the useful minimum. Farès takes only Clients and Orders for his list: fewer tables = simpler, faster report with fewer error risks.
2. Joins: the critical point
As soon as a report uses several tables (Customers and Orders), Crystal Reports must know how to relate them — via a join on a common field (Clients.ID = Commandes.ClientID). The links wizard (Links) lets you define and check these links. This is critical: badly defined joins produce wrong reports — duplicates inflating totals (one-to-many relationship: each customer repeated per order), missing rows (wrong type: an inner join hides non-matching records), even cartesian product (unlinked tables: row explosion) — often with no error message. So Farès always cross-checks his totals against a known figure before distributing.
3. Watch points
Security: do not hard-code passwords; the connection may ask for authentication at runtime. 32/64-bit architecture: an ODBC source must match the software architecture — a mismatch is THE frequent cause of “source not found”. Performance: filter at the source (record selection, level 2) rather than loading entire tables. And you can change source later (“Set Datasource Location”: test database → production) without rebuilding the report. Farès applies all four: clean credentials, matched driver, filter at the source, controlled test → production switch.
🔎 Concrete example — 🔎 Worked example — A join inflating totals
Farès links Customers and Orders and checks his revenue.
Clients ⨝ Commandes on Clients.ID = Commandes.ClientID.
Each customer appears once per order (one-to-many).
→ Without care: revenue summed over duplicated rows = WRONG.
→ Farès reflex: cross-check against the known accounting revenue.Reading: the report “looks” right but totals are wrong — the join error is silent.
Lesson: check the join type (inner vs outer) and test on a known total.
Section vocabulary
- Data source
- What Crystal Reports connects to: relational databases, files (Excel, Access, CSV, XML), SAP sources.
Simple example: Farès connects to the company sales database.
What it is not: a folder where you type figures.
Reflex: name the exact source before any report.
- ODBC / OLE DB / native
- Connection modes: native (dedicated driver, often faster); ODBC/OLE DB (universal standards).
Simple example: Farès chooses his database native driver, faster than generic ODBC.
What it is not: three names for the same thing.
Reflex: native if available, otherwise ODBC/OLE DB.
- Connection wizard (Database Expert)
- Tool for choosing the connection type, providing parameters and selecting tables.
Simple example: Farès enters server, database and credentials there, then checks Clients and Orders.
What it is not: an automatic layout generator.
Reflex: minimum tables, checked parameters.
- Joins (links between tables)
- Relationships between tables on a common field; bad links produce wrong reports.
Simple example: Farès links Clients.ID = Commandes.ClientID and cross-checks the total.
What it is not: a simple alphabetical sort of tables.
Reflex: each table linked + total cross-checked.
- 32/64-bit architecture
- An ODBC source must match the software architecture; otherwise “source not found”.
Simple example: Farès creates his DSN in the right ODBC administrator (32-bit via SysWOW64).
What it is not: a version detail with no consequence.
Reflex: connection failure → check 32/64-bit first.
In practice — In practice — Connecting to a source
- Open the wizard (Database Expert) and choose the type, like Farès: database (native or ODBC/OLE DB) or file (Excel, CSV…).
- Provide the parameters (server, database, credentials; or path) and select the TABLES needed — the useful minimum.
- If several tables: define/check the JOINS (common field) via the Links wizard — critical step.
- On failure, check the driver and the 32/64-bit architecture; filter at the source for performance.
Click-by-click script — connect + link without wrong totals (5 min)
1) Database → Database Expert → Create a connection: choose native if your database is listed, otherwise ODBC/OLE DB. 2) Enter server + database (or Excel/CSV file path) → test: tables must appear. 3) Add ONLY Clients and Orders → Links tab: check the Clients.ID = Commandes.ClientID line. 4) Preview → note the total → compare with the known figure: if doubled, like Farès, recheck the join type before continuing. 5) File → Save; note the source (test or production) in the file name.
On “source not found”, first check the driver and the 32/64-bit architecture of the DSN.
Copy-paste template — my reliable connection sheet
Source: ___ (native / ODBC / OLE DB / Excel / CSV).
Server or path: ___ | Database: ___ | Account (read-only): ___.
Tables (useful minimum): ___ + ___.
Links: ___.ID = ___.ID | Type: [ ] inner / [ ] outer.
Check: Crystal total = ___ | known total = ___ | Variance: ___.
Production switch: Set Datasource Location test → production on ___.SQL Server database
Farès connects natively to SQL Server: server, database, read-only account — then Clients + Orders linked by ID.
Excel file
Department with no database: Farès points to a closed workbook (stable path), checks column headers as field names.
Connection failure
“Source not found”: Farès reads the exact message, checks driver + 32/64-bit DSN, then parameters and rights — in that order.
Test to production
Report finalised on the test database: Farès switches via Set Datasource Location to production, then cross-checks totals.
✓ Best practices
- Select only the tables needed (less = simpler and more reliable).
- Choose the right join type (inner vs outer) depending on what you want to show.
- Test your totals against a known figure to detect multiplication.
✗ Common pitfalls
- Leaving a table unlinked (cartesian product: rows explode).
- An inner join hiding records with no match.
- Hard-coding sensitive passwords.
Key takeaways
- Crystal Reports connects to databases (native or ODBC/OLE DB) and to files, via the wizard (Database Expert).
- CRITICAL point: JOINS. Several tables must be linked by a common field; bad links = wrong reports.
- Watch: credential security and above all the 32/64-bit architecture (no. 1 cause of “source not found”).
- Best practices: filter at the source (performance); change source later (“Set Datasource Location”).
48h challenge — apply tomorrow
Connect a real source like Farès (database or Excel), link two tables on their common field, then cross-check a total against a known figure — note the variance (zero targeted).
- I connected a source via the Database Expert and selected the minimum tables.
- My joins are checked on a common field, with the right type.
- My total is cross-checked against a known figure (variance explained or zero).
Frequently asked questions
Why are joins so important, and how to avoid wrong reports?
Because they determine how Crystal Reports combines several tables — and a wrong join silently produces wrong reports. Typical cases: duplicates (one-to-many inflating totals), missing rows (inner join hiding non-matching records → prefer an outer join if you want to keep everything), cartesian product (unlinked tables). To avoid: check each link on the right common field, choose the right type, beware one-to-many on totals, and test against a known figure.
What to do when the connection fails?
Failure almost always comes from a few causes, not the report. (1) 32/64-bit mismatch of the ODBC driver — THE no. 1 cause: create the DSN in the right administrator (32-bit = odbcad32.exe in SysWOW64). (2) Missing driver: install the database one, right architecture. (3) Wrong parameters (server, database, port, path). (4) Insufficient credentials/rights. (5) Network/firewall (remote database, VPN). Farès’s method: read the exact message, test the source outside Crystal Reports, check driver and architecture first, then parameters, rights and network.
Farès’s statement uses Clients + Orders. What is the most critical point?