---
id: video/1ft4o9lQzsU
type: video
title: "20260126 - Excel Report Layouts: From Basics to Refreshable, Real-Time Reporting"
summary: "Excel report layouts in Business Central: a four-step process (get workbook with data contract, lay out data, import, test) and refreshable reports that use OData, Power Query and APIs. Covers data contract rules, hidden metadata sheets, OneDrive setup, permissions and password limits."
tier: community
language: en
tags:
  - excel report layouts
  - data contract
  - metadata
  - refreshable reports
  - power query
  - odata
  - onedrive integration
  - api connections
  - permissions
  - multilingual reports
  - pivot tables
  - conditional formatting
system: reporting
review:
  state: reviewed
  by: opus
  at: "2026-10-07T23:22:10.805Z"
  flags: []
generated:
  at: "2026-10-07T23:22:10.883Z"
  pipeline: 0.2.0
  prompts:
    extract-video: 1
    summarize-video: 2
  input_hash: 30c336495ee4bcbcaea6b2f90d6b4e3e7c79af79cb8abafe6983dc593aa127af
evidence:
  - kind: video
    url: https://www.youtube.com/watch?v=1ft4o9lQzsU&t=419s
    title: "20260126 - Excel Report Layouts: From Basics to Refreshable, Real-Time Reporting"
    date: "2026-01-28T10:55:47.000Z"
    commit: null
    t: 419
    quote: 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.
  - kind: video
    url: https://www.youtube.com/watch?v=1ft4o9lQzsU&t=476s
    title: "20260126 - Excel Report Layouts: From Basics to Refreshable, Real-Time Reporting"
    date: "2026-01-28T10:55:47.000Z"
    commit: null
    t: 476
    quote: 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
  - kind: video
    url: https://www.youtube.com/watch?v=1ft4o9lQzsU&t=489s
    title: "20260126 - Excel Report Layouts: From Basics to Refreshable, Real-Time Reporting"
    date: "2026-01-28T10:55:47.000Z"
    commit: null
    t: 489
    quote: 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
  - kind: video
    url: https://www.youtube.com/watch?v=1ft4o9lQzsU&t=576s
    title: "20260126 - Excel Report Layouts: From Basics to Refreshable, Real-Time Reporting"
    date: "2026-01-28T10:55:47.000Z"
    commit: null
    t: 576
    quote: You can actually mix and match your data from multiple sources using Power Query. So this is kind of that refreshable Excel reports experience.
  - kind: video
    url: https://www.youtube.com/watch?v=1ft4o9lQzsU&t=648s
    title: "20260126 - Excel Report Layouts: From Basics to Refreshable, Real-Time Reporting"
    date: "2026-01-28T10:55:47.000Z"
    commit: null
    t: 648
    quote: 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
links:
  learn: []
  objects:
    - object/report/708
  features: []
  topics: []
  localizations: []
  videos: []
  posts: []
  guidelines: []
video_id: 1ft4o9lQzsU
channel: yt-areopa
source_name: Areopa webinars
url: https://www.youtube.com/watch?v=1ft4o9lQzsU
published_at: "2026-01-28T10:55:47.000Z"
duration_s: 3000
captions: derived
audience:
  - functional consultant
  - developer
  - administrator
  - end user
chapters:
  - t: 0
    title: Welcome and housekeeping
  - t: 191
    title: "Presenter introduction: Tanya Bickski"
  - t: 311
    title: Excel reports vs standard reports and agenda
  - t: 398
    title: Goals and benefits of Excel report layouts
  - t: 519
    title: "Why use Excel report layouts: interactivity and collaboration"
  - t: 648
    title: End user empowerment and Excel skills
  - t: 766
    title: "Real-world use cases: physical inventory, certificates, and tracking changes"
  - t: 1030
    title: Four-step process to create Excel layouts
  - t: 1066
    title: "Step 1: Getting the Excel workbook with data contract"
  - t: 1234
    title: System Excel sheets and metadata tables explained
  - t: 1474
    title: "Step 2: Laying out data using Excel features and Copilot"
  - t: 1588
    title: OneDrive integration and uploading Excel layouts to Business Central
  - t: 1874
    title: Testing and running Excel report layouts
  - t: 1951
    title: Refreshable Excel layouts and API structure
  - t: 2067
    title: Connecting to Business Central via OData and Power Query
  - t: 2193
    title: Building dynamic refreshable reports with environment variables
  - t: 2325
    title: Q&A on environment variables, permissions, API configuration, and security
  - t: 2544
    title: Sheet protection, password security, and refreshable Excel reports
  - t: 2715
    title: Four-step process and key takeaways
  - t: 2810
    title: Call to action, implementation strategy, resources, and closing
