Dynamics 365 Business Central: Can we change the default sorting/line ordering of standard reports

Dynamics 365 Business Central

Hi, Readers.
Today I would like to briefly discuss a question I was asked a while ago, can we change the default line sorting/ordering of standard reports.
For example, there is an order.

When printing the standard report (1305 “Standard Sales – Order Conf.”), the lines are sorted as follows.

This is determined by the DataItemTableView Property of the report.

PS: In the following example, the DataItemTableView property is used to sort a table view based on the "Entry No." field.

DataItemTableView = SORTING ("Entry No.");

So can we change this standard behavior? For example, I want to change it to sort by No. in descending order, or sort by quantity in ascending order. Yes, it is possible, but please note that this property cannot be customized, so we have to find other ways.

The property ‘DataItemTableView’ cannot be customized. AL AL0246

We can use the OnAfterPreDataItem (Report Extension Data Set Modify) trigger of the DataItem. This trigger is run before a data item is processed, but after the associated variable is initialized and table views and filters are set. Let’s look at two examples.

1. Sort by No. in descending order

Source code:

reportextension 50122 MyExtension extends "Standard Sales - Order Conf."
{
    dataset
    {
        modify(Line)
        {
            trigger OnAfterPreDataItem()
            begin
                Line.SetCurrentKey("No.");
                Line.Ascending(false);
            end;
        }
    }
}

2. Sort by quantity in ascending order

Source code:

reportextension 50122 MyExtension extends "Standard Sales - Order Conf."
{
    dataset
    {
        modify(Line)
        {
            trigger OnAfterPreDataItem()
            begin
                Line.SetCurrentKey(Quantity);
                Line.Ascending(true);
            end;
        }
    }
}

PS: Dynamics 365 Business Central: How to get the sort order for the records returned (Record.GetAscending Method)

Let’s look at another report example, 701 “Inventory – List”.

There is no sorting method set in the code, so the default is to sort by primary key, Item No.

Sort by primary key (No.) in descending order:

Very simple. But please note that there are many ways to design reports. This approach is limited to reports that only display DataItems, and no reordering will be performed in subsequent triggers. For example, the data is collected in code, or the sorting is specified in code in the report trigger. At this time, you may need to check the standard code to see if there are any supported events. If not, you can only recreate the report to do this. For example, the following report, 1302 “Standard Sales – Pro Forma Inv”

We can subscribe to the following event to achieve similar requirements.

Source code:

codeunit 50122 MyCodeunit
{
    [EventSubscriber(ObjectType::Report, Report::"Standard Sales - Pro Forma Inv", OnAfterLineOnPreDataItem, '', false, false)]
    local procedure "StandardSalesProFormaInv_OnAfterLineOnPreDataItem"(var SalesHeader: Record "Sales Header"; var SalesLine: Record "Sales Line")
    begin
        SalesLine.SetCurrentKey("No.");
        SalesLine.Ascending(false);
    end;
}

Test:

Great, give it a try!!!😁

PS:
1. Dynamics 365 Business Central: How to set and change the default sort order of a page

2. Dynamics 365 Business Central: How to hide all comment lines in Standard Sales – Order Conf. (1305) report (Do not print comment lines) – Customization

3. Dynamics 365 Business Central: How to print all type of lines in standard Pro Forma Invoice report – Customization

END

Hope this will help.

Thanks for reading.

ZHU

コメント

Copied title and URL