Workbooks
Series, queries and report data
Bring more Business Central data into a sheet with Bitta Sheets: date and FlowField series, a query result, and a report dataset with its request page.
updated: applies-to: 1.0.0.0
A series fills a row or column, one value per cell. Queries and reports bring their own tables. All four are on the Insert tab, and their settings take cell references, so a sheet can drive them.
Date and FlowField series
Date series
- On the Insert tab, choose Date series.
- Set the Start, for example 2025-01-01, the Step, for example Month, and the Number of dates, for example 12.
- Under Each cell holds, choose a date filter, and set the Direction, for example across, one per column. Add period captions puts the month names above.
- Name the block, pick the top-left cell, and choose Insert.
Each cell holds a date filter for its month, with the period captions above.
FlowField series
A FlowField series reads a FlowField for each period.
- On the Insert tab, choose FlowField series. Choose the table, for example Customer, and the field, for example Sales ($). Aggregate stays on Sum.
- Add the filters. Here the No. filter is [$B9], the customer number in B9, and the Date Filter is [C6], the first month of the date series.
- Set the Number of values, for example 12 across, and a Number format.
- Choose Insert.
A token such as [C6] moves one cell along the series, so each value reads its own month. A $ pins a token, so [$B9] stays on the customer. Any live data setting takes cell tokens, and the Cell button picks the cell for you.
Double-click a value to open what's behind it, here the customer. The gear on the block reopens its settings, and More settings opens the full Series Setup.
A query
- On the Insert tab, choose Query, and choose the query, for example 1311, Top 10 Customer Sales.
- Columns picks the query's columns, and Filters on the result narrows the rows. Leave them empty for every column and row.
- Set Max Rows, for example 5, and choose Insert.
The five customers with the most sales come in under the query's column headers.
Report data
Report data runs a report and lists its dataset.
- On the Insert tab, choose Report data, and choose the report, for example 4409, Customer - Top List (Excel).
- Choose columns lists the report's columns by name. Pick the ones you need, for example CustomerNo, CustomerName and AmountLCY.
- Filters & Options opens the report's own request page. Here, customers 10000..50000.
- Filters on the result narrows the rows the sheet shows.
More settings opens the Query or Report Setup page. Once you've chosen columns, Load Columns adds the report's other columns unticked, so only your choice is used. Sort by Column and Descending sort the rows, for example by the amount, largest first.
Choose Insert, and the report runs once. Report results are kept for an hour, and query results for ten minutes.
When to use it
Use a series when every period needs its own number, and a query or report when the data already has the shape you need. For tables you filter and edit, see Workbooks and live data.