Take Control of Your Data with Excel Report Layouts in Business Central
The 2024 Wave 1 release of Dynamics 365 Business Central made enhancements to Excel report layouts, letting users and developers create their own Excel layouts for reporting. This new feature goes beyond exporting data to Excel, which has been available since the Dynamics NAV days. Instead, it is an extension (Dynamics BC Excel Report) that provides the report along with the data from the report request pages. Data from multiple tables will appear in multiple worksheets.
There are eight reports in this release focused on three areas: customers, finance, and vendors. Microsoft has announced they will be providing 35 layouts over the course of this release, including layouts focused on areas such as Fixed Assets and Service Management.
The eight reports in this release are:
- Trial Balance
- Vendor – Top List
- Customer – Top List
- Trial Balance/Budget
- Aged Accounts
- Trial Balance by Period
- Aged Accounts Receivable
- Trial Balance/Previous Year
What’s in a report layout?
The report layout includes:
- The label and data fields from the dataset of the Business Central report
- The company logo and where it will be positioned on the report
- Text format, including font type, color, and size
- Page settings such as margins and background images
Reports can have multiple report layouts. Users can select the layouts they want to use as they need them.
More control in the user’s hands
Excel Layouts were introduced in Business Central 2022 Release Wave 1 (BC20). The new report layout feature gives you more control over your data.
Users can generate reports using default settings or use pivot tables to perform deeper analysis. Users can also use the reports as templates to create custom layouts for the same data.
Business Central developers can now build custom Excel reports by adding the fields needed by the user. The user can then perform their analysis using familiar tools, including formulas, which can be used to perform calculations or create custom expressions, and PivotTables, powerful tools to summarize and analyze data.
Users also have access to Excel’s built-in Microsoft Office capabilities, such as:
- Protecting worksheets to secure specific parts of the report
- Adding labels to sensitive data
- Providing notes and comments for clarification or explanation
- Utilizing Excel’s forecasting capabilities
- Integrating with other Microsoft tools like Power Automate or OneDrive
Users can also create interactive reports using visualizations such as slicers to filter data dynamically, making it easier to analyze. Users can also view the raw data directly from the report dataset to understand how the report functions and where the data originates.
Create multiple layouts using the same dataset
In Business Central, customers and partners can add multiple layouts with existing datasets developed by Microsoft, ISVs, and partners.
Customers can use the existing datasets (data) generated by AL reports. Customers can then leverage the power of Excel to analyze that data. Microsoft Excel power users can develop their own Excel layouts and then save them in Business Central.
Once a layout is saved in Business Central, all users can utilize it when running the report.
Additionally, customers can create multiple layouts for analysis using the same dataset. This lets Excel users across all departments build Excel layouts to suit their specific needs and save them in Business Central. Customers in each department can then choose the layout they want when running these reports.
For example, the same Item Ledger data might be utilized by Production, Manufacturing, and Warehouse staff. Customers can create different views in Excel and then each department can access the data using the view that is tailored to their department’s processes.
With the latest enhancements, Microsoft started utilizing temporary tables and Query objects to optimize the performance of these reports. Microsoft also generated examples showing developers how to build complex Excel layouts. Developers can use the built-in templates to learn the possibilities with Excel layouts and build their own Excel layouts based on their company’s unique business requirements.
Find out more
The new Excel report layout feature makes it easier for users to create reports in Excel, analyze the data, and present the data using a standardized format.
Be sure to contact ArcherPoint to learn more about how to use the latest features in Business Central.