Report 38 Trial Balance by Period

Base Application, Microsoft.Finance.GeneralLedger.Reports

src/Layers/W1/BaseApp/Finance/GeneralLedger/Reports/TrialBalancebyPeriod.Report.al473 lines, Copyright (c) Microsoft Corporation. MIT

// ------------------------------------------------------------------------------------------------// Copyright (c) Microsoft Corporation. All rights reserved.// Licensed under the MIT License. See License.txt in the project root for license information.// ------------------------------------------------------------------------------------------------namespace Microsoft.Finance.GeneralLedger.Reports;using Microsoft.Finance.Analysis;using Microsoft.Finance.GeneralLedger.Account;using Microsoft.Foundation.Company;using Microsoft.Foundation.Enums;using Microsoft.Foundation.Period;using System.Utilities;/// <summary>/// Generates trial balance report with period-by-period breakdown across multiple accounting periods./// Displays account balances across consecutive periods for trend analysis and period-to-period comparison./// </summary>/// <remarks>/// Data sources: G/L Account and Accounting Period tables with multi-period balance calculations./// Supports configurable rounding factors, indentation levels, and comprehensive dimension filtering./// Used for multi-period financial analysis, trend reporting, and management dashboard requirements./// </remarks>report 38 "Trial Balance by Period"{    ApplicationArea = Basic, Suite;    Caption = 'Trial Balance by Period';    PreviewMode = PrintLayout;    UsageCategory = ReportsAndAnalysis;    DataAccessIntent = ReadOnly;    DefaultRenderingLayout = RDLCLayout;    dataset    {        dataitem("G/L Account"; "G/L Account")        {            DataItemTableView = sorting("No.");            RequestFilterFields = "No.", "Income/Balance", "Account Type", "Global Dimension 1 Filter", "Global Dimension 2 Filter";            column(COMPANYNAME; COMPANYPROPERTY.DisplayName())            {            }            column(Text015___FORMAT_Indent_; StrSubstNo(IndentationLevelCap, SelectStr(Indent + 1, IndentTxt)))            {            }            column(G_L_Account__TABLECAPTION__________GLFilter; TableCaption + ': ' + GLFilter)            {            }            column(GLFilter; GLFilter)            {            }            column(LastPageText50; LastPageText50Lbl)            {            }            column(LastPageText51; LastPageText51Lbl)            {            }            column(Text052; Text052Lbl)            {            }            column(RoundingText; RoundingText)            {            }            column(RoundingFactorInt; RoundingFactorInt)            {            }            column(STRSUBSTNO___1__2__Header_1_1__Header_1_2__; StrSubstNo('%1 %2', Header[1, 1], Header[1, 2]))            {            }            column(STRSUBSTNO___1__2__Header_2_1__Header_2_2__; StrSubstNo('%1 %2', Header[2, 1], Header[2, 2]))            {            }            column(STRSUBSTNO___1__2__Header_4_1__Header_4_2__; StrSubstNo('%1 %2', Header[4, 1], Header[4, 2]))            {            }            column(STRSUBSTNO___1__2__Header_3_1__Header_3_2__; StrSubstNo('%1 %2', Header[3, 1], Header[3, 2]))            {            }            column(STRSUBSTNO___1__2__Header_7_1__Header_7_2__; StrSubstNo('%1 %2', Header[7, 1], Header[7, 2]))            {            }            column(STRSUBSTNO___1__2__Header_6_1__Header_6_2__; StrSubstNo('%1 %2', Header[6, 1], Header[6, 2]))            {            }            column(STRSUBSTNO___1__2__Header_5_1__Header_5_2__; StrSubstNo('%1 %2', Header[5, 1], Header[5, 2]))            {            }            column(STRSUBSTNO___1__2__Header_11_1__Header_11_2__; StrSubstNo('%1 %2', Header[11, 1], Header[11, 2]))            {            }            column(STRSUBSTNO___1__2__Header_10_1__Header_10_2__; StrSubstNo('%1 %2', Header[10, 1], Header[10, 2]))            {            }            column(STRSUBSTNO___1__2__Header_9_1__Header_9_2__; StrSubstNo('%1 %2', Header[9, 1], Header[9, 2]))            {            }            column(STRSUBSTNO___1__2__Header_8_1__Header_8_2__; StrSubstNo('%1 %2', Header[8, 1], Header[8, 2]))            {            }            column(STRSUBSTNO___1__2__Header_12_1__Header_12_2__; StrSubstNo('%1 %2', Header[12, 1], Header[12, 2]))            {            }            column(STRSUBSTNO_____1__FORMAT_CLOSINGDATE_StartDate_1____1___; StrSubstNo('..%1', Format(ClosingDate(StartDate[1] - 1))))            {            }            column(LastPage; LastPage)            {            }            column(Trial_Balance_by_PeriodCaption; Trial_Balance_by_PeriodCaptionLbl)            {            }            dataitem(BlankLineCounter; "Integer")            {                DataItemTableView = sorting(Number);                trigger OnPreDataItem()                begin                    SetRange(Number, 1, "G/L Account"."No. of Blank Lines");                end;            }            dataitem("Integer"; "Integer")            {                DataItemTableView = sorting(Number) where(Number = const(1));                column(ColumnValuesAsText_4_; ColumnValuesAsText[4])                {                    AutoCalcField = false;                }                column(ColumnValuesAsText_3_; ColumnValuesAsText[3])                {                    AutoCalcField = false;                }                column(ColumnValuesAsText_2_; ColumnValuesAsText[2])                {                    AutoCalcField = false;                }                column(ColumnValuesAsText_1_; ColumnValuesAsText[1])                {                    AutoCalcField = false;                }                column(ColumnValuesAsText_10_; ColumnValuesAsText[10])                {                    AutoCalcField = false;                }                column(ColumnValuesAsText_9_; ColumnValuesAsText[9])                {                    AutoCalcField = false;                }                column(ColumnValuesAsText_8_; ColumnValuesAsText[8])                {                    AutoCalcField = false;                }                column(ColumnValuesAsText_7_; ColumnValuesAsText[7])                {                    AutoCalcField = false;                }                column(ColumnValuesAsText_6_; ColumnValuesAsText[6])                {                    AutoCalcField = false;                }                column(ColumnValuesAsText_13_; ColumnValuesAsText[13])                {                    AutoCalcField = false;                }                column(ColumnValuesAsText_12_; ColumnValuesAsText[12])                {                    AutoCalcField = false;                }                column(ColumnValuesAsText_11_; ColumnValuesAsText[11])                {                    AutoCalcField = false;                }                column(ColumnValuesAsText_5_; ColumnValuesAsText[5])                {                    AutoCalcField = false;                }                column(G_L_Account___No__; "G/L Account"."No.")                {                }                column(PADSTR_____G_L_Account__Indentation___2___G_L_Account__Name; PadStr('', "G/L Account".Indentation * 2) + "G/L Account".Name)                {                }                column(GLAccountType; GLAccountType)                {                }                column(PageGroupNo; PageGroupNo)                {                }                column(G_L_Account___No__of_Blank_Lines_; "G/L Account"."No. of Blank Lines")                {                }                column(GLAccountNewPage; "G/L Account"."New Page")                {                }                trigger OnAfterGetRecord()                begin                    GLAccountType := "G/L Account"."Account Type".AsInteger();                    if IsNewPage then begin                        PageGroupNo := PageGroupNo + 1;                        IsNewPage := false;                    end;                    if "G/L Account"."New Page" then                        IsNewPage := true;                end;            }            trigger OnAfterGetRecord()            begin                LastPage := false;                Clear(ColumnValuesAsText);                SetRange("Date Filter", 0D, ClosingDate(StartDate[1] - 1));                CalcNetChange("G/L Account");                ColumnValuesAsText[1] := RoundAmount("Net Change");                for i := 2 to MaxCount do begin                    SetRange("Date Filter", StartDate[i - 1], EndDate[i - 1]);                    CalcNetChange("G/L Account");                    ColumnValuesAsText[i] := RoundAmount("Net Change");                end;            end;            trigger OnPreDataItem()            begin                RoundingFactorInt := RoundingFactor.AsInteger();                PageGroupNo := 1;                // Indentation Level                case Indent of                    Indent::"0":                        begin                            SetFilter("Account Type", '%1|%2', "Account Type"::Posting,                              "Account Type"::"End-Total");                            SetRange(Indentation, 0);                        end;                    Indent::"1":                        begin                            SetFilter("Account Type", '%1|%2', "Account Type"::Posting,                              "Account Type"::"End-Total");                            SetRange(Indentation, 1);                        end;                    Indent::"2":                        begin                            SetFilter("Account Type", '%1|%2', "Account Type"::Posting,                              "Account Type"::"End-Total");                            SetRange(Indentation, 2);                        end;                    Indent::"3":                        begin                            SetFilter("Account Type", '%1|%2', "Account Type"::Posting,                              "Account Type"::"End-Total");                            SetRange(Indentation, 3);                        end;                    Indent::"4":                        begin                            SetFilter("Account Type", '%1|%2', "Account Type"::Posting,                              "Account Type"::"End-Total");                            SetRange(Indentation, 4);                        end;                    Indent::"5":                        begin                            SetFilter("Account Type", '%1|%2', "Account Type"::Posting,                              "Account Type"::"End-Total");                            SetRange(Indentation, 5);                        end;                end;                CompanyInfo.CalcFields(Picture);                if PeriodStartingDate = 0D then                    Error(Text004);                AccountingPeriod.Reset();                if not AccountingPeriod.Get(PeriodStartingDate) then                    Error(Text005);                case RoundingFactor of                    RoundingFactor::"1":                        RoundingText := Text011;                    RoundingFactor::"1000":                        RoundingText := Text012;                    RoundingFactor::"1000000":                        RoundingText := Text013;                end;                StartDate[1] := PeriodStartingDate;                EndDate[1] := AccPeriodEndDate(StartDate[1]);                Header[1, 1] := Format(StartDate[1]);                Header[1, 2] := Format(EndDate[1]);                AccountingPeriod2.SetFilter("Starting Date", '>=%1', AccountingPeriod."Starting Date");                MaxCount := AccountingPeriod2.Count();                if MaxCount > 13 then                    MaxCount := 13;                for i := 2 to MaxCount do begin                    AccountingPeriod.Next();                    StartDate[i] := AccountingPeriod."Starting Date";                    EndDate[i] := AccPeriodEndDate(StartDate[i]);                    Header[i, 1] := Format(StartDate[i]);                    Header[i, 2] := Format(EndDate[i]);                end;            end;        }    }    requestpage    {        SaveValues = true;        AboutTitle = 'About Trial Balance by Period';        AboutText = 'Get a detailed breakdown of your closing balances over 12 accounting periods for each G/L account, or summarized by totaling accounts.';        layout        {            area(content)            {                group(Options)                {                    Caption = 'Options';                    field(StartingDate; PeriodStartingDate)                    {                        ApplicationArea = Basic, Suite;                        Caption = 'Starting Date';                        ToolTip = 'Specifies the date from which the report or batch job processes information.';                        ShowMandatory = true;                    }                    field(RoundingFactor; RoundingFactor)                    {                        ApplicationArea = Basic, Suite;                        Caption = 'Rounding Factor';                        ToolTip = 'Specifies a rounding factor that will be used in the balance.';                    }                    field(Indent; Indent)                    {                        ApplicationArea = Basic, Suite;                        Caption = 'Indentation Level';                        OptionCaption = 'None,0,1,2,3,4,5';                        ToolTip = 'Specifies the indentation level that sets the range filter for the accounts you want to be displayed or printed. For example, if you select Indentation Level 1, you filter all Level 1 accounts ranging from Begin-Total to End-Total. If there is both a Begin-Total and an End-Total account in the selected range, the report only shows the End-Total.';                        trigger OnValidate()                        begin                            CheckIndent();                        end;                    }                }            }        }        actions        {        }    }    rendering    {        layout(RDLCLayout)        {            Type = RDLC;            LayoutFile = './Finance/GeneralLedger/Reports/TrialBalancebyPeriod.rdlc';            Summary = 'Report layout made in the legacy RDLC format. Use an RDLC editor to modify the layout.';        }    }    labels    {    }    trigger OnPreReport()    begin        GLFilter := "G/L Account".GetFilters();    end;    var        CompanyInfo: Record "Company Information";        AccountingPeriod: Record "Accounting Period";        AccountingPeriod2: Record "Accounting Period";        GLIndent: Record "G/L Account";        MatrixMgt: Codeunit "Matrix Management";        i: Integer;        RoundingFactorInt: Integer;        PageGroupNo: Integer;        GLAccountType: Integer;        LastPage: Boolean;#pragma warning disable AA0074        Text004: Label 'Enter the starting date for the first period.';        Text005: Label 'The starting date is not the starting date of an accounting period.';        Text011: Label 'Amounts are rounded to 1';        Text012: Label 'Amounts are in whole 1000s.';        Text013: Label 'Amounts are in whole 1000000s.';#pragma warning disable AA0470        Text014: Label 'Indentation Level %1 is not used in the Chart of Accounts. This Chart of Accounts uses max. %2 levels.';        IndentationLevelCap: Label 'Indentation Level : %1';#pragma warning restore AA0470#pragma warning restore AA0074        IsNewPage: Boolean;        MaxIndent: Text[5];        LastPageText50Lbl: Label '/ Last Page';        LastPageText51Lbl: Label '/ Continued';        Text052Lbl: Label 'Page';        Trial_Balance_by_PeriodCaptionLbl: Label 'Trial Balance by Period';        IndentTxt: Label 'None,0,1,2,3,4,5';    protected var        GLFilter: Text;        PeriodStartingDate: Date;        StartDate: array[20] of Date;        EndDate: array[20] of Date;        ColumnValuesAsText: array[13] of Text[30];        RoundingText: Text[80];        Header: array[13, 2] of Text[100];        RoundingFactor: Enum "Analysis Rounding Factor";        MaxCount: Integer;        Indent: Option "None","0","1","2","3","4","5";    local procedure AccPeriodEndDate(UseStartDate: Date): Date    var        AccountingPeriod2: Record "Accounting Period";    begin        AccountingPeriod2."Starting Date" := UseStartDate;        if AccountingPeriod2.Find('>') then            exit(AccountingPeriod2."Starting Date" - 1);        exit(DMY2Date(31, 12, 9999));    end;    local procedure CalcNetChange(var GLAccount: Record "G/L Account")    begin        GLAccount.CalcFields("Net Change");        OnAfterCalcNetChange(GLAccount);    end;    /// <summary>    /// Formats decimal amounts according to the specified rounding factor for multi-period trial balance display.    /// Utilizes Matrix Management codeunit for consistent amount formatting across period columns.    /// </summary>    /// <param name="Value">Decimal amount to be formatted</param>    /// <returns>Formatted amount as text with appropriate rounding applied</returns>    procedure RoundAmount(Value: Decimal): Text[30]    begin        exit(MatrixMgt.FormatAmount(Value, RoundingFactor, false));    end;    procedure InitializeRequest(NewPeriodStartingDate: Date; NewRoundingFactor: Option; NewIndent: Option)    begin        PeriodStartingDate := NewPeriodStartingDate;        RoundingFactor := "Analysis Rounding Factor".FromInteger(NewRoundingFactor);        if NewIndent <> Indent::None then begin            Indent := NewIndent;            CheckIndent();        end;    end;    local procedure CheckIndent()    begin        GLIndent.Reset();        MaxIndent := '';        if GLIndent.FindSet() then            repeat                if Format(GLIndent.Indentation) > MaxIndent then                    MaxIndent := Format(GLIndent.Indentation);            until GLIndent.Next() = 0;        if Format(Indent) > MaxIndent then            if Indent <> Indent::None then                Error(Text014, SelectStr(Indent + 1, IndentTxt), MaxIndent);    end;    [IntegrationEvent(true, false)]    local procedure OnAfterCalcNetChange(var GLAccount: Record "G/L Account")    begin    end;}