Pivot Regions

A pivot table region groups a Business Central table into a cross-tab: the row field's values run down, an optional column field's values run across, and a value field is aggregated per intersection. It answers "X by Y" questions — sales by salesperson by month, quantity by item by location — read-only, with drill-down on every amount.

Setup

Field	Meaning
Range	Only the top-left cell places the region; the groups determine the extent.
Table No.	The table whose records the pivot summarizes. Changing the table clears the field picks.
Company	The company whose records the pivot summarizes. Blank reads the company you are working in; accepts `[Address]`, so a `?FIELD(Company, Name)` slicer re-pivots another company. Drill-down opens that company's records. See Reading Another Company.
Table View	Optional filters on the records, built with the view builder (AssistEdit): every field as a row, type-aware value lookups, cell picker. Accepts `[Address]` — a slicer cell then re-pivots the sheet.
Row Field	The field whose values become the rows. Type its name or pick it from the lookup.
Then Group By	Optional second row level: rows group by the row field, then by this field beneath it. The first level renders as bold subtotal rows.
Row / Column Date Bucket	How date and datetime fields are grouped — see below. Ignored on other field types.
Sort Rows by Total	Sorts row groups by their aggregated total, largest first — top-N reading. Off, rows sort by the group value: dates chronologically, numbers numerically, text alphabetically.
Column Field	Optional: the field whose values become the columns. Blank renders a single value column.
Value Field	The numeric (decimal/integer) field aggregated per intersection. Not needed for Count.
Aggregate	Sum, Count, Average, Min or Max.
Total Column	A Total column at the right (only with a column field — without one, the single value column already is the total).
Total Row	A grand-total row at the bottom.
Max Rows	The most row groups the pivot may yield (default 1000). Beyond it the region reports an error instead of truncating — a truncated pivot would show wrong totals.

The single value column's header reads like "Sum of Amount" / "Average of Quantity" (plain "Count" when nothing is aggregated). Records whose group value is empty appear under (blank); empty intersections stay truly empty cells, the Excel way.

Date Buckets

Calendar buckets group into contiguous ranges: Exact Date, Day, Week (W22 2026), Month (Jan 2026), Quarter (Q1 2026), Year.

A bucketed time axis is continuous: the pivot shows every bucket between the oldest and the newest data, not only the buckets that hold records. Sales in January, June and November yield eleven month columns (or rows), the empty ones truly empty. Exact Date is the exception — it lists just the dates that occur. When a second row field is a bucketed date, every first-level group gets the same range of buckets, so the groups line up row for row.

Sub-unit buckets group across the calendar — all Januaries together, all Mondays together:

Month of Year (Jan..Dec), Quarter of Year (Q1..Q4), Week of Year (1..53), Day of Week (Mon..Sun), Day of Month (1..31), Hour of Day (datetime fields; on a plain date field it falls back to the exact date).

Sub-unit axes are continuous too: data in Q1 and Q4 shows Q1 through Q4.

Because a sub-unit group is not a contiguous date range, sub-unit cells carry no drill-down — a filter cannot express "all Januaries", and opening a wider set than the number on screen would mislead.

Drill-Down

Ctrl+click an amount to open the source table's list page filtered to exactly the records behind it: the region's table view plus the cell's row/column group filters. Subtotals, the total column, the grand-total row — every level drills to its own scope.

Caching and Refresh

Scanning a large table is slow, so the computed pivot is cached. It recomputes when any setting changes — including a slicer moving a [Cell] referenced in the table view — and on the region's refresh button. Ordinary recalculations reuse the cached result (drill-down stays armed either way).

Limits

At most 200 columns from the column field — filter the source or bucket dates coarser.
At most Max Rows row groups — the region errors rather than truncates.

Example

Sales amount by salesperson, by month, top sellers first:

Table: Value Entry; Table View: WHERE(Item Ledger Entry Type=FILTER(Sale),Posting Date=FILTER([C2])) with C2 a ?RANGE slicer.
Row Field: Salespers./Purch. Code; Column Field: Posting Date, Column Date Bucket Month.
Value Field: Sales Amount (Actual); Aggregate Sum; Sort Rows by Total on.