Skip to content
Bitta Apps

Chapter 2 of 4Page 11 of 12

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

  1. On the Insert tab, choose Date series.
  2. Set the Start, for example 2025-01-01, the Step, for example Month, and the Number of dates, for example 12.
  3. 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.
  4. Name the block, pick the top-left cell, and choose Insert.
Insert date series dialog starting 2025-01-01, one month apart, 12 dates, each cell a date filter, across with period captions

Each cell holds a date filter for its month, with the period captions above.

Date series across a row: month captions above, and a date filter for each month of 2025 below

FlowField series

A FlowField series reads a FlowField for each period.

  1. On the Insert tab, choose FlowField series. Choose the table, for example Customer, and the field, for example Sales ($). Aggregate stays on Sum.
  2. 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.
  3. Set the Number of values, for example 12 across, and a Number format.
  4. Choose Insert.
Insert FlowField series dialog for the customer Sales field with the No. filter pinned to cell B9, the Date Filter on cell C6, 12 values across

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.

One customer's sales month by month in a FlowField series under the date series

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

  1. On the Insert tab, choose Query, and choose the query, for example 1311, Top 10 Customer Sales.
  2. Columns picks the query's columns, and Filters on the result narrows the rows. Leave them empty for every column and row.
  3. Set Max Rows, for example 5, and choose Insert.
Insert query dialog with query 1311, Columns and Filters on the result left empty, and Max Rows 5

The five customers with the most sales come in under the query's column headers.

Query result with the five customers with the most sales under the query's column headers

Report data

Report data runs a report and lists its dataset.

  1. On the Insert tab, choose Report data, and choose the report, for example 4409, Customer - Top List (Excel).
  2. Choose columns lists the report's columns by name. Pick the ones you need, for example CustomerNo, CustomerName and AmountLCY.
  3. Filters & Options opens the report's own request page. Here, customers 10000..50000.
  4. Filters on the result narrows the rows the sheet shows.
Insert report data dialog for report 4409 with Filters and Options and the columns CustomerNo, CustomerName and AmountLCY
Request page of Customer - Top List with the Customer No. filter 10000..50000

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.

Query or Report Setup for report 4409 with Load Columns listing four columns and Amount2LCY unticked

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.

Next step

Ready to try Bitta Sheets?