Add a Pivot Table with Live Data in your Spreadsheet inside Business Central (Advanced Spreadsheets)
Pivot tables with live data in Business Central spreadsheets, using the advanced spreadsheets and reporting app. The demo builds a pivot table on item ledger entries, shows refresh on open, drilldown to source records, and changing date buckets to quarters.
- Introduction to pivot tables with live data
- Creating a new pivot table region
- Configuring data source and pivot table fields
- Completing the pivot table and viewing results
- Interacting with live data and drilldown capability
- Modifying pivot table structure and data reusability
- Conclusion and sandbox trial
Overview
Erik Hougaard shows in about three minutes how to add a pivot table region to a spreadsheet in Business Central with the advanced spreadsheets and reporting app. He picks item ledger entries as the data source, adds a filter, and sets row and column fields and the values to aggregate.
Once built, the pivot table shows live data. It refreshes whenever the spreadsheet is opened and can be refreshed by hand. Control-clicking a cell opens the item ledger entries behind the number. The structure can be changed, for example by switching date buckets to quarters, and the result stays spreadsheet data that can feed other calculations. A link to try it in a cloud sandbox is given.
Key points
- The demo adds a pivot table with live data using the advanced spreadsheets and reporting app for Business Central.
- A new pivot table region is created in the spreadsheet, then a data source (item ledger entries in the demo) is chosen and can be filtered.
- Row fields, column fields and the summed value field (quantity in the demo) are configured in the pivot table setup.
- Date fields can be bucketed, for example changed to quarters, after the pivot table is built.
- The data refreshes whenever the spreadsheet is opened and can also be refreshed manually.
- Control-click a pivot cell to drill down to the underlying item ledger entries.
- Pivot table output stays spreadsheet data, so it can serve as input for other calculations.
Features
Every feature below: status not stated, demoed.
| Feature | At |
|---|---|
| Pivot tables with live data in spreadsheets | 0:00 |
| Pivot table field configuration | 0:37 |
| Drilldown from pivot table to source data | 1:42 |
| Spreadsheet-based pivot table data reusability | 1:56 |
AL objects mentioned
As heard in the captions. A name that matches one object page by exact type and name links to it; the others stay as named.
- table “item ledger entries” at 0:37
Not found in BC28-30: table “item ledger entries”.
Quotes
- 0:00 “How to add a pivot table with live data in your spreadsheet using the advanced spreadsheets and reporting app for Business Central?”
- 0:37 “With live data. Let’s go for item ledger entries. And maybe let’s add a filter here.”
- 1:29 “And this is live data. So if if you hit the the refresh it will be refreshed. Whenever you open it, it’s refreshed.”
- 1:42 “I control click and I get the item ledger entries. That’d be the thousand to take we worked on.”
- 2:10 “We can go in and and we can say, “Okay, hang on. Let’s change the buckets to be quarters instead.” And now we have”
Disclaimers in the video
- 2:32 other: What you can do is click the link below and you can try this out for free in a cloud sandbox.
Presenters (as heard): Eric.