Bitta Sheets · Part 12 · 2:19 · YouTube
Business Central Pivot Tables on Live Data
What this video covers
Summarize any Business Central table in a pivot table inside your workbook: rows, columns, values and totals, straight from posted data.
Group dates by quarter, rank the top customers with the rest as Others, show values as a percent of the total, and use dimensions as rows.
Transcript
Which product lines sold best last year, and in which quarter? A pivot table on live sales answers it in a minute. Here's how to build pivot tables on live Business Central data. A pivot table sums up any Business Central table by rows,
columns and values. It reads the data, and every number opens the records behind it. In a workbook, open the Insert tab and choose Pivot table. Pick the table: posted sales invoice lines.
Then filter it to last year's posting dates, the customers you want, and item lines. Now the layout. Item category down the rows, posting date across,
grouped by quarter, and the amount as the value. Insert, and the pivot adds it all up: every product line, quarter by quarter, with totals. A second pivot ranks customers, straight from the customer ledger entries.
Add the sales amount twice: once as a value, and once as a percent of the total. Sort the rows by their totals, show only the top three, and add the rest as Others.
Two customers bring in about two thirds of the year. The rest stay in Others, so the total still adds up to one hundred percent. A third pivot reads the general ledger: the credit amounts on the income accounts. Dimensions work as rows, too.
Here, the customer group. More settings opens the full Pivot Setup page in Business Central. Small customers bring in the most. Pivot tables are read only and cached.
To see what's behind a number, double-click it. Business Central opens the records it adds up. Totals drill down too. From there, you can check every invoice line behind the total.
Use a pivot table when a question needs totals by product, customer or period, straight from posted data. Next, turn these numbers into charts and KPI cards.
More Bitta Sheets videos
All videos
1:18OverviewEdit Business Central Tables Live in a Spreadsheet
2:08Part 1Edit Business Central Data in an Excel-Style Sheet
3:08Part 2Business Central Bulk Update Without Export and Import
2:50Part 3Business Central Budget in a Spreadsheet, Written Back Live
2:35Part 4Business Central Budget Entry by Department
2:56Part 5Business Central Consolidation: Companies and Account Mapping