Dynamics 365 Business Central 2023 Wave 2 – Provide queries for users to do ad hoc data analysis

With each update of Dynamics 365 Business Central, Microsoft enhances what is often referred to as the base application and enhances the development environment. Dynamics 365 Business Central 2023 Wave 2 has several exciting Application features for runtime 12.0. One of the exciting features is to Dynamics 365 Business Central 2023 Wave 2 – Provide queries for users to do ad hoc data analysis.

In the realm of AL development for Microsoft Dynamics 365 Business Central, Query objects are a pivotal tool for extracting and amalgamating records from multiple tables, thereby structuring the data into a unified dataset composed of rows and columns, akin to the functionality provided by an SQL query. There are two types of Query objects: ‘normal’ and ‘API’. Additionally, Queries can execute computational operations on the data, such as sums or averages within a specific dataset column. Moreover, the utilization of Query objects offers a notable enhancement in terms of performance efficiency, particularly in data reading and processing operations.

Before Microsoft Dynamics 365 Business Central 2023 Wave 2, if a developer wanted to display the results of a query on a page, they had to open the Query, loop through the records, and add them to a page with a temporary source.

page 50100 "DVLPR Customer Overview"
{
    ApplicationArea = All;
    Caption = 'Top Customer Overview';
    DeleteAllowed = false;
    InsertAllowed = false;
    ModifyAllowed = false;
    PageType = List;
    SourceTable = "DVLPR Top Customer Overview";
    SourceTableTemporary = true;
    UsageCategory = Lists;

    layout
    {
        area(content)
        {
            repeater(General)
            {
                field("No."; Rec."No.")
                {
                    ToolTip = 'Specifies the value of the No. field.';
                }
                field(Name; Rec.Name)
                {
                    ToolTip = 'Specifies the value of the Name field.';
                }
                field(City; Rec.City)
                {
                    ToolTip = 'Specifies the value of the City field.';
                }
                field("Country/Region Code"; Rec."Country/Region Code")
                {
                    ToolTip = 'Specifies the value of the Country/Region Code field.';
                }
                field("Country/Region Name"; Rec."Country/Region Name")
                {
                    ToolTip = 'Specifies the value of the Country/Region Name field.';
                }
                field("Profit (LCY)"; Rec."Profit (LCY)")
                {
                    ToolTip = 'Specifies the value of the Profit (LCY) field.';
                }
                field("Sales (LCY)"; Rec."Sales (LCY)")
                {
                    ToolTip = 'Specifies the value of the Sales (LCY) field.';
                }
                field("Sales Person Name"; Rec."Sales Person Name")
                {
                    ToolTip = 'Specifies the value of the Sales Person Name field.';
                }
            }
        }
    }

    trigger OnOpenPage()
    var
        TopCustomerOverviewQuery: Query "DVLPR Top Customer Overview";
    begin
        TopCustomerOverviewQuery.Open();
        while TopCustomerOverviewQuery.Read() do begin
            Rec.Init();
            Rec."No." := TopCustomerOverviewQuery.No;
            Rec.Name := TopCustomerOverviewQuery.Name;
            Rec.City := TopCustomerOverviewQuery.City;
            Rec."Sales (LCY)" := TopCustomerOverviewQuery.Sales_LCY;
            Rec."Profit (LCY)" := TopCustomerOverviewQuery.Profit_LCY;
            Rec."Salesperson Code" := TopCustomerOverviewQuery.Salesperson_Code;
            Rec."Sales Person Name" := TopCustomerOverviewQuery.SalesPersonName;
            Rec.Insert;
        end;
    end;
}

Now – a developer can display the Query by running the object directly. Wait, there is a bonus – not only does the Query display, but it is in the new Analysis Mode.

pageextension 50100 "DVLPR Customer List Ext" extends "Customer List"
{
    actions
    {
        addlast("&Customer")
        {
            action("DVLPR Top Customers Query")
            {
                ApplicationArea = All;
                Caption = 'Top Customers Query';
                Image = Sales;
                RunObject = query "DVLPR Top Customer Overview";
                Tooltip = 'Open the top customer query.';
            }          
        }
    }
}

Read more about the feature Provide queries for users to do ad hoc data analysis across tables here.

Note: The code and information discussed in this article are for informational and demonstration purposes only. This content was created referencing Microsoft Dynamics 365 Business Central 2023 Wave 2 online.