Creating an Excel Layout Report

Article
04/08/2022
3 minutes to read

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 additional views, filtering, and sorting on data. Such a layout designed by the end-user, can be imported and used as a new layout. The following steps will show how to create a basic report based on an Excel layout.

The example 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.

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

Create a simple Excel layout report

The following 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 does not 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 this is not present, it will be generated.

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';
        }
    }
}

Now, press 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 will get the same starting point. Then you can design the layout, save as a new layout, and include in your AL project.
Right-click the generated MyExcelContactList.xlsx file, and choose Reveal in File Explorer. This will open File Explorer.
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 is important to not change the dataset in Excel, only the layout.
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.
From the PivotTable Fields pane to the right, choose a suitable number of fields to add to the report.
Save the report and close the Excel window.
Back in Visual Studio Code, press Ctrl+F5 to compile and launch Business Central.
Now, to choose the changed report layout, search for the Report Layout Selection page, and then search for the Contact List (ID 5050) report.
In the Layout Type column, choose Excel, and then choose the Run Report from the action bar.
On the request page, choose the Download button, and once the report is downloaded, open it.
In Excel, you should now see the Contact List report as a pivot table, sorted as you specified in step 6.

Note

If the report layout is not generated, open the settings.json from Visual Studio Code. Use Ctrl+Shift+P, then choose Preferences: Open User Settings, locate the AL Language extension. Under Compilation Options, choose Edit in settings.json and add the following line:

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

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

Creating an Excel Layout Report

Create a simple Excel layout report

See also

Feedback