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.
The default is to use a single sheet for all data.
With data in multiple worksheets, the report layout can easily include data models defined with the PowerPivot feature in Excel:
New Functionality In Microsoft Dynamics 365 Business Central 2023 Wave 2
What should we write about next?
If there is a topic which fits the typical ones of this site, which you would like to see me write about, please use the form, below, to submit your idea.