Excel Report Layouts, new in BC 2022 Wave 1

With Business Central 2022 Wave 1 (aka Version 20), we can now create report layouts with Excel. In this video, I take a first look at how you can create Excel reports in AL. Check it out:

https://youtu.be/VNl6L2LAgNM

In this video, Erik takes a first look at one of the most exciting features in Business Central 2022 Wave 1 (version 20): the ability to use Excel as a layout engine for reports. He walks through creating an Excel report layout from scratch, explores how data items are rendered, and discovers some important limitations and workflow challenges that developers should be aware of.

Getting Started: Creating an Excel Report Layout

Erik begins by creating a brand new extension and building a report from the ground up. The key properties that enable Excel layouts are straightforward — you specify an Excel file for the layout and set the default layout type to Excel.

However, compiling an empty report immediately produces an error:

“Malformed Excel report layout at location Excel — the issue is a report must have at least one column to support Excel layouts.”

Fair enough. The report needs a dataset with at least one column before it can generate an Excel layout.

Building the Dataset

Erik adds a Customer data item with several common columns: number, name, address, city, post code, phone number, and some financial fields like Sales (LCY), Balance (LCY), and Balance Due (LCY). Here’s the initial structure of the report:

report 50100 "Test Excel"
{
    Caption = 'Testing Excel';
    ExcelLayout = 'SuperExcelLayout.xlsx';
    DefaultLayout = Excel;
    dataset
    {
        dataitem(Customer; Customer)
        {
            column(No_Customer; "No.")
            {
            }
            column(Name_Customer; Name)
            {
            }
            column(Address_Customer; Address)
            {
            }
            column(City_Customer; City)
            {
            }
            column(PostCode_Customer; "Post Code")
            {
            }
            column(PhoneNo_Customer; "Phone No.")
            {
            }
            column(SalesLCY_Customer; "Sales (LCY)")
            {
            }
            column(BalanceLCY_Customer; "Balance (LCY)")
            {
            }
            column(BalanceDueLCY_Customer; "Balance Due (LCY)")
            {
            }
        }
    }
}

After compiling, the compiler generates an Excel file automatically. Opening it reveals a single sheet called Data containing all the column headers from the dataset.

Customizing the Excel Layout

The idea behind Excel report layouts is that you can add your own sheets and formulas on top of the generated data. Erik creates a new sheet called “Report” and adds summary formulas:

  • Total Sales — a SUM formula referencing the Sales (LCY) column on the Data sheet
  • Balance — a SUM of the Balance (LCY) column
  • Total Due — a SUM of the Balance Due (LCY) column

After saving the modified Excel file and deploying the extension, the report downloads as an .xlsx file (the filename comes from the report’s caption). The Report sheet correctly shows the totals calculated from the customer data, and the Data sheet contains all the customer records.

The Layout Regeneration Problem

Here’s where Erik hits the first significant workflow issue. When he adds new columns to the dataset (Country/Region Code and Profit (LCY)), the existing Excel layout file is not updated. The new columns simply don’t appear in the output.

The only way to get the new columns is to delete the existing layout file and let the compiler regenerate it. But this means losing all customizations — the Report sheet with its formulas, any formatting, everything.

Erik checks the documentation but finds no solution to this problem. The conclusion is clear: you need to get your dataset right before investing heavily in layout customization, or be prepared to copy-paste your work after every dataset change.

Multiple Data Items: Everything Gets Flattened

Erik then explores what happens with multiple data items by adding a Vendor data item alongside the Customer data item:

dataitem(Vendor; Vendor)
{
    column(No_Vendor; "No.")
    {
    }
    column(Name_Vendor; Name)
    {
    }
    column(Address_Vendor; Address)
    {
    }
    column(BalanceLCY_Vendor; "Balance (LCY)")
    {
    }
    column(BalanceDueLCY_Vendor; "Balance Due (LCY)")
    {
    }
}

His naive expectation was that each data item would get its own sheet — a Customer sheet and a Vendor sheet. Instead, everything is mangled together into a single Data sheet. The vendor rows show up with null values in all the customer-specific columns, and vice versa. While the seven vendor records do appear, their customer fields are all null.

Nested Data Items: The Flattening Gets Worse

Taking it a step further, Erik adds Customer Ledger Entries as a child data item under Customer:

