4.3Cross-Tabs
The essentials in 30 seconds
- The cross-tab crosses two dimensions: rows (one dimension), columns (another) and a summarized value at the intersection — plus row, column and grand totals.
- Column cardinality decides readability: a few-valued dimension in columns (months, quarters), the many-valued dimension in rows — otherwise the table explodes in width.
- Farès's reflex: build via the Cross-Tab Expert (rows, columns, summarized field), place in the report footer for the global summary — and color with conditional formatting.
Threaded mini-case — Farès and the product × month sales grid
Farès, a controller in Algiers, must show how each product sells month by month: a nested grouped report would produce pages of unreadable lists. He inserts a cross-tab via the Cross-Tab Expert — rows: Product (many-valued dimension), columns: Month (12 values), value: total sales — placed in the report footer, with automatic totals and conditional formatting that colors the best cells. Third milestone of the thread: thousands of rows condensed into a grid that management reads in one minute.
Continued from cases 4.1-4.2 — Farès moves from visuals to two-dimensional analysis.
1. Two dimensions in one grid
The cross-tab summarizes data along two dimensions at once — the equivalent of a pivot table. Rows (one dimension), columns (another) and, at the intersection, a summarized value. Like Farès's grid: sales by product (rows) and by month (columns), a total at each intersection, plus row, column and grand totals.
In one grid, the cross breakdown of thousands of rows: ideal for comparing and analyzing two-way distributions (sales by region and quarter, headcount by department and status). The cross-tab does NOT show detail — only summarized values, very compact.
2. Rows, columns, value: the Cross-Tab Expert
The Cross-Tab Expert defines three elements: rows — the field whose values become rows; columns — the one whose values become columns; the summarized field and its operation — the value at the intersection (Sum, Count). Crystal Reports generates the grid and the totals automatically.
You can have SEVERAL fields in rows or columns (sub-levels), several summarized values, and format cells (numbers, alignment) or color them with conditional formatting. The cross-tab goes in a report (global cross-tab) or group header or footer (one cross-tab per group).
3. Column cardinality: the rule that saves you
Crucial point: the columns field creates ONE column per distinct value. With many values (hundreds of customers), the table becomes huge in width — unreadable and printable nowhere. Farès's rule: a few-valued dimension in columns (months, quarters, categories, statuses), the many-valued dimension in rows (rows paginate vertically).
Remedies if it overflows: swap rows and columns, group at a coarser level (quarter instead of day, category instead of product), filter or Top N (see 3-5), format numbers and adjust widths. Like any summary, the cross-tab depends on correct data (joins!).
🔎 Concrete example — 🔎 Concrete example — Product × month sales
Farès's grid: the right distribution of axes.
Rows: Product (many).
Columns: Month (12 values).
Value: Sum(Ventes).
→ Grid + row/column/grand totals.Reading: many-valued rows, tight columns, summarized value, automatic totals.
Lesson: a two-way analysis, dense and telling, without a single detail row.
Section vocabulary
- Cross-Tab
- Grid summarizing along TWO dimensions (rows × columns) with a VALUE at the intersection and automatic totals. The equivalent of a pivot table.
Simple example: Farès reads sales by product and by month in a single grid.
What it is not: a long hierarchical list with detail.
Reflex: two dimensions to compare at a glance: think cross-tab.
- Rows / Columns / Value
- The three elements: ROWS field, COLUMNS field, SUMMARIZED FIELD at the intersection with its operation (Sum, Count).
Simple example: Product in rows, Month in columns, total sales at the intersection for Farès.
What it is not: three raw fields displayed side by side.
Reflex: new cross-tab: name the three elements before clicking.
- Column cardinality
- The COLUMNS field must have FEW distinct values; too many values make the table very wide and unreadable.
Simple example: 12 months in columns for Farès: clean; 300 customers there would be unreadable.
What it is not: a mere aesthetic choice.
Reflex: columns exploding: swap or group coarser.
- Cross-tab placement
- Report HEADER or footer (global) or GROUP header/footer (one cross-tab per group).
Simple example: Farès puts his global summary in the report footer.
What it is not: the Details section row by row.
Reflex: global or per-category summary: place accordingly.
In practice — In practice — Create a cross-tab
- Insert a cross-tab (Cross-Tab Expert): choose the ROWS field (e.g. product), the COLUMNS field (e.g. month — few values) and the SUMMARIZED FIELD (Sum of sales).
- Let Crystal Reports generate the grid with automatic totals (per row, per column, grand).
- Place the cross-tab in the Report FOOTER (global) or GROUP footer (one per group); format the cells (numbers, alignment).
- Watch cardinality: few values in columns; optionally apply conditional formatting (color by value).
Ready-to-say script — explain the grid in a meeting (30 s)
“This is Farès: each row is a product, each column a month, and the cell gives the total — with totals per product, per month and the grand total. Colored cells flag the best sales: you see seasonality without opening the detail.”
Say when presenting the grid: walk through row, column, intersection, then totals.
Copy-paste template — cross-tab scoping sheet
Question: [which product sells in which month?]
Rows: [Product — many-valued dimension]
Columns: [Month — 12 values max]
Value: [Sum(Ventes)] — Totals: [row + column + grand]
Placement: [report footer — global]
Finish: [FR number format, right alignment, heat map yes/no]Product × month sales
Like Farès: many-valued rows, 12 columns, automatic totals — seasonality by product in one grid.
Exploding columns
Swap rows and columns or group coarser (quarter, category) to recover a readable grid.
Summary + detail combined
Grouped report for detail AND summary cross-tab in the report footer: both levels in a single report.
Grid that speaks
Conditional formatting as a heat map: high and low values jump out.
✓ Best practices
- Few-valued dimension in COLUMNS, many-valued dimension in ROWS.
- Group at a coarser level (quarter, category) if needed.
- Conditional formatting (heat map) to highlight what matters.
✗ Common pitfalls
- A high-cardinality field in columns (table too wide).
- Too many rows AND columns (consider Top N, filtering).
- Unformatted cells (unreadable numbers).
Key takeaways
- The CROSS-TAB summarizes along TWO dimensions: ROWS × COLUMNS, with a VALUE at the intersection and automatic TOTALS.
- Define ROWS, COLUMNS and the SUMMARIZED FIELD (Sum, Count). Crystal Reports generates the grid and the totals.
- Watch CARDINALITY: few values in COLUMNS; the many-valued dimension goes in ROWS.
- Placed in a Report (global) header/footer or GROUP header/footer; combines with conditional formatting.
48h challenge — apply tomorrow
On real sales: create a product × month cross-tab (12 columns max), format the numbers, add a heat map and check the totals against the grouped report.
- I can create a cross-tab (rows, columns, value) via the Cross-Tab Expert.
- I can distribute dimensions by their cardinality.
- I can place, format and color a summary cross-tab.
Frequently asked questions
When should I use a cross-tab instead of a classic grouped report?
The GROUPED report organizes along ONE dimension (possibly nested) showing detail — a vertical hierarchical structure, useful when you want to see rows. The CROSS-TAB summarizes along TWO dimensions simultaneously in a compact grid, WITHOUT detail — useful for comparing a measure along two axes (product × month). Choose: two dimensions to cross or a compact two-way summary → cross-tab; one dimension with detail → grouped. Both complement each other: grouped detail + summary cross-tab in the same report.
My cross-tab is too wide or unreadable: how do I make it usable?
Cause #1: too many COLUMNS — each distinct value of the columns field creates a column. Put a FEW-valued dimension in columns (months, quarters, categories) and the many-valued one in rows; otherwise swap or group coarser (quarter instead of day). Other remedies: Top N or filtering if too many rows, number format and adjusted widths, conditional formatting as a heat map to spot what matters, landscape orientation when printing. Keep the cross-tab SYNTHETIC.
Which dimension goes in cross-tab columns?