This post is part of the New Functionality In Microsoft Dynamics 365 Business Central 2023 Wave 2 series in which I am taking a look at the new functionality introduced in Microsoft Dynamics 365 Business Central 2023 Wave 2.
The twenty-eighth of the new functionality in the Development section is Define multi-worksheet datasets for Excel reports.
AL developers can now define AL report datasets so that when running with Excel layouts, multiple top-level data items are stored in their own worksheets in Excel. This makes data models such as parent-child easier to model in Excel, and enables them to use powerful modeling tools such as PowerPivot in Excel.
Enabled for: Users by admins, makers, or analysts
Public Preview: Oct 2023
General Availability: Dec 2023
Feature Details
Prior to this release, developers who wanted to create an Excel report on a data model with multiple top-level data items, such as a parent-child relationship, had to do a lot of extra modeling in Excel to separate the two before they could use that data in the Excel layout.
With this release, the developer can set a new ExcelLayoutMultipleDataSheets property to True. The AL runtime will generate an Excel worksheet for each data item and place its data there.
Multiple sheets will be named #DataItemName, where DataItemName is the dataitem name in the report design. Adding new empty Excel layouts to the report uses the property value to determine the sheet structure.