Skip to content
Bitta Apps

Chapter 2 of 4Page 10 of 12

Workbooks

BC functions and filters

Build a Business Central month-end pack in Bitta Sheets with live BC functions, one month filter, a data table that follows it, and a refresh.

updated: applies-to: 1.0.0.0

Bitta Sheets adds Business Central functions to the sheet. One filter picks the month, and every formula and table follows it, so a month-end pack updates itself.

One filter for the month

  1. On the Business Central tab, choose Add filter.
  2. Set the Caption, for example Month, and the Kind: a list of values, a date, a period, a dimension or a record.
  3. Type a Default value, for example the November dates, and a Linked cell, for example B2.
  4. Choose Save. The Month filter appears in the filter bar, and B2 holds the period, ready for formulas.
Add a filter dialog with the caption Month, the kind Period, the November dates as the default value and the linked cell B2

Filters this live data lists the data tables and other live data the filter applies to. Without live data, the filter still drives formulas through its linked cell, or with BC.SLICER.

Business Central functions

  1. Select a cell, and on the Formulas tab choose BC function. It's also on the right-click menu, as Insert BC function.
  2. Pick the function, for example BC.GL, the total of G/L entries for an account filter, a period and dimension filters.
  3. Fill in the arguments. Pick opens a list of accounts, tables, fields or dimensions, or you type a filter, for example the revenue accounts 40000..49999. For the period, type a cell, for example B2.
  4. The Formula preview builds as you go. Choose Insert.
Insert BC function dialog for BC.GL with the account picker open on the list of G/L accounts
Insert BC function dialog for BC.GL with the revenue accounts 40000..49999, the period from cell B2 and the formula preview

While Business Central answers, the cell shows #GETTING_DATA. G/L entries keep their sign, so revenue gets a minus: =-BC.GL("40000..49999", B2) gives 92,745.00 for November. Cost of sales is the same function on the cost accounts, and the margin is a plain sheet formula.

The other functions work the same way:

  • BC.BUDGET reads a G/L budget, here next year's revenue budget, FY2027.
  • BC.BALANCE returns the balance of an account filter at a date.
  • BC.SUM adds up a field, for example open receivables, and BC.COUNT counts records, for example open sales orders.
  • BC.LOOKUP finds a value, for example an account name, and BC.FIELD reads a field of a record, for example a balance.
  • BC.COMPANY returns the company.
  • BC.SLICER reads the value of a filter in the filter bar.
Close pack for November with revenue, cost of sales and gross margin, and a BC.BUDGET formula for the FY2027 revenue budget

A data table that follows the filter

  1. Select a cell, and on the Insert tab choose Data table.
  2. Pick the table, for example G/L Entry, and the columns: posting date, account, description and amount.
  3. Under Filters, filter it to the revenue account. For Posting Date, take the value from a filter in the filter bar: Month.
  4. Choose Insert. The table lists November's revenue entries.
Insert data table dialog for G/L Entry with four columns, a G/L account filter and Posting Date taken from the Month filter

Next month, refresh

When the month changes, open the Month filter in the filter bar. Pick a preset, from This month to Last 12 months, or type the dates, and choose Apply. Every formula and the table follow: December revenue is 96,215.00.

Month filter open with the presets This month to Last 12 months and the From and To dates
Close pack for December with revenue 96,215.00 and the G/L entries table following the Month filter

For a list of values, the filter also searches the values and offers Look up in Business Central. Edit filter opens the filter builder, with conditions, + Or and the period presets.

Workbook filters

Workbook filters apply to every data table and connected table at once, by a field of a table or by a dimension, with a default value and a linked cell.

Add a workbook filter dialog that applies to a field of a table or a dimension, with a default value and a linked cell

Refresh

  • Refresh all, or F9, reads Business Central again. It's on the Business Central tab, and on the Home and Data tabs.
  • Calculate Now, on the Formulas tab, recalculates the sheet.
  • The Live data list in the filter bar shows every live data block in the workbook.

When to use it

Use it for month-end packs, management reports, and any number you rebuild every month. For the full financial report, see Financial reports and budgets.

Next step

Ready to try Bitta Sheets?