Workbooks
Pivot tables on live data
Summarize any Business Central table in a pivot table: rows, columns and values with totals, quarters, top N with Others, and a drill-down on every number.
updated: applies-to: 1.0.0.0
A pivot table sums up any Business Central table by rows, columns and values, straight from posted data. Every number opens the records behind it.
Insert a pivot table
- In a workbook, open the Insert tab and choose Pivot table.
- Pick the source table, for example posted sales invoice lines, and filter it: last year's posting dates, the customers you want, item lines only. You can also choose the Company to read from.
- Set the layout: up to three fields down the Rows, up to two across the Columns, and up to six Values. Group a date by quarter, month, year or another period.
- Choose Insert. The pivot adds it all up, with totals.
Values, totals and ranking
- Each value has an aggregate: Sum, Count, Average, Minimum, Maximum or Distinct count.
- Show as turns a value into a percent of the row, the column or the total, a running total, or the difference from the previous one. Add the same field twice to show the amount and its percent of the total side by side.
- Turn Subtotals, the Grand total row and the Grand total column on or off.
- Sort the rows by their labels or their totals, show only the top N, and add the rest as Others, so the total still adds up to one hundred percent.
Dimensions and G/L entries
A pivot on the general ledger can use dimensions as rows, for example the customer group, next to the G/L accounts and amounts. More settings opens the full Pivot Setup page in Business Central.
Read only, cached, and drill-down
Pivot tables are read only and cached: they show the totals as of the last refresh. To see what's behind a number, double-click it, and Business Central opens the records it adds up. Subtotals and totals drill down too.
When to use it
Use a pivot table when a question needs totals by product, customer or period, straight from posted data. To show the numbers at a glance, add charts and KPI cards.