Excel layouts in Dynamics

365 Business Central

Introduced in the 2022 release wave 1
Excel layouts in four five bullets
• Interactive reports (Not designed for print, One report can serve multiple
purposes (filtering, sorting, slicers, pivot tables etc.)
• Mix’n’match data from Business Central with other data sources
(Sharepoint lists, On-premises databases, files, APIs, …) using Power Query
in the report.
• Use built-in, advanced Excel features to do post-processing on rendered
reports (triggering automation flows, Organization data types, What-if
analyses, Forecast sheet, Analyze Data with support for natural language)
• Rich collaboration capabilities (commenting, sharing, protecting for
certain audiences) on a report
• Democratized design experience - allowing end users to participate more
actively in layout creation. Contains the raw data, making it possible for a
user to understand how the report works
Layout experience
Creating a layout in four easy steps
1. Get Excel workbook with the “data contract”
• Edit existing Excel layout, or
• Get blank template from request page, Schedule, Excel Document (Data only)
2. Layout data as you wish
• Use almost any Excel feature (except VBA and password protection)
3. Import layout file into Business Central
• On Report Layouts page, choose New Layout
4. Test layout
• On Report Layouts page, choose Run Report
Get blank template
Get blank template
 The “Data Contract” (do not change this)

Contract
The “Data Contract” (do not change this) – new
feature in 2023 release wave 2: multiple worksheets

Data contract
Translatable Idea: report author can use tags for
Excel layouts  Worksheet names
 Chart headers
(23.3)  Pivot elements
…

Server will replace tags at runtime when

rendering the report.
Translatable Excel layouts - example
Layout using $$ syntax Report when user runs it
Static and Always have a (hidden) worksheet with
runtime  Report AL metadata
metadata  Request metadata
worksheet  Request page filters
(23.3)
 Report captions
Report Metadata worksheet

Runtime metadata
Static metadata
Enables API based (refreshable) Excel reports
Enables ”About this report” info for author
Company/User/Report date easily available for author
Caption data worksheet
Layout the data (pivot tables, slicers, and charts)
Use multiple pivot tables in the same report
Use table formulas to create calculated columns
Use PowerQuery to merge with external data
Credit scores loaded from Excel workbook on
Sharepoint site
Importing the layout
Importing the layout
Importing the layout
Testing the layout
Design guidelines (for layouts)
Worksheet naming and location
• Locate worksheets in the order you think will be most useful for users
so that they do not have to scroll when using the report.

• Good worksheet names help users quickly get an overview of the

information they can obtain by navigating to the worksheet.

• An Excel worksheet can be up to 31 characters long.

One or multiple worksheets?
• In contrast to a report designed for print or pdf, an Excel report typically
consists of multiple worksheets, each of which is designed for a different
purpose. Some common types of worksheets are
• Overview dashboard
• Pivot table
• Table
• Print-friendly
• About the report

• There is no technical limit to the number of worksheets in a report, but

users probably prefer a number they can overskue.
• Note that you can develop multiple Excel layouts for the same report, so
maybe design for a specific persona.
One or multiple worksheets?
• In contrast to a report designed for print or pdf, an Excel report typically
consists of multiple worksheets, each of which is designed for a different
purpose. Some common types of worksheets are
• Overview dashboard
• Pivot table
• Table
• Print-friendly
• About the report

• There is no technical limit to the number of worksheets in a report, but

users probably prefer a number they can overskue.
• Note that you can develop multiple Excel layouts for the same report, so
maybe design for a specific persona.
Using Office themes
• Consider using a common Office theme for all your layouts.
Developer experience
Developer experience
 Create a Report Object in AL.

 Define your report dataset.

 Provide a path to your Excel

Layout.

 Publish your report extension.

What if I change the dataset definition?
• Easiest way to change your layouts in the AL projects is simply to add
the new fields (metadata) to the data contract worksheet
OneDrive Integration
Configure OneDrive
Integration
Unlocked actions
• Open in OneDrive
• Share window

Example features
• Report inbox
• Attachments factbox
• Incoming documents
• Sales Budgets
• Customizations and add-ons
Affected features
• Open in Excel
• Edit in Excel
• Report export (layouts ☺)
Power BI integration
Ad hoc reporting - Excel Organization Data Types based
on Business Central

• Built in Excel data types expand

with ones defined by you for
your organization
• Based on shared Power BI
dataset and “featured” tables
(needs PRO license)
• Ideal for reference data: items,
customers etc
• Works for on-premises and
cloud
• Auto data refresh - as requested
in Power BI
Where does Excel layouts shine?
Use Excel layouts when
• The report dataset fits Excel
• Less aggregated is good
• Less formatted data is good

• The users want to interact with the data

• A paper copy is not a priority

Understanding the numbers and process
• A report with an Excel layout contain the raw data
• aka data lineage (what data lies below this report)

• Possible for a user to understand how the report works

• aka process lineage (which steps were taken to transform the raw data to the
results in the report)
Democratizing the layout experience
• Report development in Business Central consists of two parts
• developing the report dataset (the report object)
• developing the layout (Word/RDLC/Excel)

• With Excel layouts, report layout experience is now in the hand of

normal users
• Given that the dataset has the data needed in the correct format, end
users now have total freedom to change the look and feel of the report,
add additional views on the data, filter and sort as they need, etc. If
one of these modifications make sense to save, they can just import it
as a new layout.
Excel layout samples
aka.ms/bcexcelsamples
Post-processing (with Excel)
Using Excel features to do post processing
and data analysis
• Sort & Filter
• Data Tools
• Forecast
• Outline data
• Power Pivot
• Data Insights
•…
Post-processing in Excel – Sort & Filter
• Slice’n’dice data on worksheets
Post-processing in Excel – Data Tools
• Manipulate data for further
analysis
Post-processing in Excel – Forecast
• If data is laid out right, maybe
you can use Excel forecasting
features
Post-processing in Excel – Outline
• Group data and make subtotals
Post-processing in Excel – PowerPivot
• Create rich data models and add
measures and KPIs
Let Excel find insights in your data
Collaborate (with Excel)
Using Excel features to collaborate on the
report document
• Comments
• Notes
• Ink
• Protect
• Document classification
• Power Automate Flows
•…
Collaborate in Excel – Comments
Collaborate in Excel – Use notes
Collaborate in Excel – Draw notes with Inking
Collaborate in Excel – Protect
sheet/workbook
Collaborate in Excel – Classify the document
• Maybe the content is not for
everyone?
Collaborate in Excel – Share the report
Collaborate in Excel – Kick off a Flow
Using Power Query
Using Power Query in the layout to enrich
with external data
• Merge/join the report dataset with data from
• SQL Server databases (on-premises or in Azure)
• Power BI datasets
• Excel Organizational data types
• Sharepoint lists
• Files on Onedrive (Excel, txt, CSV, …)
• Data in Azure Datalake/Synapse
• APIs (such as weather data or web shops)
• …
Using Power Query - Files
Using Power Query - Databases
Using Power Query – Azure data stores
Using Power Query – Azure services
Using Power Query – Other sources
Tips and Tricks
Do you have code samples?
Yes, please visit
https://aka.ms/bcexcelsamples
I just want the report data in Excel
• Tip: Just use request page, Schedule, Excel Document (Data only)

• Quick tip: do it with an Excel layout if you want to remove columns

you don’t need from the Data worksheet (Yes, you can do that)

• Advanced tip: Consider doing some data manipulation in Power

Query. Just the things you always do
I want to to create a layout from scratch, but I
don’t know what might be useful to show
• Tip: Try using Analyze Data and just add the insights to the layout
I want to manipulate data and show it in a
worksheet
• Tip: learn about table formulas in Excel

• Advanced tip: Do it in Power Query

I want to use nice headings in the pivot tables
• Just rename fields in the Pivot table

• Do not rename fields in the Data worksheet (remember the contract)

I want to use the report as a data source
• Every night update an Excel workbook stored in a ‘public’ place (e.g.
Onedrive)

• Using Power Query, other Excel workbooks (or Power BI) use the
report as data source.

• You effectively have your report dataset exposed to Power BI

Links
• Documentation:
• aka.ms/bcreporting

• Samples
• aka.ms/bcexcelsamples
How it works in the server
• During upload we remove all data from the Data worksheet

• During RUN, we update the Data-Sheet with new data

• Author can remove columns from the Data worksheet, any other
changes are not allowed

• ForceFullCalculation and FullCalculationOnLoad set to true to update

all pivot table and graph references.