With Microsoft Dynamics 365 Business Central 2022 Wave 1 (BC20), a new feature is added to the application, that adds the capability to create custom report layouts in Excel with all the potential that Excel enables us – pivot tables, formulas, charts, etc.
The Excel layouts work as do Word and RDLC layouts, meaning that they can be edited and imported back to Business Central, using only the Web Client and without the need to develop any functionality, if no dataset changes are needed.
Important note: Not all reports have an optimized dataset to be used with Excel.
This feature is can be of major usage since it enables the extraction of rough data and gives the customer the power to design and perform its own custom calculations trough Excel.
To create an Excel layout for a Report:
1. Run the Report and choose Send To, and then on Microsoft Excel Document (data only):
2. The result is an Excel file with 2 sheets:
a. Data: Contains the Report dataset.
b. Report Metadata: Contains the Report filters and report properties.
3. Add a new sheet to the Excel file, design the layout and import back the Report layout to Business Central from the Report Layouts page by using the New Layout action:
4. Run the Report from the Report Layouts page, using the Run Report action with the imported Excel Layout record selected:
5. Open the Excel file (In this case the Excel report layout sheet contains only a Pivot Table that shows the Sales Amount per Customer):