features:
  - name: Excel report layouts
    status: unclear
    t: 398
    verified: false
    status_source: video
  - name: Power Query integration for refreshable reports
    status: unclear
    t: 442
    verified: false
    status_source: video
  - name: Data contract in Excel layouts
    status: unclear
    t: 1066
    verified: false
    status_source: video
  - name: System Excel sheets and metadata
    status: unclear
    t: 1234
    verified: false
    status_source: video
  - name: Named formulas for metadata access
    status: unclear
    t: 1474
    verified: false
    status_source: video
  - name: Excel native features in report layouts
    status: unclear
    t: 1521
    verified: false
    status_source: video
  - name: Copilot integration in Excel reports
    status: unclear
    t: 1554
    verified: false
    status_source: video
  - name: OneDrive integration for Excel reports
    status: unclear
    t: 1588
    verified: false
    status_source: video
  - name: Physical inventory Excel reports
    status: unclear
    t: 766
    verified: false
    status_source: video
  - name: Multilingual Excel report layouts
    status: unclear
    t: 1403
    verified: false
    status_source: video
  - name: Office 365 collaboration for Excel reports
    status: unclear
    t: 622
    verified: false
    status_source: video
  - name: Excel layout import and validation
    status: unclear
    t: 1689
    verified: false
    status_source: video
  - name: Excel report layout testing and execution
    status: unclear
    t: 1874
    verified: false
    status_source: video
  - name: Refreshable Excel report layouts
    status: unclear
    t: 1951
    verified: false
    status_source: video
  - name: Business Central API structure and authentication
    status: unclear
    t: 2007
    verified: false
    status_source: video
  - name: Dynamic API connection using environment variables
    status: unclear
    t: 2193
    verified: false
    status_source: video
  - name: Custom APIs for Excel reports
    status: unclear
    t: 2285
    verified: false
    status_source: video
  - name: Permissions and API access for Excel reports
    status: unclear
    t: 2403
    verified: false
    status_source: video
  - name: Sheet protection and password limitations
    status: unclear
    t: 2544
    verified: false
    status_source: video
  - name: Excel report layout four-step process
    status: unclear
    t: 2714
    verified: false
    status_source: video
  - name: Existing report data sets export to Excel
    status: unclear
    t: 2739
    verified: false
    status_source: video
  - name: Excel data analytical capabilities for reporting
    status: unclear
    t: 2770
    verified: false
    status_source: video
objects_mentioned:
  - report Physical inventory list
  - report inventory order details
  - api sales order item categories
  - api sales orders API
  - table item ledger entries
quotes:
  - t: 419
    text: 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.
    check: exact
  - t: 476
    text: 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
    check: exact
  - t: 489
    text: 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
    check: exact
  - t: 576
    text: You can actually mix and match your data from multiple sources using Power Query. So this is kind of that refreshable Excel reports experience.
    check: exact
  - t: 648
    text: 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
    check: exact
---

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

> Excel report layouts in Business Central: a four-step process (get workbook with data contract, lay out data, import, test) and refreshable reports that use OData, Power Query and APIs. Covers data contract rules, hidden metadata sheets, OneDrive setup, permissions and password limits.

