Hi, Readers.
Dynamics 365 Business Central 2023 wave 2 (BC23) is generally available. More details: General availability: Dynamics 365 Business Central 2023 release wave 2 (BC23)
I will continue to test and share some new features that I hope will be helpful.
In this post, I would like to talk about how to set whether an Excel layout will render to multiple data sheets or in a single sheet named Data. This is not yet documented in the Business Central 2023 release wave 2 (BC23) release plan. But it is mentioned in AL Language extension changelog Version 12.0
Add report property ExcelLayoutMultipleDataSheets
https://marketplace.visualstudio.com/items/ms-dynamics-smb.al/changelog
Add a new boolean report property ExcelLayoutMultipleDataSheets that controls how Excel data sheets are created when using an Excel layout. Use this property to emit root data items to separate Excel sheets or to a single sheet named Data. Multiple sheets will be named #DataItemName, where DataItemName is the dataitem name used in the report design. Adding new empty Excel layouts to the report will use the property value to determine the sheet structure.
The default is to use a single sheet for all data.
Microsoft Excel report layouts are based on Excel workbooks (.xlsx files). With them, you can create reports that include familiar Excel features for summarizing, analyzing, and presenting data such as formulas, PivotTables, and PivotCharts. More details: Business Central 2022 wave 1 (BC20) new features: Excel layouts for BC Reports
In this wave, Microsoft has added a new property to Excel report layout, ExcelLayoutMultipleDataSheets property (default is false)
(property) ExcelLayoutMultipleDataSheets: Boolean
Sets whether an Excel layout will render to multiple data sheets or in a single sheet named Data.
Multiple sheets will be named Data_DataItemName, where DataItemName is the dataitem name used in the report design. There will be one sheet per root dataitem in the report object. The default is to use a single sheet for all data.
This is also mentioned in the documentation below:
Excel layout data contract in 2023 release wave 2 and later versions
Let me do a simple test.
I have created a simple report with two root-level DataItem and a child-level DataItem.
On the report request page, choose Send to… -> Microsoft Excel Document (data only), then choose OK.
If the ExcelLayoutMultipleDataSheets property is false, which is the default, a single sheet is used for all data (as described in the previous section).
Data sheet:
If the ExcelLayoutMultipleDataSheets property is true, the AL runtime generates an Excel worksheet for each data item and place its data there (only for root-level DataItem).
Customer sheet:
Vendor sheet:
We can make it easier to create an Excel layout through this new property. Give it a try!!!😁
PS:
1. There is a problem with this property in the BC23 Preview version, but not in the release version. In the BC23 Preview version, if ExcelLayoutMultipleDataSheets property is true, you will export an empty layout that contains no data.
2. If there is an Excel layout file in the previous format before you change the propertiy, the following error will be prompted when publishing or packaging.
error AL0444: Malformed Excel report layout at location ‘MyExcelReport.xlsx’. The issue is: ‘The Excel layout must contain a sheet named ‘Data’.’.
3. Because the feature Send to Microsoft Excel Document (data only) is also available for other layout types, this property is not limited to Excel layout. More details: Save report dataset to Excel from the request page (Report Inspector)
4. My test code:
report 50103 "Customer Vendor List"
{
Caption = 'Customer Vendor List';
DefaultLayout = Excel;
ExcelLayout = 'MyExcelReport.xlsx';
ApplicationArea = All;
UsageCategory = ReportsAndAnalysis;
ExcelLayoutMultipleDataSheets = true;
dataset
{
dataitem(Customer; Customer)
{
column(CustNo_; "No.")
{
}
column(CustName; Name)
{
}
column(CustSales__LCY_; "Sales (LCY)")
{
}
column(CustBalance__LCY_; "Balance (LCY)")
{
}
dataitem("Ship-to Address"; "Ship-to Address")
{
DataItemLink = "Customer No." = field("No.");
column(Code; Code)
{
}
column(Name; Name)
{
}
}
}
dataitem(Vendor; Vendor)
{
column(VendNo_; "No.")
{
}
column(VendName; Name)
{
}
column(VendPurchases__LCY_; "Purchases (LCY)")
{
}
column(VendBalance__LCY_; "Balance (LCY)")
{
}
}
}
}Release Plan has been updated:
Define multi-worksheet datasets for Excel reports
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.
https://learn.microsoft.com/en-us/dynamics365/release-plan/2023wave2/smb/dynamics365-business-central/define-multi-worksheet-datasets-excel-reports
END
Hope this will help.
Thanks for reading.
ZHU
コメント