New features coming soon to Excel layouts for Business Central

The release plan for Business Central just got updated and two new entries for the 2023 release wave 2 are now public. Both of them are about improvements to the Excel layout feature that gives the person who is working with the layout the ability to do more with less.

During October and November, the team has worked hard on getting the new features into our main branch and it is now ready for testing for people with access to insider builds (hint, hint, nudge, nudge...)

Make use of static and runtime metadata available in Excel layouts

The release plan entry https://learn.microsoft.com/en-us/dynamics365/release-plan/2023wave2/smb/dynamics365-business-central/make-use-static-runtime-metadata-excel-layouts

states two things in the Business value for the feature

1. This feature makes it easier for layout authors to access and use metadata in their reports. This means less coding in AL and allows for more self-service and greater efficiency in report creation.
2. Advanced layout authors can now call Business Central APIs from Power Query, enabling them to create refreshable reports.

Here are some examples of how you can use the new metadata in your layouts:

• No need to encode Company name and User in the report dataset or in parameters. Just grab them from the report request worksheet.
• The report request worksheet also contains Entra (AAD) id and environment id. You can use these from PowerQuery in Excel to call Business Central APIs and enrich the data available in the report outside what is present in the report dataset defined in AL.
• All report captions are available from the Captions worksheet, so no need to add them to the report dataset.

Translatable Excel layouts

The release plan entry https://learn.microsoft.com/en-us/dynamics365/release-plan/2023wave2/smb/dynamics365-business-central/translatable-excel-layouts

states this in the Business value for the feature

If a business operates in multiple countries or regions, it's helpful to be able to generate reports in multiple languages. ISVs can ship reports with Excel layouts where the Excel output can be translated into the user's language when they run the report.

Basically, this means that reports with Excel layouts can now be multi-language, just like what you know from RDL or Word layouts.

Standard Excel concepts such as

• Worksheet names
• Chart headers
• Pivot elements
• Slicers

are now translatable if the layout author use the new $$ syntax. At runtime when running the report for a user, the Business Central server will replace strings surrounded by $ characters with corresponding captions.

We hope to also release the first multi-language Excel reports with the base app very soon. Stay tuned to see that in action.

Reporting documentation for developers

Related to this work, we did a major makeover of the reporting documentation for developers. Everything there is to know about Business Central reporting should now be available from a single place in our documentation.

The new features will of course be added to this documentation when they have been released.

Check out the new report developer docs here: https://learn.microsoft.com/en-us/dynamics365/business-central/dev-itpro/developer/devenv-reports

(and please comment on things/topics you think are missing there. Or just do a PR to the docs repo)

Resources

Visit the new release plan entries here

• https://learn.microsoft.com/en-us/dynamics365/release-plan/2023wave2/smb/dynamics365-business-central/make-use-static-runtime-metadata-excel-layouts
• https://learn.microsoft.com/en-us/dynamics365/release-plan/2023wave2/smb/dynamics365-business-central/translatable-excel-layouts

The Powerpoint deck on aka.ms/bcexcelsamples has been updated with new slides that illustrate the new features.

Check out the new report developer docs here: https://learn.microsoft.com/en-us/dynamics365/business-central/dev-itpro/developer/devenv-reports

Signup to the newsletter (and/or spread the word)

That's it for now. Thanks for reading along. Do comment on things that resonated with you when reading the article.

If you liked the newsletter and think that others might benefit from it as well, please send them the signup link here:

https://www.dhirubhai.net/newsletters/6981923051273658368

All earlier posts are also available from there.