Query, Report & SQL Regions

Three region types fill the sheet from result sets rather than directly from a table: a query object, a report's XML output, or a SQL SELECT statement. All three render read-only cells — a header row of column names, then one row per result row — with paging via Max Rows.

Common Properties

Field	Meaning
Range	Only the top-left cell places the region; width comes from the columns, height from the rows.
Max Rows	Result rows per page (default 1000).
Include Headers	Whether the first row shows the column names.

Rendered values become ordinary cells: numeric results register as numbers formulas can reference; text stays text. Unlike table regions, these regions expose no record global — XLOOKUP over them scans the rendered cells like any plain range.

Table regions, field slicers and the agent's direct table reads reject the app's spreadsheet cell, region, region-field, virtual-cell, conditional-format, scenario, scenario-cell and permission tables. These paths report "The spreadsheet app's own tables cannot be used as a data source." Document metadata, template and skill tables are outside this protected list. This is a restriction on those access paths, not a blanket guarantee for every query, report, SQL statement or other region type. Business Central table permissions still apply.

Query Regions

The region runs a Business Central query object and lays out its result set. The result is cached — SaveAsJson fetches the whole set once; press the region's refresh button (or pick a different query) to re-run.

Pick the query by number; the name shows alongside.
Query.SaveAsJson takes no external filters, so filters and sorting belong in the query object itself.
The query runs in the company you are working in. Query regions have no Company setting — the platform runs a query object in the session's company only; a SQL region reads other companies.
Column headers come from the query's column names (underscores read as spaces).
Typing is taken from the data: numbers become numeric cells, booleans render Yes/No, and everything quoted — text, codes, dates — stays text.

Example: your developer (or an AppSource app) ships a query that joins salespeople with their year-to-date invoiced amounts. Point a query region at it and the joined, aggregated result lands on the sheet — re-run on the badge's refresh button — where you can chart it, style it and compute on it. When you need the same result but filterable from the sheet, rebuild it as a SQL region (below) instead, since queries cannot take [Cell] filters.

Report Regions

The region runs a Business Central report and parses its XML output into rows and columns.

Pick the report by number. Filters & Options opens the report's own request page; the values you set there are stored with the region and applied on every run (the field shows Default or Customized, and can clear back to defaults). Picking a different report resets stored filters.
Works with standard dataset-style report output; reports emitting a custom flat XML shape are parsed generically (the repeated row elements become rows).
Values become numbers only when they look like an invariant number — codes like 007, ISO dates and locale-formatted values stay text and are never mangled.
The report runs in the company you are working in. Report regions have no Company setting — the platform runs a report object in the session's company only.

Caching: running a report is slow, so the output is cached. The report re-runs only when its configuration changes (report or stored filters) or when you press the region's refresh button on its badge. Ordinary recalculations reuse the cached rows.

Example: the Customer - Top 10 List report as a region. Pick the report, open Filters & Options, set the period and "quantity to show" on its request page — those choices are stored with the sheet. The report's dataset rows land as cells you can chart or annotate; press the refresh button on the badge when you want fresh numbers. This is the pattern for any report whose logic you already trust: reuse it on a sheet instead of re-modelling it.

SQL Regions

The region executes a SELECT statement through the extension's SQL engine against Business Central tables. The SQL Setup dialog edits the statement in a SQL source editor with syntax highlighting and completion — table names, and field names once the FROM table is written.

SELECT [No.], [Name], [Balance (LCY)]
FROM [Customer]
WHERE [Balance (LCY)] > 0
ORDER BY [Balance (LCY)] DESC

Only SELECT is allowed (set operations opening with a parenthesis also pass) — the region re-executes, so data-changing statements are refused.
[Address] cell references are substituted with the cell's value before execution, so slicers can drive it: WHERE [No.] = '[B3]'. Numeric cells resolve without thousand separators; date slicers resolve in XML format; ?FIELD slicers resolve to their raw key even when the cell shows a display field's text.
Column headers show the real field names or aliases, as written.
Result typing follows the data like query regions.
Company: the statement runs against the company you are working in, or against the company the region's Company setting names — every table in the statement is read there. Accepts [Address], so a ?FIELD(Company, Name) slicer switches the company; the resolved company is part of the cache key. See Reading Another Company.

Caching: the result set is cached; the statement re-executes when its resolved text changes (edited, or a slicer moved a referenced [Cell]), when you page past the cached rows, or on the region's refresh button. The engine fetches one row past the current page, so the "more" indicator is always truthful without pulling the whole result set.

SQL examples

Top 10 customers by balance (something a table view cannot sort by, since the balance is a FlowField):

SELECT TOP 10 [No.], [Name], [Balance (LCY)]
FROM [Customer]
ORDER BY [Balance (LCY)] DESC

Item sales for the item picked in slicer E2, within the period in slicer C2:

SELECT [Posting Date], [Document No.], [Valued Quantity], [Sales Amount (Actual)]
FROM [Value Entry]
WHERE [Item No.] = '[E2]' AND [Item Ledger Entry Type] = 'Sale'
  AND [Posting Date] BETWEEN '[AA3]' AND '[AA4]'
ORDER BY [Posting Date]

(AA3/AA4 hold the range's two ends as XML dates — for example =Format(CalcDate('<-CM>', C2), 0, 9).)

Choosing Between Them

Need	Use
Simple filtered records, editable, formulas over the whole set	A table region
Joins/aggregations already modelled as a query object	Query region
Output of an existing report, with its request-page options	Report region
Free-form joins, aggregates and expressions, driven by slicers	SQL region
Group-and-aggregate cross-tabs with drill-down	A pivot region