One of the reporting features I like the most in Dynamics 365 Business Central is the possibility to create Excel Layout reports. Excel Layout reports are essentially reports based on Excel workbooks, with all the fancy stuffs of Microsoft excel available.
With Excel Layouts, when you create a new report first you define the report dataset in code (dataitems, fields etc.) Then you design a layout with Type = Excel and here you can basically export all the data you need in a basic Excel sheet. The end user can then modify that layout by using all the available Excel features like formula, charts, sliders, pivots etc and upload the modified layout as a new layout that will be available at the next time he runs the report.
Every Excel layout file must have a worksheet called Data that defines which metadata fields from the dataset definition of the report object the layout uses (data contract). Please remember that:
- Metadata fields must be written in the first row of the Data worksheet, one in each
cell. - All metadata fields in the Data worksheet must exist as metadata fields in the
dataset definition of the report object. - You can’t rename fields in the Data worksheet. They must match metadata fields in
the dataset definition.
Excel Layout reports now have also a property called ExcelLayoutMultipleDataSheets that sets whether an Excel layout report 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.
Starting from the upcoming version 23.3 release, 3 new system worksheets (hidden by default) will be added to an Excel Layout by the server side:
- TranslationData: contains data that you can use in your layouts to provide multi-language strings that don’t exist as captions in the report object. This new sheet contains 3 columns called CaptionKey, Language and Value, where CaptionKey is an unique name that can be used in the layout to reference captions enclosed in $ characters ($mycaption$).
- CaptionData: contains data from column captions and report labels specified in the AL report object. This new sheet contains 2 columns called CaptionKey and Value, where CaptionKey includes field caption names for all dataset fields with IncludeCaption=true as well as label names in the labes section of the report. Values from this table can be used in the report layout by using the $tag$ notation.
- Aggregated Metadata: contains data from the report AL metadata, request metadata, request page options and filters in separated tables. More in details, we have the following tables:
- ReportMetadataValues: contains the metadata coming from the report object, like extension id, publisher, version, objectd id and name etc.
- ReportRequestValues: contains metadata coming from the report invocation, like tenant id, environment deatils, company details, user that started the report, language id etc.
- ReportRequestPageValues: contains metadata coming from the report request page.
- ReportFilterValues: contains metadata related to filters applied to the report when executing it. There will be one row for each active filter defined on the request page.
These new system sheets will be useful to retrieve context about the report execution and also with the data coming from the ReportRequestValues worksheet, the report layout author can now call Business Central APIs from PowerQuery in Excel and create refreshable Excel reports.
To create Excel Layouts reports that supports multiple languages you can now use the new CaptionData and TranslationData tables in the Excel layout. You can use ‘$tag$’ substitution for Excel elements in your worksheets. At report generation time, the Business Central server replaces $tag$ with the corresponding value defined in the TranslationData or CaptionData tables. If a tag
exists in both tables, data from the TranslationData table takes precedence. The tag name is case sensitive and unmatched elements will be left unmodified.
Be ready for these new changes then and start using Excel Layouts reporting at full power…
Stefano Demiliani 