Tag: Excel

Introducing ExcelLayoutMultipleDataSheets in Business Central 2025 Wave 1

With the release of Business Central 2025 Wave 1, business central continues to enhance developer and user experience in reporting and data analysis. One such powerful addition is the new property: ExcelLayoutMultipleDataSheets. This feature addresses a long-standing limitation in Excel export scenarios—allowing multiple datasets to be exported into separate sheets within a single Excel workbook.

What is ExcelLayoutMultipleDataSheets?

The ExcelLayoutMultipleDataSheets property is a new setting introduced for report objects that use Excel layouts. It enables developers to bind multiple data items or datasets to different worksheets in an Excel layout file (.xlsx), making reports more organized and structured when exported.

🧩 Structured Reports
Separate sheets for different datasets make it easier for business users to navigate complex reports—such as Sales Orders on one sheet, Customer Info on another, and Totals on a summary sheet.

🛠️ Developer Control
You can name your data items and match them to sheet names in your Excel layout. This gives you more granular control and reduces the need for workarounds.

How to Use ExcelLayoutMultipleDataSheets

report 50100 MyMultiSheetReport
{
    UsageCategory = ReportsAndAnalysis;
    ApplicationArea = All;
    DefaultRenderingLayout = MyExcelLayout;
    ExcelLayoutMultipleDataSheets = false; // Global setting is to use a single sheet

    dataset
    {
        dataitem(Customer; Customer)
        {
            column(CustomerNo; "No.") { }
            column(CustomerName; Name) { }
        }
        dataitem(Vendor; Vendor)
        {
            column(VendorNo; "No.") { }
            column(VendorName; Name) { }
        }
    }

    rendering
    {
        layout(MyExcelLayout)
        {
            Type = Excel;
            ExcelLayoutMultipleDataSheets = true; // Override for this specific layout
        }
    }
}

In this example, even though the global ExcelLayoutMultipleDataSheets property for the report is set to false, the MyExcelLayout will render the output with two separate worksheets:

  • Data_Customer containing the customer data.
  • Data_Vendor containing the vendor data.

If the ExcelLayoutMultipleDataSheets property within the MyExcelLayout definition was set to false (or not specified), both datasets would be combined into a single “Data” sheet in the Excel output.

The enhancement of the ExcelLayoutMultipleDataSheets property in Business Central Wave 1 2025 offers developers greater flexibility and control over Excel report layouts. By enabling the creation of multi-sheet Excel files at the layout level, you can deliver more user-friendly and better-organized reports, ultimately empowering your users to gain deeper insights from their Business Central data.

Stay tuned for more ….

Report.TargetFormat() Method in Business Central 2025 Wave 1

With the release of Microsoft Dynamics 365 Business Central 2025 Wave 1, Microsoft has continued to improve the AL language and reporting capabilities. One notable addition for developers working with reports is the new Report.TargetFormat() method. This small yet powerful enhancement gives developers more control and visibility over how reports are executed, especially in scenarios involving automation, scheduling, or integration.

The Report.TargetFormat() method allows you to set the target output format for a report at runtime. This is particularly useful in scenarios where the desired output format isn’t static or needs to be determined based on user preferences, system configurations, or specific business logic.

Imagine you have a page action that allows users to export a list of customers. Instead of having separate actions for different formats, you can use Report.TargetFormat() to dynamically generate the output in Excel.

pageextension 50100 CustomerListExt extends "Customer List"
{
    actions
    {
        addfirst(Processing)
        {
            action(ExportToExcel)
            {
                Caption = 'Export to Excel';
                Image = ExportExcel;
                trigger OnAction()
                var
                    CustomerListReport: Report "Customer List";
                    ReportSettings: Record "Report Settings";
                begin
                    ReportSettings.TargetFormat := Enum::"Report Format"::Excel;
                    CustomerListReport.Run(ReportSettings);
                end;
            }
        }
    }
}

In this example, before running the “Customer List” report, we set the TargetFormat property of a ReportSettings record to Excel. When the report is executed using Run(ReportSettings), it will automatically be generated as an Excel file.

Consider a scenario where you need to automatically generate and archive a sales order confirmation as a PDF after the order is posted. You can achieve this within a codeunit.

codeunit 50101 SalesOrderProcessing
{
    procedure GenerateConfirmationPDF(SalesHeaderRec: Record "Sales Header")
    var
        SalesConfirmationReport: Report "Sales - Order Conf.";
        ReportSettings: Record "Report Settings";
    begin
        ReportSettings.TargetFormat := Enum::"Report Format"::PDF;
        SalesConfirmationReport.SetTableView(SalesHeaderRec);
        SalesConfirmationReport.Run(ReportSettings);
        // Code to archive the generated PDF can be added here
    end;
}

Here, within the GenerateConfirmationPDF procedure, we explicitly set the TargetFormat to PDF for the “Sales – Order Conf.” report. When the report is run, it will be generated directly as a PDF document.

You can create a more user-friendly experience by presenting users with a choice of output formats before running a report.

pageextension 50102 ItemListExt extends "Item List"
{
    actions
    {
        addfirst(Processing)
        {
            action(ExportItemData)
            {
                Caption = 'Export Item Data';
                Image = Export;
                trigger OnAction()
                var
                    ItemListReport: Report "Item List";
                    ReportSettings: Record "Report Settings";
                    ExportFormat: Option "PDF","Excel","CSV";
                begin
                    ExportFormat := Dialog.Confirm('Choose Export Format:', true, ExportFormat);
                    case ExportFormat of
                        ExportFormat::PDF:
                            ReportSettings.TargetFormat := Enum::"Report Format"::PDF;
                        ExportFormat::Excel:
                            ReportSettings.TargetFormat := Enum::"Report Format"::Excel;
                        ExportFormat::CSV:
                            ReportSettings.TargetFormat := Enum::"Report Format"::CSV;
                    end;
                    ItemListReport.Run(ReportSettings);
                end;
            }
        }
    }
}

The Report.TargetFormat() method is a subtle yet powerful addition to the AL reporting toolbox in Business Central 2025 Wave 1. It unlocks a new level of flexibility for report behavior and presentation, enabling developers to create more intelligent, format-aware reporting solutions.

Stay tuned for more.

How to use Excel to update data in Business Central.

With the release of Business Central Wave 2 2021 aka BCV19 lot of new features has been added to make life easier of developer and/or customers.

Now days if you interacting with customer users sometimes customer want to update data from excel to the application directly. This situation occurs when customer have different resource to update the master data and those people actually preparing data based in excel and then key user update the data in application after reviewing. This is very much possible in business central by using configuration packages in business central.

But what if data is enter in excel and publish directly to business central.

Lets see how we can do that.

Open the vendor List and click on Edit in Excel as shown below.

Once you click on that it will download the file as below and the file showing Data Connector

If you look at the data connector then it has bunch of option to add new data ,refresh the data ,provide filters etc.

To add new data in the excel please click on ‘New’ and add the new vendor in the excel file.

Once you click on publish then it will upload the data in business central as below.

As well as it validate the required fields also . You can look at the Search Name field. How cool it is

On the data connector side there is lot of flexibility while entering data such as date field

Additionally we can filter in excel file as below.

Hope this will ease out some of data entries.

Stay tuned for more.