dataitem("Cust. Ledger Entry"; "Cust. Ledger Entry")
{
    DataItemLink = "Customer No." = Field("No.");

    column(CustomerNo_CustLedgerEntry; "Customer No.")
    {
    }
    column(PostingDate_CustLedgerEntry; "Posting Date")
    {
    }
    column(DocumentType_CustLedgerEntry; "Document Type")
    {
    }
    column(DocumentNo_CustLedgerEntry; "Document No.")
    {
    }
    column(DocumentDate_CustLedgerEntry; "Document Date")
    {
    }
    column(Amount_CustLedgerEntry; Amount)
    {
    }
    column(AmountLCY_CustLedgerEntry; "Amount (LCY)")
    {
    }
    column(CurrencyCode_CustLedgerEntry; "Currency Code")
    {
    }
    column(Open_CustLedgerEntry; Open)
    {
    }
}

This creates a fully flattened output where parent and child data are repeated across rows. The customer’s Sales and Balance values appear on every ledger entry row for that customer. This makes it extremely difficult to create meaningful summaries in Excel because you can’t simply SUM a column without double-counting parent-level values.

Erik also noticed an interesting quirk: Date and Boolean fields from child records appear to have their values copied/repeated onto subsequent rows, even when those rows belong to different data items. Number and text fields show as null, but dates and booleans carry forward — a subtle but potentially confusing behavior.

The Final Report Structure

Here’s the complete report AL file that Erik ended up with after all his experimentation:

report 50100 "Test Excel"
{
    Caption = 'Testing Excel';
    ExcelLayout = 'SuperExcelLayout.xlsx';
    DefaultLayout = Excel;
    dataset
    {
        dataitem(Customer; Customer)
        {
            column(No_Customer; "No.") { }
            column(Name_Customer; Name) { }
            column(Address_Customer; Address) { }
            column(City_Customer; City) { }
            column(Country_Region_Code; "Country/Region Code") { }
            column(PostCode_Customer; "Post Code") { }
            column(PhoneNo_Customer; "Phone No.") { }
            column(SalesLCY_Customer; "Sales (LCY)") { }
            column(BalanceLCY_Customer; "Balance (LCY)") { }
            column(BalanceDueLCY_Customer; "Balance Due (LCY)") { }
            column(Profit__LCY_; "Profit (LCY)") { }

            dataitem("Cust. Ledger Entry"; "Cust. Ledger Entry")
            {
                DataItemLink = "Customer No." = Field("No.");
                column(CustomerNo_CustLedgerEntry; "Customer No.") { }
                column(PostingDate_CustLedgerEntry; "Posting Date") { }
                column(DocumentType_CustLedgerEntry; "Document Type") { }
                column(DocumentNo_CustLedgerEntry; "Document No.") { }
                column(DocumentDate_CustLedgerEntry; "Document Date") { }
                column(Amount_CustLedgerEntry; Amount) { }
                column(AmountLCY_CustLedgerEntry; "Amount (LCY)") { }
                column(CurrencyCode_CustLedgerEntry; "Currency Code") { }
                column(Open_CustLedgerEntry; Open) { }
            }
        }
        dataitem(Vendor; Vendor)
        {
            column(No_Vendor; "No.") { }
            column(Name_Vendor; Name) { }
            column(Address_Vendor; Address) { }
            column(BalanceLCY_Vendor; "Balance (LCY)") { }
            column(BalanceDueLCY_Vendor; "Balance Due (LCY)") { }
        }
    }
}

Key Takeaways and Observations

  1. All data items are flattened into a single “Data” sheet. There is no automatic separation of data items into separate sheets. This was Erik’s biggest surprise and complicates Excel layout design significantly.
  2. The layout file is not updated when the dataset changes. You must delete the existing layout file and let the compiler regenerate it, losing all customizations in the process.
  3. Dataset design must be different for Excel layouts. The flat output means you need to think carefully about how parent-child relationships and multiple data items will look when combined into a single table. You may need to add control columns to help Excel identify where one dataset ends and another begins.
  4. Excel formulas like VLOOKUP and INDEX become essential. To work with the flattened data effectively, you’ll need to use more advanced Excel functions to locate and aggregate the specific data you need.
  5. Date and Boolean fields have unexpected carry-forward behavior across rows from different data items, while number and text fields show as null.
  6. No preview is available — the report can only be downloaded or sent, not previewed in the browser.

Conclusion

Excel report layouts in Business Central 2022 Wave 1 are a welcome addition, but this first look reveals that the feature has some significant workflow and design challenges. The single-sheet flattened output means that designing datasets for Excel layouts requires a fundamentally different approach than designing for Word or RDLC layouts. The inability to update the layout file when the dataset changes creates a painful development loop. Erik suggests that Microsoft could improve the workflow by allowing dataset updates to be merged into existing layout files rather than requiring a full regeneration. Despite these challenges, the ability to leverage Excel’s powerful formula engine for report output opens up interesting possibilities — particularly for financial reports and data analysis scenarios where Excel is the natural tool of choice.

Picture of hougaard

hougaard