Business Central reporting in Excel Layouts

Content menu

• Reporting
• Create Excel layouts for your Business Central reports
• Excel layouts for Business Central reports
• Excel layouts
• Why use Excel layouts?

Reporting

Reporting is given extremely high priority by many business users and business leaders, both because reports serve as a way for decision makers to gain insight into new growth opportunities, but also as a way to share data as a basis for discussions about business planning and future strategies for the company.

Create Excel layouts for your Business Central reports

One of the new features introduced in Dynamics 365 Business Central 2022 Release Wave 1 is the ability to create Excel layouts for both standard and custom Business Central reports.

This means that starting with Business Central 20, you can create custom exports in Excel to fill a predefined and pre-formatted Excel report (with formulas, macros, charts and so on) or to create an Excel report exactly as in a given model.

Excel layouts for Business Central reports

Excel layouts work in the same way as Word and RDLC layouts and can be edited and saved again in Business Central.

With Excel layouts for Business Central reports, business users can now create and edit report layouts by simply using the full range of functionality and options in Excel, such as sliders, charts, charts and pivot tables.

If you want to create an Excel layout from scratch, simply open the report request page and then run your report with the 'Excel document (data only)' option. The report will then generate an Excel file with sample data and the fields available in the report definition. Then simply add your layout to additional tabs in the Excel file. If you want to test your Excel layout for security, just import it as a custom layout in Business Central. Once the system has validated that the layout is valid and ready for use, you can go to the request page and run the report with your new layout.

Excel layouts

Microsoft Excel report layouts are based on Microsoft Excel workbooks (.xlsx files) and are a report layout designed in Microsoft Excel that you can use to format your business reporting data in Excel spreadsheets.

Excel report layouts let you create reports using familiar Excel functions to summarize, analyze and present data, such as formulas, pivot tables and pivot charts.

Why use Excel layouts?

There are many good reasons to use Excel layouts, here are some of them:

• You can create interactive reports using visualizations such as data filtering slicers.
• View raw data from the report dataset and gain insight into and understanding of how the report works and where the data your visuals are based on comes from.
• Use the built-in Microsoft 365 features and options to finish rendering reports, such as:
  • Protecting the spreadsheets to prevent other users from accidentally or deliberately modifying, moving or deleting data in a spreadsheet.
  • Apply sensitivity labels to your files and emails to comply with company policies and information protection requirements.
  • Add 'Comments' to have conversations with other users or 'Notes' to make remarks.
  • Forecasting and analysis, where with the 'What if' analysis functions in Excel, you can use several different sets of values ​​in one or more formulas and thus examine all the different results.
• Use installed and integrated add-ons and app integrations, such as Power Automate flows or OneDrive.