The preview environment for Dynamics 365 Business Central 2022 release wave 1 (BC20) is available. Learn more: Link
I will continue to test and share some new features that I hope will be helpful.
Users can now use Excel to create and edit layouts for reports.
Excel layouts work the same way as Word and RDLC layouts in that they can be edited and saved back again into Business Central.
With Excel layouts for Business Central reports, users can now create and edit report layouts simply by using the full palette of capabilities in Excel such as sliders, diagrams, charts, and pivot tables.
To create an Excel layout from scratch, open the report request page, and then run the report with the option Excel document (data only). The report generates an Excel file with sample data and the fields that are available in the report definition. Then you add your layout to additional tabs in the Excel file. To test the layout, simply import it as a custom layout in Business Central. After the system has validated that the layout is valid for use, you can now go to the request page and run the report with your new layout.https://docs.microsoft.com/en-us/dynamics365-release-plan/2022wave1/smb/dynamics365-business-central/new-capabilities-financial-reporting-account-schedules
In Business Central 2022 wave 1 (BC20), Microsoft brought us a new layout type, no code or low code reporting with Excel layouts.
PS: In BC19.5
How to use it? We only need three steps, let’s see more details.
Using Request Page to get (raw) data in Excel
Open the report request page, and then choose Send to…
For example, Standard Sales – Order Conf. (1305)
Select Microsoft Excel Document (data only), then choose OK.
Open the downloaded excel.
PS: This was a new feature in Business Central 2021 wave 1 (BC18).
More details: Get report (raw) data in Excel from the request page
Save report dataset to Excel from the request page (Report Inspector)
In this Excel file you will see two sheets. “Data” is that contains captions and record values from your dataset.
And “Report Metadata” contains Filters and Report Properties.
Please do not change any field names and table structure of the Data sheet.
PS: In BC19.5, the name of the Data sheet is the name of the Report, and it has been unified as “Data” since BC20.
Adding layout in other sheets
Then add your layout to additional tabs in the Excel file. Now you can use all the features of Excel, including excel formulas, charts, PowerQuery, Power Pivot, Data Insights and more.
We can reference fields with Excel formulas such as Data[Field1] or Data!A2.
More details about Using structured references with Excel tables.
To make it easier to understand, I created a simple report for printing a single sales order report.
PS: Although you cannot translate the Header name in the Data (Field names in the Data sheet are the name in the dataset), you can translate the Label contents as before, and then take the value in the Label when getting the data, which will be translated.
Or you can move values other than headers to other sheets and add translations manually. Then create charts and other information you need from the new Sheet.
Saving as custom layout in BC
Open the Report Layouts page.
PS: This is a new page in BC20.
Choose New Layout.
Select the Report ID, enter the Layout Name and Description. Then select Excel for the Format Options.
Choose your Excel layout file.
PS: If you want to update the layout, please choose Replace Layout on the Report Layouts page.
Now you can choose Set Default here.
Or go to Report Layout Selection page, to select the new Excel layout.
After applying the new Excel Layout, you will not be able to see the Print and Preview buttons on the request page, replaced by the Download button. So Excel layout cannot be printed directly.
The Excel File:
PS: In Send to…, in addition to Schedule, there are only two options, XML Document and Microsoft Excel Document (data only).
If you choose Microsoft Excel Document (data only), this will download a completely new file.
In conclusion, RDLC layout is too complicated for most consultants. And Word layout have many limitations, for example, we cannot use any formulas like in RDLC. Now Excel layout is coming, Excel is the tool we already know and like. Although there may be some shortcomings now, I think this can already replace many of the original reports. Give it a try!!!😁
Update 2022/04/12: Microsoft added some tips and tricks to get your imagination started. Also added a Powerpoint deck if you want to do internal/external presentations.
Update info from Dynamics 365 Business Central Launch Event 2022 Release Wave 1:
Hope this will help.
Thanks for reading.