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, analyzing, and presenting data such as formulas, PivotTables, and PivotCharts.

Example of an Excel layout.

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

Why use Excel layouts?

Benefits of using Excel layouts:

  • You can create your own report layouts with Excel, either from scratch or based on an existing layout.
  • You can include multiple worksheets, interactive elements, visualizations, pivot tables, and slicers.
  • You can view raw data from the report dataset to help understand how the report works and where the data in visuals comes from.
  • You can use built-in Microsoft Office features for post-processing on rendered reports, including:
  • You can use installed add-ins and app integrations, such as Power Automate flows and OneDrive.
Tip

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, go to Save Excel workbooks and report files in OneDrive

Get started

There are two tasks involved in setting up an Excel layout for 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 for your report:

  • From any report.
  • From an existing Excel report layout.
  • From Visual Studio Code.

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, and a Report Metadata sheet.

  1. Select the Lightbulb that opens the Tell Me feature 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, then Microsoft Excel Document (data only), and then OK.

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

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

Task 2: Add the Excel layout to the report

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

  1. Select the Lightbulb that opens the Tell Me feature 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. In the Layout Name field, enter a name.

  5. In the Format Options field, choose Excel.

  6. Select OK, and 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 dialog box.
    • Select the click here to browse link, find the file, and then select Open.

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

  7. To review the report in the new layout, choose the layout from the list, and then select Run Report.

Understanding Excel layouts

There are a few things you need to know or consider when you create or change Excel layouts. Every Excel layout must have a Data sheet and a Data table. These elements define the business data that you can work with from Business Central. The Data sheet links the layout to the business data, which is the basis for the calculations and visualizations that you present on other sheets.

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

The different elements of an Excel layout.

No. Element Description Mandatory
1 Data sheet
  • Must have the name Data.
  • Can only include one table, which must be named Data.
 Is mandatory
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.
 Is mandatory
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 when working with the Data sheet:

  • You can delete or hide columns.
  • You can place the sheets in any order, with the Data sheet first or last.
  • You shouldn't rename the Data sheet, Data table, or any of the columns.
  • You shouldn't add columns unless they already exist in the report dataset.

Creating an Excel layout report (developer documentation)
Report and document layouts overview
Set the layout used by a report
Import and export a custom report layout (Legacy)
Analyzing report data with Excel and XML
Run and print reports in Business Central
Working with Business Central

Find free e-learning modules for Business Central here