Define multi-worksheet datasets for Excel reports
|Enabled for||Public preview||General availability|
|Users by admins, makers, or analysts||Oct 2, 2023||Nov 1, 2023|
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.
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: