Skip to content
Bitta Apps

Chapter 2 of 4Page 5 of 12

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

  1. In a workbook, open the Insert tab and choose Pivot table.
  2. 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.
  3. 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.
  4. Choose Insert. The pivot adds it all up, with totals.
Insert pivot table dialog with Posting Date grouped by quarter as columns, Sum of Amount as the value, and totals and sorting options

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.
Two pivot tables: sales by product line per quarter with totals, and the top three customers with Others and the percent of total

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.

Pivot Setup page in Business Central for a G/L entry pivot with the customer group dimension as rows

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.

Posted Sales Invoice Lines opened by double-clicking a pivot value, listing the lines behind the number

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.

Next step

Ready to try Bitta Sheets?