Bitta Sheets · Part 23 · 2:30 · YouTube
Business Central Queries, Report Data and Date Series in a Sheet
What this video covers
Bring more kinds of live Business Central data into a sheet with Bitta Sheets.
Build a row of monthly date filters with a date series, read a FlowField for each month with a FlowField series, and drill from any value to the record behind it.
Then insert a query's result and a report's dataset: choose the columns, set the report's request page options, and sort the rows.
Transcript
Marcus wants one customer's sales by month, and the top customers, in one sheet. Here's how to bring series, queries and report data into a sheet. A series fills a row or column, one value per cell.
Queries and reports bring their own tables. Date series makes a row of dates. Start on January 1, 2025, one month apart, twelve of them.
Each cell holds a date filter, across the row, with the period captions above. A FlowField series reads a FlowField for each period. Choose the Customer table and Sales.
Then the filters. The No. filter points at B9, with a dollar sign so it stays put. Date Filter points at C6, and moves one cell along the series.
Aggregate stays on Sum. Ask for twelve values across, and set a number format. One customer's sales, month by month. Double-click a value to open what's behind it,
here the customer. The gear reopens its settings, and More settings opens the full Series Setup. Query runs a Business Central query.
Choose 1311, Top 10 Customer Sales, and set Max Rows to five. The five customers with the most sales, under the query's column headers. Report data runs a report and lists its dataset. Choose Customer - Top List.
Choose columns picks the report's columns by name. Filters & Options opens the report's own request page. Here, customers 10000 to 50000. More settings opens the setup page.
Load Columns lists the report's other columns. They come in unticked, so only your choice is used. Then sort by the amount, largest first. Insert runs the report.
Report results are kept for an hour, and query results for ten minutes. Use a series when every period needs its own number, and a query or report when the data already has the shape you need. Next, setting up Sheets: permissions and the role center.
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