Using Excel Layouts to Run Reports in Dynamics 365 Business Central

Up until now, using customized report layouts in Dynamics 365 Business Central could only be done using Word or RDLC (Report Definition Language Client side) and did not provide any benefit for some basic reports. Using account schedules to define your financials is a powerful tool, but what about when you want to view customer sales data and have it in an interactive layout for your sales team?

In the 2022 Wave 1 release, Microsoft has introduced Excel Layouts for users to export raw data from within Business Central, transpose the data in an interactive report that represents what users consider to be relevant and save that layout within Business Central for future use, by all or designated users.

Let’s look at how we can create a custom report layout for the “Customer – sales list” in four simple steps.

*Note – using excel layouts are not designed for printed reports.

Step 1

First, we want to “send” the report to an Excel document (data only). The user can use the “tell me” feature in Business Central to search for the required report. In this example I am going to export the aged accounts payable.

This format extracts the raw data from the system and presents it in a table format as seen in the screen clip below:

Until now, this raw data format was not a practical way for users to extract and analyze their data. Any changes made to the file would not be saved, requiring the user to duplicate the analytics every time the report was ran. With excel layout capability, users can save their file and store it as the default report layout within Business Central.

Step 2

Next the user can create customized tables to represent the data in a visual format. Users can get creative in designing reports by adding custom tables, insert pivot tables, slicers, graphs etc. Users can define as many worksheets within the report layout as they desire. Excel offers many powerful data analysis tools that can be used to make suggestions on how to help you create that first report layout.

*Note – do not use any password protection or document sensitivity as Business Central will not be able to validate the file when you upload.

In a simple example, we can create a customer mailing list by formatting the raw data from the customer sales report, into a table with very specific fields:

In a more detailed example, using the same report from Business Central, I have created multiple worksheets analyzing the same data within the report:

I have inserted a pivot table to analyze total sales of each customer, added a bar graph for visual effect and inserted slicers so users can filter the data:

Step 3

Once the report has been defined, the next step is to save the file in any repository that you prefer and then upload into Business Central. Use the “Tell me” function to search for “report layouts”.

Select “New Layout” from the ribbon and define the criteria for the Report ID and then choose the excel file from the saved location.

To ensure this custom layout will be the format presented when Business Central runs the requested report, you need to define your excel file as the default report layout using the function found in the ribbon called “set default”.

Now when you choose to run the report in Business Central, you want to select the download option which will download the excel file with all the custom analytics you created.

Step 4

You can now collaborate with team members within the file using comments and notes. You can share the document and protect the workbook from user edits with password and sensitivity functions.

Get creative with your reporting and share with us how you have taken raw data and turned it into a valuable report for your organization!

Please connect with us to learn more about Business Central and using Excel Layouts.