Convert Nav Report (that used a DotNet List) into AL Extension

When developing Extensions for NAV, using the Modern Development Environment in Visual Studio Code, you cannot use .Net Interoperability. See this link if you want to read more about this subject.

So if you have NAV developments that use .Net and you want to move them into Extensions you will probably need to replace the logic implemented using .Net with something else.

In the past I created a pattern that can be used when you need to display (log) in a report multi-column information about what a processing report did (for example).

What was the idea ?
– The report first adds information in a List while processing something (as you need);
– and at the end it transforms the elements in the list to a multi-column layout based on the number of columns you want to show.

This was implemented using simple .Net Interoperability in NAV as presented and explained in this blog post.

Now, let’s see how we can convert nav report in order to make it work with Visual Studio Code AL Language.

Microsoft already added in AL some built in .Net Wrappers. Two of them are Lists and Dictionaries, so I can now use the native AL List to replace the .Net list and make the report work in AL.

–> With this in mind I created a new project in Visual Studio Code that contains the following files:

–> And I wrote the code which you can change and adapt for your own needs when you need to display a multi-column log report in AL:

report 50000 TestReport
{
    DefaultLayout = RDLC;
    RDLCLayout = 'ReportWithLogLayout.rdl'; //your report layout

    dataset
    {
        dataitem(GLAccount; "G/L Account")
        { 
            trigger OnAfterGetRecord();
                var
                GLEntry : Record "G/L Entry";
                SumAmount : Decimal;
                CountTransactions : Integer;

                begin
                    clear(GLEntry);
                    clear(SumAmount);
                    clear(CountTransactions);
                    GLEntry.SetRange("G/L Account No.","No.");
                    IF GLEntry.FindSet then
                        repeat
                            SumAmount += GLEntry.Amount;
                            CountTransactions += 1;
                        until GLEntry.Next = 0;

                    IF CountTransactions > 0 then
                        InsertReportLog("No.",SumAmount,CountTransactions,SumAmount/CountTransactions);
                end;
        }
        dataitem(Integer;Integer)
        {
            column(GLAccNo; ListExample.Get(Number + i)){}
            column(SumOfAmount; ListExample.Get(Number + j)){}
            column(CountTransactions; ListExample.Get(Number + k)){}
            column(AverageTransactions; ListExample.Get(Number + l)){}

            trigger OnPreDataItem();
            begin
                Integer.SetRange(Number,1,ListExample.Count / 4);   
            end;

            trigger OnAfterGetRecord();
            begin
                //the index of the AL native List starts from 1, not 0.
                If Integer.Number = 1 then begin
                    i += 0; j += 1; k += 2; l += 3;
                end Else begin
                    i += 3; j += 3; k += 3; l += 3;
                end;
                //Message(ListExample.Get(Number + i));
            end;

            
        }
    }
    
  var
       ListExample : List of [Text]; //AL build in dot net wrapper
       i : Integer;
       j : integer;
       k: integer;
       l : integer;

    local procedure  InsertReportLog(GLAccNo : Code[10]; SumAmount : Decimal; CountTrans : Integer; AvgTransAmount : Decimal);
    begin
        ListExample.Add(GLAccNo);
        ListExample.Add(Format(SumAmount));
        ListExample.Add(Format(CountTrans));
        ListExample.Add(Format(AvgTransAmount));
    end;

}

You can see how easy it is to use the build in List in AL. You don’t even have to instantiate it. But be careful that a strange thing is that it’s numbering is not zero based, so the first item added to the AL List has the index 1 not 0.

–> The next step was to add on the “Chart Of Accounts” page a New Action that runs the report (for testing purposes). I did this by creating a pageextension:

pageextension 50001 ChartOfAccExtension extends "Chart of Accounts"
{
    layout
    {
        // Add changes to page layout here
    }

    actions
    {
        addlast(Reporting)
        {
            action(ShowMyReport)
            {
                CaptionML = ENU = 'Calculate Amounts for All Accounts';
                Image = CalculateCost;
                
                trigger OnAction();
                begin
                    Report.Run(Report::TestReport);
                end;
            }
        }
    }
    
    var
        myInt : Integer;
}

For creating the *.rdl layout I followed the steps provided in this link. But in the future you will not have to do all of this because Microsoft already provided a more easy way in the February Development Preview. Now when you specify the path of the report layout in VS Code, the extension will automatically generate the *.rdl file in the specified path.

–> Test Layout quickly created in Report Builder:

Now let’s test the report in NAV 2018 Web Client. First you need to publish the extension and afterwards:

1) Check in Extension Management and see that extension was published and installed:

2) Go to Chart Of Accounts and use the action added with a pageextension:

3) Press Preview or Print:

4) The report displays the correct information in the layout using the Pdf reader built in the browser:

5) I checked if the amounts are correct by running also the old version of the report. The results are the same:

You can find and download the folder of the AL project here (File Name: Log Report in AL.zip):
https://github.com/andreilungu/NAV_AL

If you would like to learn more about programming in Microsoft Dynamics NAV I recommend buying these books:
Programming Microsoft Dynamics NAV – Fifth Edition
Building ERP Solutions with Microsoft Dynamics NAV