What's New: Excel Layouts For Developers (2024 release wave 1)
Excel layout features for Business Central developers presented at the 2024 release wave 1 launch: the hidden report metadata worksheet, the caption data worksheet and runtime translation tags for multilanguage layouts (shipped in 23.3), refreshable reports that use Power Query and Business Central APIs, seven new preview Excel reports in version 24, and Excel Copilot (in preview) for finding outliers in report data.
- Introduction and Session Overview
- Translatable Excel Reports - Metadata Worksheet
- Caption Data and Translation Capabilities
- Runtime Translation Tags and User-Defined Translations
- Translation Example Demo and Use Cases
- Refreshable Reports Using APIs and Power Query
- Constructing URIs with Environment and Company Metadata
- New Excel Reports in Base Application
- Demo - Aged Accounts Receivables Report and Excel Layout Structure
- Multiple Data Items in Separate Worksheets and Excel Copilot Introduction
- Excel Copilot Setup and Outlier Detection Demo
- Learning Resources, BI Triangle Framework, and Best Practices
- Related Sessions and Community Resources
Overview
The session shows how report authors build Excel layouts that translate at runtime. A hidden metadata worksheet holds report, tenant, environment, company and timestamp data. A caption data worksheet holds report captions and labels. Translation tags in worksheet names, chart headers, pivots and slicers are replaced with the user’s language values when the report runs. An optional user-defined translation worksheet covers custom strings.
It then covers refreshable reports, where Power Query loads data from Business Central APIs using the environment name and company ID from the metadata worksheet. The seven new preview Excel reports in version 24, including Aged Accounts Receivables, are demoed as examples. The session also covers separate worksheets for each top-level data item (from version 23.0) and Excel Copilot, which is in preview and needs the workbook on OneDrive.
Key points
- Since version 23.3, Excel layouts include a hidden metadata worksheet with report object info, request properties, tenant ID, environment name, company name and ID, and the run timestamp.
- Translation tags (dollar-dollar) in worksheet names, chart headers, pivot elements and slicer headers are replaced with user-language values at runtime. Captions come from the caption data worksheet (also version 23.3).
- An optional user-defined translation worksheet lets layout designers add custom strings and translations that are not captions in the report object.
- Refreshable reports load data with Power Query from Business Central APIs, using the environment name and company ID from the metadata worksheet. Users must enable content in Excel.
- Version 24 ships seven new Excel reports in preview in the base application, added to the Business Manager and Accountant role centers. The demo showed a translation tag that was not yet translated.
- From version 23.0, each top-level data item in a report can appear in its own worksheet.
- Excel Copilot is in preview and requires the workbook on OneDrive. Its suggestions still need to be checked in Business Central.
Features
| Feature | Status | At | Evidence |
|---|---|---|---|
| Translatable Excel Layouts | status not stated, demoed | 1:09 | |
| Report Metadata Worksheet | status not stated, demoed | 1:25 | |
| Caption Data Worksheet | status not stated, demoed | 3:26 | |
| Runtime Translation Using Translation Tags | status not stated, demoed | 4:22 | |
| User-Defined Translation Data Worksheet | status not stated, demoed | 5:02 | |
| Refreshable Excel Reports via Power Query and APIs | status not stated | 7:18 | |
| New Excel Reports in Base Application | preview, demoed | 9:51 | “they are in preview for because we still need to maybe tweak a few things” (10:32) |
| Aged Accounts Receivables Excel Report | status not stated, demoed | 11:12 | |
| Multiple Data Items in Separate Worksheets | status not stated, demoed | 15:06 | |
| Excel Copilot for Data Analysis | preview, demoed | 15:46 | “this is about Excel co-pilot it’s is still in in preview in Excel” (15:46) |
| Open in Excel from List Pages | status not stated | 16:06 | |
| Excel Layout Raw Data Export | status not stated, demoed | 16:06 | |
| OneDrive Integration for Copilot | status not stated, demoed | 17:47 |
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 “Aged Accounts Receivables” at 11:32
- report “Customer Aging data” at 15:06
- report “Accounts Payables report” at 17:27
- report “Vendor Aging data” at 17:27
Not found in BC28-30: report “Aged Accounts Receivables”, report “Customer Aging data”, report “Accounts Payables report”, report “Vendor Aging data”.
Quotes
- 1:09 “back in 23 23.3 we added the ability or we added um two Excel layouts that we always have a hidden worksheet with some”
- 2:05 “you can actually use this in the Excel report itself if you want to provide like context specific help uh maybe a worksheet stating”
- 4:22 “we added the ability to translate all of this uh at runtime so as a report author you can now use translation tags or”
- 4:42 “when you layout the Excel and when you then upload and when people run the report with with this Excel layout the server will”
- 7:03 “it’s already present in version 23.3 but now this is the first time we we we talk about it in the launch”
- 7:18 “the new worksheet that was added in version 23.3 enables refreshable uh Excel reports if only you you uh you load the data in”
- 9:51 “with version uh 24 with this new version uh we also ship new Excel reports in the base application”
- 11:53 “if actually a pro tip here if you demo this with a customer make sure that you show this with Excel sorry with one”
- 12:11 “the first thing you notice is that you need to enable content this is just a warning from Excel because we have some power”
- 14:52 “starting in in version 23.0 there’s a new option in in in Excel uh layouts that can give you each of the data item”
- 15:46 “this is about Excel co-pilot it’s is still in in preview in Excel uh but how you can think of this”
- 16:26 “a report with Excel layout will also give you the raw data set directly in Excel so so that’s that’s that’s a good like”
- 17:47 “the Excel workbook must be on one drive so this is another uh benefit from enabling integration with business Central to one drive is”
- 20:20 “of course I mean co-pilot can only suggest things I will still need to go check it in business Central um and have a”
- 21:00 “analyzing pivoting and sharing data in business Central that shows you all the different types of artifacts for reporting powerbi Excel data analysis on”