Skip to content
Bitta Apps

Bitta Sheets · Part 10 · 3:03 · YouTube

Business Central Month-End Reporting with Live Formulas and Filters

What this video covers

Build a Business Central month-end close pack that updates itself: live BC functions, one month filter, refresh.

Add a Month filter linked to a cell, insert BC.GL with the Insert BC function dialog, read budgets, balances and counts, add a G/L entries table that takes its period from the filter bar, then switch to the next month and refresh.

Transcript

It's month-end again. The close pack should update itself for the new month. Here's a close pack that reads Business Central live, for any month you pick.

Bitta Sheets adds Business Central functions to the sheet. One filter picks the month, and every formula and table follows it. Start with the month. On the Business Central tab, choose Add filter.

Call it Month, make it a period, type the November dates as the default, and link it to cell B2. The filter appears in the filter bar, and B2 holds the period, ready for formulas.

Select B4. On the Formulas tab, choose BC function. Choose BC.GL, the total of G/L entries. Pick an account from the list, or type a filter:

the revenue accounts. For the period, type B2. The formula builds as you go. Insert, and the cell shows getting data while Business Central answers.

G/L entries keep their sign, so revenue gets a minus. Cost of sales is the same function on the cost accounts, and the margin is a plain sheet formula. BC.BUDGET reads a budget: here, next year's revenue budget,

FY2027. BC.SUM adds up open receivables, BC.COUNT counts open sales orders, BC.LOOKUP finds an account name, and BC.FIELD reads a balance. BC.COMPANY returns the company.

BC function is also on the right-click menu. Select E3, and on the Insert tab, choose Data table. Pick G/L Entry, then four columns: posting date, account, description and amount.

Filter it to the revenue account. For Posting Date, take the value from a filter in the filter bar: Month. Insert, and the table lists November's revenue entries.

When the month changes, open the Month filter. Pick a preset like This month or Last 12 months, or type the dates: December. Every formula and the table follow: December revenue is ninety six thousand

two hundred fifteen. Workbook filters apply to every data table at once, by a table field or by a dimension. Refresh all, or press F9, and the sheet reads Business Central again.

Calculate Now recalculates the sheet. The live data list shows every block in the workbook. Use it for month-end packs, management reports, and any number you rebuild every month.

Next: get started, and import from Excel.

More Bitta Sheets videos

All videos

Next step

See Bitta Sheets on your own data