Working with Microsoft Excel layouts

Microsoft Excel report layouts are based on Excel workbooks (.xlsx files). With them, you can create reports that include familiar Excel features for summarizing, analysing, and presenting data such as formulas, PivotTables, and PivotCharts.

This article explains some important things you need to know to get started with Excel layouts.

Why use Excel layouts?

Benefits of using Excel layouts:

• Create interactive reports using visualizations such as slicers.
• View raw data from the report dataset, which helps you understand how the report works and where the data in visuals comes from.
• Use built-in Microsoft Office features to do post-processing on rendered reports, including:
  • Protecting worksheets
  • Applying sensitivity labels
  • Adding comments and notes
  • Forecasting and analysis
• Use installed add-ins and app integrations, such as Power Automate flows or OneDrive.

Tip

With OneDrive integration set up, when you run a report with an Excel layout, the Excel workbook file is copied to OneDrive and then opened in Excel online. For more information, see Save Excel workbooks and report files in OneDrive

Get started

There are basically two tasks involved in setting up an Excel layout of a report:

1. Create the new Excel layout file.
2. Add the new layout to the report.

Task 1: Create the Excel layout file

There are several ways to create an Excel layout file for a report.

From any report

Follow these steps to create an Excel layout from any report, regardless of the current layout type. The Excel layout contains the required Data sheet and table, a Report Metadata sheet, and nothing else.

1. Choose the icon, enter Report Layouts, and then choose the related link.

   The Report Layouts page appears and lists all the layouts currently available for all reports.

2. On the Report Layouts page, choose any layout for the report, then choose the Run Report action.

3. On the report's request page, choose Send to>Microsoft Excel Document (data only)>OK.

   This step downloads an Excel workbook that contains the report dataset.

4. Open the downloaded file in Excel, make changes, then save the file.

From another Excel report layout

If there's already an Excel layout for a report, you can use the existing layout as a starting point. There are two approaches to getting a copy of the layout. You can either export the existing layout from the Report Layouts page or download the layout from the report's request page. Both ways download an Excel layout file that includes all the sheets of the existing file. The difference is when you download it from the request page, the layout includes actual data. (The data isn't required but it helps when designing the layout.)

Approach 1: Export the layout from the Report Layouts page

1. Choose the icon, enter Report Layouts, and then choose the related link.

   The Report Layouts page appears and lists all the layouts currently available for all reports.

2. Select the Excel layout from the list, then choose the Export Layout action from the top of the page.
3. Open the file in Excel, make the changes, then save the file.

Approach 2: Download the layout from the report's request page

1. Choose the icon, enter Report Layouts, and then choose the related link.

   The Report Layouts page appears and lists all the layouts currently available for all reports.

2. On the Report Layouts page, choose any layout for the report, then choose the Run Report action.
3. On the report's request page, choose Download.
4. Open the file in Excel, make the changes, then save the file.

From AL code

This method is the most advanced way to create an Excel report layout. It requires knowledge of AL code and is intended for programmers. In this approach, the Excel layouts are part of an extension package you install. Learn more at Creating an Excel Layout Report in the Developer and IT Pro help.

Task 2: Add the Excel layout to the report

Once you have the Excel layout file, the next task is to add it as a new layout for the report.

1. Choose the icon, enter Report Layouts, and then choose the related link.

   The Report Layouts page appears and lists all the layouts currently available for all reports.

2. Choose New Layout.

3. Set Report ID to Report.

4. Enter a name in Layout Name.

5. Set Format Options to Excel.

6. Select OK, then do one of the following steps to upload the layout file for the report:

   • Drag the file from file explorer on your device to the dialogue box.
   • Select the click here to browse link, find the file, then select the Open button.

   The selected file is uploaded to the layout, and the Report Layouts page opens.

7. To see how the report looks in the new layout, choose the layout from the list, then select Run Report.

Understanding Excel layouts

There are a few things you need to know or consider when creating or making changes to Excel layouts. Every Excel layout must include two elements: a Data sheet and a Data table. These elements form the basis of the layout by defining the business data from Business Central that you can work with. You can think of the Data sheet as a kind of contract between the layout and the business data. You use this data as the source of calculations and visualizations that you want to present on other sheets.

There are some requirements to the structure of the Excel workbook that must be met for the layout to work. The following diagram and table outline the elements of an Excel layout and the requirements.

No.	Element	Description	Mandatory
1	Data sheet	Must have the name Data. Can only include one table, which must be named Data.
2	Data table	Must have the name Data. Must have at least one column. Can only include columns that are in the report dataset. Must start in the first cell A1 of the Data sheet.
3	Presentation sheets	Used to present data. Data comes from the Data sheet.
4	Report Metadata sheet	Automatically included if the layout was created by exporting another Excel report. Contains general information about the report. Can be deleted.

In summary, you should and shouldn't do the following things on the Data sheet:

• Don't change the name of Data sheet, Data table, or columns.
• You can delete or hide columns.
• Don't add any columns unless they're included in the report dataset.
• You can place the sheets in any order, with the Data sheet first or last.

See also

Creating an Excel layout report (developer documentation)
Managing Report Layouts
Change the Current Report Layout
Import and Export a Custom Report or Document Layout (Legacy)
Analysing Report Data with Excel
Working with Reports
Working with Business Central

Find free e-learning modules for Business Central here