Business Central 2022 wave 1 (BC20) new features: Excel layouts for BC Reports

Dynamics 365 Business Central

Hi, Readers.
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.

Use Excel to design layouts for reports:

Business value:

Users can now use Excel to create and edit layouts for reports.

Feature details:

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, VBA 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.

Sample file:

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 OK.

Choose your Excel layout file.

Added successfully.

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.

Test

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:

Test Video:

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.
https://github.com/microsoft/BCTech/tree/master/samples/Excel

Update info from Dynamics 365 Business Central Launch Event 2022 Release Wave 1:

END

Hope this will help.

Thanks for reading.

ZHU

コメント

タイトルとURLをコピーしました