[Watch on YouTube](https://www.youtube.com/watch?v=1ft4o9lQzsU) · Areopa webinars · 2026-01-28 · 50:00 · tier community · reviewed (checked by Opus)

## 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.

## Chapters

- [0:00](https://www.youtube.com/watch?v=1ft4o9lQzsU&t=0s) Welcome and housekeeping
- [3:11](https://www.youtube.com/watch?v=1ft4o9lQzsU&t=191s) Presenter introduction: Tanya Bickski
- [5:11](https://www.youtube.com/watch?v=1ft4o9lQzsU&t=311s) Excel reports vs standard reports and agenda
- [6:38](https://www.youtube.com/watch?v=1ft4o9lQzsU&t=398s) Goals and benefits of Excel report layouts
- [8:39](https://www.youtube.com/watch?v=1ft4o9lQzsU&t=519s) Why use Excel report layouts: interactivity and collaboration
- [10:48](https://www.youtube.com/watch?v=1ft4o9lQzsU&t=648s) End user empowerment and Excel skills
- [12:46](https://www.youtube.com/watch?v=1ft4o9lQzsU&t=766s) Real-world use cases: physical inventory, certificates, and tracking changes
- [17:10](https://www.youtube.com/watch?v=1ft4o9lQzsU&t=1030s) Four-step process to create Excel layouts
- [17:46](https://www.youtube.com/watch?v=1ft4o9lQzsU&t=1066s) Step 1: Getting the Excel workbook with data contract
- [20:34](https://www.youtube.com/watch?v=1ft4o9lQzsU&t=1234s) System Excel sheets and metadata tables explained
- [24:34](https://www.youtube.com/watch?v=1ft4o9lQzsU&t=1474s) Step 2: Laying out data using Excel features and Copilot
- [26:28](https://www.youtube.com/watch?v=1ft4o9lQzsU&t=1588s) OneDrive integration and uploading Excel layouts to Business Central
- [31:14](https://www.youtube.com/watch?v=1ft4o9lQzsU&t=1874s) Testing and running Excel report layouts
- [32:31](https://www.youtube.com/watch?v=1ft4o9lQzsU&t=1951s) Refreshable Excel layouts and API structure
- [34:27](https://www.youtube.com/watch?v=1ft4o9lQzsU&t=2067s) Connecting to Business Central via OData and Power Query
- [36:33](https://www.youtube.com/watch?v=1ft4o9lQzsU&t=2193s) Building dynamic refreshable reports with environment variables
- [38:45](https://www.youtube.com/watch?v=1ft4o9lQzsU&t=2325s) Q&A on environment variables, permissions, API configuration, and security
- [42:24](https://www.youtube.com/watch?v=1ft4o9lQzsU&t=2544s) Sheet protection, password security, and refreshable Excel reports
- [45:15](https://www.youtube.com/watch?v=1ft4o9lQzsU&t=2715s) Four-step process and key takeaways
- [46:50](https://www.youtube.com/watch?v=1ft4o9lQzsU&t=2810s) Call to action, implementation strategy, resources, and closing

## Features

| Feature | Status | At |
|---|---|---|
| Excel report layouts | status not stated, demoed | [6:38](https://www.youtube.com/watch?v=1ft4o9lQzsU&t=398s) |
| Power Query integration for refreshable reports | status not stated, demoed | [7:22](https://www.youtube.com/watch?v=1ft4o9lQzsU&t=442s) |
| Data contract in Excel layouts | status not stated, demoed | [17:46](https://www.youtube.com/watch?v=1ft4o9lQzsU&t=1066s) |
| System Excel sheets and metadata | status not stated, demoed | [20:34](https://www.youtube.com/watch?v=1ft4o9lQzsU&t=1234s) |
| Named formulas for metadata access | status not stated, demoed | [24:34](https://www.youtube.com/watch?v=1ft4o9lQzsU&t=1474s) |
| Excel native features in report layouts | status not stated, demoed | [25:21](https://www.youtube.com/watch?v=1ft4o9lQzsU&t=1521s) |
| Copilot integration in Excel reports | status not stated | [25:54](https://www.youtube.com/watch?v=1ft4o9lQzsU&t=1554s) |
| OneDrive integration for Excel reports | status not stated | [26:28](https://www.youtube.com/watch?v=1ft4o9lQzsU&t=1588s) |
| Physical inventory Excel reports | status not stated, demoed | [12:46](https://www.youtube.com/watch?v=1ft4o9lQzsU&t=766s) |
| Multilingual Excel report layouts | status not stated, demoed | [23:23](https://www.youtube.com/watch?v=1ft4o9lQzsU&t=1403s) |
| Office 365 collaboration for Excel reports | status not stated | [10:22](https://www.youtube.com/watch?v=1ft4o9lQzsU&t=622s) |
| Excel layout import and validation | status not stated, demoed | [28:09](https://www.youtube.com/watch?v=1ft4o9lQzsU&t=1689s) |
| Excel report layout testing and execution | status not stated, demoed | [31:14](https://www.youtube.com/watch?v=1ft4o9lQzsU&t=1874s) |
| Refreshable Excel report layouts | status not stated, demoed | [32:31](https://www.youtube.com/watch?v=1ft4o9lQzsU&t=1951s) |
| Business Central API structure and authentication | status not stated, demoed | [33:27](https://www.youtube.com/watch?v=1ft4o9lQzsU&t=2007s) |
| Dynamic API connection using environment variables | status not stated, demoed | [36:33](https://www.youtube.com/watch?v=1ft4o9lQzsU&t=2193s) |
| Custom APIs for Excel reports | status not stated, demoed | [38:05](https://www.youtube.com/watch?v=1ft4o9lQzsU&t=2285s) |
| Permissions and API access for Excel reports | status not stated | [40:03](https://www.youtube.com/watch?v=1ft4o9lQzsU&t=2403s) |
| Sheet protection and password limitations | status not stated, demoed | [42:24](https://www.youtube.com/watch?v=1ft4o9lQzsU&t=2544s) |
| Excel report layout four-step process | status not stated, demoed | [45:14](https://www.youtube.com/watch?v=1ft4o9lQzsU&t=2714s) |
| Existing report data sets export to Excel | status not stated, demoed | [45:39](https://www.youtube.com/watch?v=1ft4o9lQzsU&t=2739s) |
| Excel data analytical capabilities for reporting | status not stated, demoed | [46:10](https://www.youtube.com/watch?v=1ft4o9lQzsU&t=2770s) |

## 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](https://www.youtube.com/watch?v=1ft4o9lQzsU&t=790s)
- [report 708 "Inventory Order Details"](../objects/report/708.md) at [18:01](https://www.youtube.com/watch?v=1ft4o9lQzsU&t=1081s)
- api "sales order item categories" at [38:05](https://www.youtube.com/watch?v=1ft4o9lQzsU&t=2285s)
- api "sales orders API" at [41:57](https://www.youtube.com/watch?v=1ft4o9lQzsU&t=2517s)
- table "item ledger entries" at [47:20](https://www.youtube.com/watch?v=1ft4o9lQzsU&t=2840s)

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

## Quotes

- [6:59](https://www.youtube.com/watch?v=1ft4o9lQzsU&t=419s) "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](https://www.youtube.com/watch?v=1ft4o9lQzsU&t=476s) "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](https://www.youtube.com/watch?v=1ft4o9lQzsU&t=489s) "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](https://www.youtube.com/watch?v=1ft4o9lQzsU&t=576s) "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](https://www.youtube.com/watch?v=1ft4o9lQzsU&t=648s) "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](https://www.youtube.com/watch?v=1ft4o9lQzsU&t=25s) other: Moving from RDLC to Excel is what we see in the Microsoft BC central team.
- [27:25](https://www.youtube.com/watch?v=1ft4o9lQzsU&t=1645s) other: one caveat is just making sure that your your popups are not blocked. And you'll just want to review external ...
- [40:35](https://www.youtube.com/watch?v=1ft4o9lQzsU&t=2435s) other: I can definitely though look into that and and share like on LinkedIn or something once we dive into that further
- [40:35](https://www.youtube.com/watch?v=1ft4o9lQzsU&t=2435s) 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](https://www.youtube.com/watch?v=1ft4o9lQzsU&t=2475s) 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.