Advanced Spreadsheets and Reporting Documentation
Index

Spreadsheets Overview

Advanced Spreadsheets and Reporting includes a full spreadsheet editor that runs inside Business Central. It combines familiar spreadsheet mechanics — cells, formulas, formatting, a fill handle, copy and paste — with live Business Central data: areas of the sheet (called regions) are filled directly from tables, queries, reports, financial reports, budgets, SQL statements, pivots, date ranges and FlowField aggregates on every recalculation.

What Makes It Different from Excel

In Excel you export data and work on a snapshot. Here the data stays in Business Central:

  • Regions bind rectangles of the sheet to live data sources. A table region re-queries its table on every recalculation, so the numbers are never stale.
  • Formulas are AL expressions, not Excel functions. Anything the AL language can evaluate works in a cell — plus the familiar spreadsheet helpers SUM, AVERAGE, COUNT, PRODUCT and XLOOKUP.
  • Drill-down: Ctrl+click a region cell to open the record or the ledger entries behind the number.
  • Write-back: table regions and budget matrix regions can be made editable, validating your input through the same business logic as any Business Central page.
  • Slicers turn cells into interactive filters — one pick recalculates the whole sheet.

The editor includes a built-in Cheatsheet for Excel Users (Help menu) that maps Excel habits to their equivalents here.

Key Concepts

Concept Description
Spreadsheet document A saved sheet, identified by a code (up to 20 characters) and a description. Stored entirely in Business Central.
Cell Holds a value, a formula (starting with =), or a slicer (starting with ?). Cell content can be up to 2,048 characters.
Region A rectangle managed by the engine and filled from a data source, or rendered as a chart, KPI card or page. Thirteen region types are available.
Slicer An input cell with a picker: a fixed list, a record picked on a table's lookup page, dimension values, a date, or a date range.
Page region Marks an area that becomes a tab in preview mode and prints as its own sheet.
Scenario A named copy of the sheet's cell contents — for budget versions like Base, Stretch, Worst Case.
Template A reusable rectangle of a sheet — cells, styles, regions, conditional formats and sizes — applied anywhere on any sheet.
Init code AL procedures defined per sheet, callable from any formula.

The Grid

  • Columns run from A to ZZ (702 columns); rows run to 1,048,576.
  • Default column width is 96 pixels (resizable between 36 and 480); default row height is 26 pixels (resizable between 16 and 300).
  • Only cells that actually hold content or styling are stored, so sheets stay small.

Region Types at a Glance

Type Fills the region with Details
Table Records from any Business Central table Table Regions
Chart A chart drawn from cell ranges Chart & KPI Regions
Financial Report A financial report (account schedule) run live Financial Report Regions
Page Nothing — marks a preview tab / printed page Preview & Printing
KPI Card A KPI card rendered from cells Chart & KPI Regions
Budget An editable G/L budget matrix with write-back Budget Matrix Regions
Query A query object's result set Query, Report & SQL Regions
Report A report's XML output Query, Report & SQL Regions
SQL A SELECT statement's result set Query, Report & SQL Regions
Pivot Table A table grouped and aggregated into a cross-tab Pivot Regions
Date Range A run of dates from a start date and an interval — single dates, start/end pairs or filter texts Date Range Regions
FlowField One table field calculated once per cell, its [Cell] filters shifting per step FlowField Regions

How to Open the Spreadsheet Editor

There are several entry points:

  1. Tell Me: search for Spreadsheet (the editor) or Spreadsheets (the document list). Search terms like excel, sheet, cells, formula and calc also find it.
  2. The Spreadsheets list page shows every sheet you have access to, with actions to create a new blank sheet, edit, preview, manage permissions and refresh the list.
  3. Financial Reports page: the Open as Spreadsheet action runs the selected financial report and opens it as a ready-made interactive sheet — a title, a period slicer and a slicer per report dimension, all wired to the Financial Report region below them.
  4. G/L Budget Names page: Open as Spreadsheet opens the budget as an editable account-by-period matrix under a title, a period slicer and a slicer per global/budget dimension, all wired to the Budget region below them.
  5. The Advanced Spreadsheets and Reporting menu includes a Spreadsheets entry that opens the document list.

Your First Sheet in Five Minutes

A taste of how the pieces fit — a mini receivables view:

  1. Open Spreadsheets and choose New Blank Sheet.
  2. Type Open customer balances in A1 and make it bold.
  3. Click Regions → New Region: ID CUST, type Table, range A3, table Customer, fields No., Name, Balance (LCY), table view WHERE(Balance (LCY)=FILTER(>0)).
  4. In E1 type a label Total: and in F1 the formula =CUST.TableSum("Balance (LCY)") — the total over every filtered customer, not just the rows on screen.
  5. Ctrl+click any customer row — their card opens.
  6. Save as RECEIVABLES. Done: a live list that is current every time anyone opens it.

How a Sheet Calculates

On every recalculation the engine works through the sheet in a fixed order:

  1. Slicer cells are evaluated first, so their chosen values exist before anything else runs.
  2. Regions are filled — date ranges first (so other regions can point at their cells), then tables, financial reports, budgets, pages, queries, reports, SQL, pivots and FlowFields. Their values become available to formulas.
  3. The GL record global is registered, so =GL.GLSum(...) works on any sheet.
  4. Literal cells are registered (user content in a cell overrides region data at the same address).
  5. Formulas are evaluated exactly once each, in dependency order — a cell is computed only after the cells it references. Circular references are broken rather than looping.
  6. Conditional formats are evaluated against the final values.
  7. Chart and KPI regions render last, over the fully evaluated sheet.

Recalculation happens automatically after every edit, and on demand with Sheet → Recalculate in the toolbar (or F9).

Requirements and Permissions

All spreadsheet objects are covered by the Advanced Spreadsheets and Reporting permission set (AFR_Hgd) that ships with the extension. Access to individual sheets is controlled per document — see Documents & Sharing.