Category: Business CentralRead time: 5 MinsPublished on: 16 Feb 2026

Designing Excel Layout Reports in Microsoft Dynamics 365 Business Central

  1. Home
  2. Blog
  3. Excel Layout Reports in Business Central

The traditional way of designing reports in Business Central is RDLC or Word. Excel Layout reports in Business Central provide a modern alternative using the ExcelLayout property and DefaultLayout = Excel in AL code, enabling pivot tables, custom filtering, formulas, and charts that make reporting more interactive and user friendly.

This guide explains how to create Excel Layout reports in Business Central using AL report objects, dataset design, and Excel features such as PivotTables and PivotCharts, and compares Excel layout vs RDLC to help you choose the right approach.

For organizations looking to design advanced Excel Layout or migrate RDLC reports, working with experienced Business Central consulting services ensures optimal dataset design and maintainable AL report extensions.

Like other reports, Business central has two parts to design the excel report

  1. Report Dataset
  2. Report Layout

1. Report Dataset

Within Microsoft Dynamics 365 Business Central, you need to create the report dataset for an Excel Layout report using AL. This dataset determines which columns appear on the ExcelLayout sheet.

Steps to create Excel report using AL code

  • Create a report in AL Code
  • Assign the property DefaultLayout = Excel
  • Specify the report name with/ without file path having extension as .xlsx in ExcelLayout Property
  • Specify a table name for the dataset in the dataitem
    This image shows a snippet of AL code ,the language used for Microsoft Dynamics 365 Business Central within a code editor, specifically defining a report object.
  • Design the columns

These columns become the source for the ExcelLayout worksheet, where users can apply PivotTables, formulas, and additional Excel-based analysis.

2. Report Layout

  • By building the AL package, Business Central automatically generates the default Excel Layout file defined in the ExcelLayout property of the report object.
    ExcelLayout property of the report object
  • The standard Excel Layout includes organized sheets like Data, Aggregated Metadata, Translation Data, and Caption Data, which Business Central uses to generate the final Excel report.
    The screenshot displays an Excel workbook with multiple tabs, specifically focused on a table structure within the 'Data' worksheet.
  • The user can include additional design using the values from the Data Sheet.
  • New sheet features in Excel include tools for summarizing, analyzing, and presenting data, such as formulas, PivotTables, and PivotCharts.

Using the Business Central Excel Layout, developers can add PivotTables, PivotCharts, slicers, and formulas to transform raw dataset output into interactive management reports.

A screenshot of the same Excel spreadsheet with the 'PivotTable from table or range' dialog box open. The Table/Range field is populated with 'PostedReceipts', which matches the dataitem name from the AL code.
A screenshot of a PivotTable which shows 'Row Labels' and 'Sum of PPV_Value' with a grand total of 0. To the right, a PivotChart is displayed.

Save the changes to the report’s layout.

Difference Between RDLC and Excel Layout Reports

Unlike RDLC reports that focus on print-ready layouts, Use Excel Layout reports for analysis and self-service reporting, allowing end users to summarize data using native Excel capabilities.

3. Run the Excel Report

Use Request pages if required. Publish your changes and run the report. The Request Page in Business Central can be used to filter the dataset before generating the Excel Layout report, ensuring only relevant data is exported to Excel. Apply the filters on the request page.

A screenshot of a Business Central report request page titled 'Posted Excel report'. The Report Layout is set to a custom file named 'TestPostedExcelReport.xlsx'.

You can now download the report.

A screenshot of the PivotTable lists specific PO numbers (e.g., 106852, 106937) under 'Row Labels' with corresponding numeric values in the 'Sum of PPV_Value' column.
Other Key Points:
  1. By setting the ExcelLayoutMultipleDataSheets AL property to True, users can show multiple data items across different sheets, an approach recommended for creating complex Excel reports in Business Central.
  2. You can also create the Standard report using an Excel layout.
  3. Microsoft releases Out-of-box excel reports for various modules.
  4. Standard Business Central reports can be converted to Excel Layout using report extensions, enabling PivotTable-based analysis without modifying the base application.
A screenshot of the Report Layouts list view in Business Central. The table displays multiple columns, including Report ID, Layout Name, Description, Default, Extension, and Type.

4. Benefits of Excel Layout Reports in Business Central

  • Enables PivotTables and PivotCharts directly from Business Central dataset
  • Supports formulas, slicers, and custom calculations
  • Better alternative to RDLC for analytical reporting
  • Allows end users to modify layout without AL changes
  • Works with standard and custom Business Central reports
Limitations to Consider
  • Not ideal for pixel-perfect printable documents (RDLC is better)
  • Large datasets may affect Excel performance
  • Layout must follow ExcelLayout structure generated by AL

5. FAQs

  1. What is ExcelLayout property in Business Central?

    The ExcelLayout property in AL defines the .xlsx file used as the design template for an Excel Layout report in Business Central.

  2. How to enable multiple data sheets in Excel report?

    Set the AL property ExcelLayoutMultipleDataSheets = True to display each dataitem in a separate Excel sheet.

  3. Can standard BC reports use Excel layout?

    You can use report extensions to add Excel Layouts to standard reports without changing the original objects.

  4. RDLC vs Excel layout – which to choose?

    Use RDLC for print documents and Excel Layout for analytical and PivotTable-based reporting.