Define multi-worksheet datasets for Excel reports
Important
This content is archived and is not being updated. For the latest documentation, go to What's new or changed in Business Central. For the latest release plans, go to Dynamics 365, Power Platform, and Cloud for Industry release plans.
| 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.
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:
