Creating an Excel layout report

APPLIES TO: Business Central 2022 release wave 1 and later

When you create a new report, there are two main tasks to consider. First, you define the report dataset of data items and columns. Then, you design the report layout. With the Excel report layout, you can create a basic report that prints a dataset and leave it up to the end-user to further modify it by using the full palette of capabilities in Excel such as sliders, diagrams, charts, pivot tables, and PowerQuery to design the report. This offers flexibility and freedom for the end-user, being able to change the look and feel of a report, adding more views, filtering, and sorting on data. Such a layout designed by the end-user, can be imported and used as a new layout.

For more information about the report object, see Report Object and for report extension objects, see Report Extension Object.

How Excel layouts work

When designing an Excel layout, you need to know the following information:

• How the layout is connected to the dataset definition, also known as the data contract.
• How the layout file is validated when importing it into Business Central.
• How the Business Central server merges the layout with data when running the report.

Excel layout data contract in 2023 release wave 1 and earlier versions

Every Excel layout file must have a worksheet called Data. This worksheet has one purpose: defining which metadata fields from the the dataset definition of the report object the layout uses, which is sometimes also called the data contract between the layout file and the report dataset definition. The data contract consists of the following rules:

1. Metadata fields must be written in the first row of the Data worksheet, one in each cell.
2. All metadata fields in the Data worksheet must exist as metadata fields in the dataset definition of the report object.
3. You can't rename fields in the Data worksheet. They must match metadata fields in the dataset definition.
4. You don't have to use all metadata fields in the dataset definition in the Data worksheet.

For more information about the data contract, see Understanding Excel layouts.

Excel layout data contract in 2023 release wave 2 and later versions

APPLIES TO: Business Central 2023 release wave 2 (version 23) and later

The ExcelLayoutMultipleDataSheets property allows you to work with reports that render multiple worksheets for the report data when the dataset has multiple data items. By setting the property to true, the AL runtime generates an Excel worksheet for each data item and places its data there. Otherwise, if the property is false, which is the default, a single sheet is used for all data (as described in the previous section).

Each of the multiple sheets is named #DataItemName, where DataItemName is the name given to the dataitem in the report design. When new empty Excel layouts are added to the report, the property is used to determine the sheet structure.

With data in multiple worksheets, the report layout can now easily include data models defined with the PowerPivot feature in Excel.

System Excel sheets

APPLIES TO: Business Central 2023 release wave 2 (version 23) and later

Starting from version 23.3, the Business Central server adds three system worksheets that you can use for easier layouts and for creating translatable Excel layouts:

• TranslationData
• CaptionData
• Aggregated Metadata

All three system worksheets are hidden by default and the data in these worksheets is organized in Excel tables from which you can reference individual fields using Excel table formulas.

TranslationData worksheet definition (table TranslationData)

The TranslationData worksheet contains data that you can use in your layouts to provide multi-language strings that don't exist as captions in the report object. When the Business Central server generates the Excel report, it can use data from the TranslationData table as part of translating strings to the user's language.

The data in the TranslationData worksheet is located in the Excel table TranslationData with three columns CaptionKey, **Language, and Value:

CaptionData worksheet definition (table CaptionData)

The CaptionData worksheet contains data from column captions and report labels specified in the AL report object. When the Business Central server generates the Excel report, it can use data from the CaptionData table as part of translating strings to the users language.

Column captions are generated for all dataset fields with 'IncludeCaption = true'.

The data in the CaptionData worksheet is located in the Excel table CaptionData with two columns CaptionKey and Value:

Aggregated metadata sheet definition (multiple tables)

The Aggregated metadata worksheet contains data from the report AL metadata, request metadata, request page options and filters. Each type of data is available in its own Excel table:

• ReportMetadataValues
• ReportRequestValues
• ReportRequestPageValues
• ReportFilterValues

ReportMetadataValues table

The ReportMetadataValues table contains metadata from the report object.

ReportRequestValues table

The ReportRequestValues table contains metadata from the report request (the report invocation that created the document).

ReportRequestPageValues table

The ReportRequestPageValues table contains metadata from the report request page when the report request was issued.

The table has two columns Request Page Option and Request Page Option Value. It contains all Key-Value pairs of entries from request page options.

ReportFilterValues table

The ReportFilterValues table contains metadata from the report request page when the report request was issued.

The table has two columns Filter and Filter Value. It contains all Key-Value pairs of filters from the request page.

The actual filter format is '<DataItemName>::<Source Table Caption>::<FilterGroup>::<Field Caption>'.

There will be one row for each active filter defined on the request page.

Named formulas

In Business Central 2024 release wave 2 and later, when you create Excel report layout workbooks, either from VSCode or when you get a new template from the request page, Excel report layout workbooks include named formulas for easier lookups. Instead of having to write complicated VLOOKUP or XLOOKUP formulas, report authors can use named formulas, such as ReportRequest.Date or ReportMetaData.ReportHelpLink.

To see all available formulas in an Excel workbook, in the Defined Names group, choose Formulas, and then Name Manager.

Translating Excel sheets in 2023 release wave 2 and later versions

APPLIES TO: Business Central 2023 release wave 2 (version 23) and later

Starting in version 23.3, you can create Excel layouts that work for multiple languages.

There are two ways to translate strings:

• Using report captions and labels or per-layout translation texts with Excel lookup functions.
• Using '$tag$' substitution.

If strings are available in field captions or labels in the report object, you can use Excel cell lookups to the data in the CaptionData table. This table is populated by the Business Central server when the report is generated and contains the caption strings for fields and report labels from the report object. Use the same technique with the TranslationData table that holds per-layout translation texts. For these texts, use the Format Region field from the ReportRequestValues table in your Excel lookups.

It's also possible to 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 are left unmodified.

The following Excel elements can be translated:

• Worksheet name
• Text data in a cell
• Chart title
• Chart axis titles (available from version 24.1)
• Pivot table name (right-click on the pivot table, choose PivotTable Options, then add your tag in the PivotTable Name field)
• Pivot field name (right-click on the field in the pivot table, choose Field Settings, then add your tag in the Custom Name field)
• Slicer name (right-click on the slicer, choose Slicer Settings, then add your tag in the Caption field)

Worksheet references with translation tags will be updated in cell formulas as well to maintain proper data references in the final document.

Validating an Excel layout

When importing an Excel layout as part of an app or when a user uploads an Excel layout file, Business Central Server does the following operations:

1. Loads the Excel layout file and validates whether the file is indeed an Excel file (.xlsx) and that it isn't password protected. If the file isn't a valid Excel file, Business Central Server rejects the layout.
2. Reads the metadata fields present in the Data worksheet (the content of the data contract). If no Data worksheet exists, Business Central Server rejects the layout.
3. Removes any other data present in the Data worksheet.
4. Validates whether the metadata fields present in the Data worksheet are all present as metadata fields in the dataset definition of the report object. In other words, Business Central Server checks that the data contract is valid. If it isn't, Business Central Server rejects the layout.

Running a report with an Excel layout

When a report with an Excel layout is run, Business Central Server does the following operations:

1. Generates the dataset as specified in the dataset definition in the report object and modified by the filters and options from the request page.
2. Loads the Excel layout file.
3. Inserts the data into the Data table in the Data worksheet in the Excel layout file.
4. Provides the merged Excel workbook to the user for download or view in Excel online if enabled by the tenant administrator. For more information about viewing Excel outputs in Excel online, visit Save Excel workbooks and report files in OneDrive.

Changing the data contract after adding new columns to the report dataset

If you add new columns to the report dataset after you've created Excel layouts, the data contracts in the layouts don't get updated automatically. But you don't need to recreate the layouts from scratch, you can add the new columns manually to the header line in the data contract worksheets.

For a report developer working with AL code, maybe the simplest way to get the new column names is from the AL code for the report object. For a report developer working just in Excel, the simplest way to get the new column names is to run the report in Business Central and on the request page, then choose the Microsoft Excel Document (data only) option. This will give you an Excel workbook with all the columns in the data contract.

Report labels in Excel layouts

Report labels are used by report layouts as, for example, the heading for a field in a table, the title for a chart, or the title for the report itself.

Starting in version 23.3, report labels defined in the Labels section of the report object and captions included on dataitem columns using the IncludeCaption property are available in the CaptionData worksheet in Excel.

For more information about labels, see Report labels.

Formatting data in Excel layouts

It's often desirable to format data in a report in a different way than how the same data appears in tables, queries, or pages. For example, if a decimal field in a table has precision of five digits, the same level of precision might not be needed in the report.

Specifically for Excel layouts, there are many ways to control formatting of data elements directly in Excel. For more examples on how to format data in Excel, see:

• Formatting dates in Excel
• Formatting numbers in Excel

Drillthrough to Business Central from an Excel layout

With drillthrough in an Excel layout, you can create hyperlinks back into Business Central from Excel cells. When the report user selects the cell, they drillthrough to the target page to get details that are filtered to that context. To implement a drillthrough link, you need to know, which page to open and also construct, which filters to apply to that page. You filter the data that is displayed in the page by using the filter URL parameter. The filter parameter lets you display specific records from the underlying table of the page.

For more information, see Web URL syntax.

Using fonts in Excel layouts

The online version of Business Central contains preinstalled fonts on the servers that can be used when generating reports. You can use any of these fonts in your layout.

For more information, see Available fonts in Business Central online.

Using Office document themes in Excel layouts

Office document themes make it easy to coordinate colors, fonts, and graphic formatting effects across your Word and Excel layouts and quickly update them.

For more information, see Change a theme and make it the default in Word or Excel.

Best practices for Excel layouts

Excel functionality

When doing lookups inside the Excel workbook, use the XLOOKUP function instead of VLOOKUP. For more information, see XLOOKUP function.

Consider using Power Query as a powerful tool to clean and transform data (for example, use it to set the correct data types). Power Query is available in all Excel versions since Excel 2016. The connectors offered by Excel versions differs as stated in this support article: Power Query data sources in Excel versions. For more information, see Power Query in Excel.

