D365 Business Central : Using Excel Layout for Reporting

Since Business Central 2022 Wave 1 (BC20), we have the ability to use Excel Layout for reporting. Excel layouts work the same way as Word and RDLC layouts in that they can be edited and saved back again into Business Central. With Excel layouts for Business Central reports, users can now create and edit report layouts simply by using the full capabilities in Excel such as sliders, diagrams, charts, and pivot tables. Let’s try to create one using Inventory Order Details.

This is the standard Inventory Order Details layout. We are going to make an excel layout from this report.

To make an excel layout, we need to export the report raw data to excel first. We can do this by running the report and click Send To > Microsoft Excel Document (data only).

Open the downloaded excel file and you will get an excel with two sheets: Data and Report Metadata.

The Data sheet contains the record information saved as Table called Data. The Report Metadata sheet contains the report properties and filters.

Make sure you do not change the name or structure of the existing sheets !

We can now use the Data table to generate any report that we need. We can use all the features of Excel, such as excel formulas, pivots, charts, and so on.

Let’s try to create pivot and chart from it. First, we create an Outstanding Quantity pivot based on table Data.

Next, we create Outstanding Amount chart based on table Data.

Save the excel so we can use the file as Excel Layout. Go back to D365 Business Central and search for Report Layouts.

Click New Layout and fill in the necessary information.

Choose the excel file that we modified and upload it to Business Central.

Our new Excel Layout is inserted. Now we can use Set Default to apply it.

Before we test the new excel layout, I purposely created 10 more new Sales Orders so we can test that it will work will new data as well. Let’s run the report now.

Notice that the Print and Preview button is gone now. It is replaced by a new button called Download. This means that we can’t print the Excel directly. Let’s download and open the excel.

Nice ! You now have the Pivot and Chart in the Excel. As you can see, the new 10 Sales Order are part of the data as well. It’s nice and easy, so go and give it a try.