20260126 - Excel Report Layouts: From Basics to Refreshable, Real-Time Reporting

Overview

Tanya Bickski presents how to build Excel report layouts from any existing report data set in Business Central without code. She walks through the four-step process: export a workbook with the data contract, lay out the data with Excel features such as pivot tables, conditional formatting and Copilot, import the file back, and test it. Use cases include physical inventory lists, certificates and tracking changes.

The second half covers refreshable Excel reports. These connect to Business Central through OData and Power Query, use the API URL structure, and build dynamic connections from variables read from the aggregated metadata sheet. The Q&A covers permissions, API configuration, and why sheets cannot be password protected when uploaded as layouts.

Key points

• The data contract is the data sheet and table named data. It must start at cell A1, needs at least one column, and end users should not change its column names; developers can add new columns from the report data set.
• Hidden system sheets hold report metadata, captions, translations and aggregated metadata (tenant, environment, company, language). They can be re-hidden, but the presenter knows no way to stop users from unhiding them.
• When importing a layout, the Excel file must not be password protected. The data worksheet must exist and be named correctly, and columns are validated against the data contract.
• With OneDrive set up by an administrator through the OneDrive setup wizard, the workbook opens in the browser. Without it, the file downloads to the device. Popups must not be blocked, and SharePoint external sharing settings should be reviewed.
• Refreshable reports connect through an OData feed in Power Query with an organizational account. API URL: API.Central.dynamics.com/2.0/[tenant-id]/[environment-name]/api/[publisher]/[group]/[API]. Publisher and group are needed only for custom APIs.
• Power Query variables can read tenant ID, environment name and company ID from the aggregated metadata sheet, which makes switching between sandbox and production easier. Company ID may contain curly brackets that need cleanup.
• The presenter believes users of API-based reports need permissions on the tables used but had not verified it. Password protection is possible only for refreshable reports stored outside Business Central.

Features

AL objects mentioned

As heard in the captions. A name that matches one object page by exact type and name links to it; the others stay as named.

• report “Physical inventory list” at 13:10
• report 708 “Inventory Order Details” at 18:01
• api “sales order item categories” at 38:05
• api “sales orders API” at 41:57
• table “item ledger entries” at 47:20

Not found in BC28-30: report “Physical inventory list”, table “item ledger entries”.

Quotes

• 6:59 “Excel reports don’t require any coding experience. So everything that I’m kind of going through today, I didn’t have to write any custom code.”
• 7:56 “Where we’ve kind of been limited with reporting in the past is your data is presented to you kind of in that like flat”
• 8:09 “When we give the data over to Excel, we’re able to use pivot tables. were able to use really the whole, you know, experience”
• 9:36 “You can actually mix and match your data from multiple sources using Power Query. So this is kind of that refreshable Excel reports experience.”
• 10:48 “The biggest thing about Excel reports is that it enables the end user to be involved in their layout creation and maintenance. So it’s”

Disclaimers in the video

• 0:25 other: Moving from RDLC to Excel is what we see in the Microsoft BC central team.
• 27:25 other: one caveat is just making sure that your your popups are not blocked. And you’ll just want to review external …
• 40:35 other: I can definitely though look into that and and share like on LinkedIn or something once we dive into that further
• 40:35 subject-to-change: I haven’t necessarily dove into too much. I can definitely though look into that and and share like on LinkedIn …
• 41:15 subject-to-change: I don’t necessarily know that there’s a way to secure it.

Presenters (as heard): Tanya Bickski, BB, Bert, Ron, Tanya Burkski.