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

Business value

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.

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.

Example of how to model data with PowerPivot in the Excel layout.

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:

Example of how to model data with PowerPivot in the Excel layout.

See also

Creating an Excel layout report (docs)