Table formulas in Excel are a powerful way to work on table data. For more information, see Use calculated columns in an Excel table.

Worksheet naming and location

Arrange worksheets in the order you think will be most useful for users so that they don't have to scroll when using the report.

Good worksheet names help users quickly get an overview of the information they can obtain by navigating to the worksheet.

An Excel worksheet can be up to 31 characters long.

One or multiple worksheets?

In contrast to a report designed for print or pdf, an Excel report typically consists of multiple worksheets, each of which is designed for a different purpose. Some common types of worksheets are:

• Overview dashboard
• Pivot table
• Table
• Print-friendly
• About the report

There's no technical limit to the number of worksheets in a report, but users probably prefer limited number of worksheets. You can develop multiple Excel layouts for the same report, so maybe design for a specific persona.

Overview dashboard worksheet

Excel has visuals such as charts and maps, sparklines, and slicers, that can be used for creating dashboards known from Business Intelligence products such as Power BI. You can use these visuals to create dashboard worksheets that let users interact with the data and get visually appealing graphs showing trends or top 10 numbers.

Pivot table worksheet

Use a pivot table worksheet to give users a way to interact with the dataset. Typically, only one pivot worksheet in the report is needed (unless you want to offer different ready-made analysis). If you add multiple ready-made pivot table worksheets, consider naming them X by Y, such as Customers by Item or Sales by Region.

Table worksheet

A table worksheet can be used to display a specific view on the dataset, maybe only showing a subset of the columns and maybe even with some added computed columns.

Print-friendly worksheet

Some users might want to print the report, but this shouldn't force you to design all worksheets to be print-friendly. Instead, consider having one or more worksheets that has been optimized for print, maybe one for horizontal and one for landscape paper orientation.

About the report worksheet

Users aren't always 100% sure how your report can be used and for whom it was designed. Consider always having an About the report worksheet that explains:

• What the report is about and maybe also for which persona.
• A description for each worksheet that explains what the users can do here.
• Maybe also add See also links to documentation in case the user wants to learn more.

Example: Create a simple Excel layout report

The following steps show how to create a basic report based on an Excel layout. The example also illustrates how compilation triggers a starter template for the Excel layout. If an existing layout is referenced with the LayoutFile property, the layout is validated based on the schema of the report dataset.

The example extends the Contact List report only by adding a rendering section, which adds a new Excel layout to the list of options for printing the Contact List report. The layout doesn't yet exist, but will be generated based on the existing report dataset for the report and can then be modeled by using Excel reporting capabilities. The example uses the Type Property to set the type of report to Excel and it uses the LayoutFile Property to specify the name of the file that contains the Excel layout. If LayoutFile property isn't present, it will be generated.

1. Create a new report extension of the Contact List page by adding the following lines of code:

   reportextension 50101 MyExtContactList extends "Contact - List"
   {
       rendering
       {
           layout(LayoutExcel)
           {
               Type = Excel;
               LayoutFile = 'MyExcelContactList.xlsx';
           }
       }
   }
   

2. Now, select Ctrl+Shift+P, and then choose AL: Package. The MyExcelContactList.xlsx will be generated, as you can see in the right pane of Visual Studio Code.

   Tip

   Another way of generating the data set to build a layout on, is to run a report in Business Central and on the request page, then choose the Microsoft Excel Document (data only) option, and you'll get the same starting point. Then you can design the layout, save as a new layout, and include in your AL project.

3. Right-click the generated MyExcelContactList.xlsx file, and choose Reveal in File Explorer. This step opens File Explorer.

4. Choose the MyExcelContactList.xlsx file in File Explorer and open it in Excel.
   Excel now opens and you should see the dataset of the Contact List. Note that it's important to not change the dataset in Excel, only the layout.

5. In Excel, go to the Insert tab, choose PivotTable, and then choose From Table/Range with the default options of Data and New worksheet. Choose the OK button.

6. From the PivotTable Fields pane to the right, choose a suitable number of fields to add to the report.

7. Save the report and close the Excel window.

8. Back in Visual Studio Code, select Ctrl+F5 to compile and launch Business Central.

9. Now, to choose the changed report layout, search for the Report Layout Selection page, and then search for the Contact List (ID 5050) report.

10. In the Layout Type column, choose Excel, and then choose the Run Report from the action bar.

11. On the request page, choose the Download button, and once the report is downloaded, open it.

12. In Excel, you should now see the Contact List report as a pivot table, sorted as you specified in step 6.

"al.compilationOptions": {
  "generateReportLayout": true
}

It's possible to specify multiple layouts for a report. For more information, see Defining Multiple Report Layouts.

See also

Report Design Overview
Report Object Creating a Word Layout Report
Creating an RDL Layout Report
Defining Multiple Report Layouts
ExcelLayout Property
ExcelLayoutMultipleDataSheets Property
LayoutFile Property
Save Excel workbooks and report files in OneDrive
Understanding Excel layouts
Available Fonts in Business Central online