Report 36 Fiscal Year Balance

Base Application, Microsoft.Finance.GeneralLedger.Reports

src/Layers/W1/BaseApp/Finance/GeneralLedger/Reports/FiscalYearBalance.Report.al658 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.Enums;using Microsoft.Foundation.Period;using System.Utilities;/// <summary>/// Generates fiscal year balance report with period analysis and account hierarchy display options./// Provides fiscal year-end balance information with configurable indentation levels and results filtering./// </summary>/// <remarks>/// Data source: G/L Account table with fiscal year date filtering and balance calculations by period./// Supports multiple display options: indentation levels, rounding factors, and results filtering for zero balances./// Used for fiscal year-end analysis, regulatory reporting, and financial statement preparation procedures./// </remarks>report 36 "Fiscal Year Balance"{    AdditionalSearchTerms = 'closing balance';    ApplicationArea = Basic, Suite;    Caption = 'Fiscal Year Balance';    UsageCategory = ReportsAndAnalysis;    DefaultRenderingLayout = RDLCLayout;    dataset    {        dataitem("G/L Account"; "G/L Account")        {            DataItemTableView = sorting("No.");            RequestFilterFields = "No.", "Account Type", "Global Dimension 1 Filter", "Global Dimension 2 Filter";            column(STRSUBSTNO_Text000_PeriodStartingDate_PeriodEndingDate_; StrSubstNo(Text000, PeriodStartingDate, PeriodEndingDate))            {            }            column(STRSUBSTNO_Text002_FYStartingDate_; StrSubstNo(Text002, FYStartingDate))            {            }            column(COMPANYNAME; COMPANYPROPERTY.DisplayName())            {            }            column(STRSUBSTNO_Text015_Indent_; StrSubstNo(Text015, SelectStr(Indent + 1, IndentTxt)))            {            }            column(G_L_Account__TABLECAPTION__________GLFilter; TableCaption + ': ' + GLFilter)            {            }            column(ShowResults; ShowResults)            {            }            column(GLFilters; GLFilter)            {            }            column(PageGroupNo; PageGroupNo)            {            }            column(Text_LastPage; LastPageCap)            {            }            column(Text_Continued; ContinuedCap)            {            }            column(NoBlankLines; "No. of Blank Lines")            {            }            column(RoundingText; RoundingText)            {            }            column(FYStartingDate___1; Format(FYStartingDate - 1))            {            }            column(STRSUBSTNO___1___2__PeriodStartingDate_PeriodEndingDate_; StrSubstNo('%1..%2', PeriodStartingDate, PeriodEndingDate))            {            }            column(PeriodEndingDate; Format(PeriodEndingDate))            {            }            column(AccountType; "Account Type")            {            }            column(AccountTypePosting; GLAccountTypePosting)            {            }            column(Fiscal_Year_BalanceCaption; Fiscal_Year_BalanceCaptionLbl)            {            }            column(G_L_Account___No__Caption; FieldCaption("No."))            {            }            column(NameCaption; NameCap)            {            }            column(DebitCaption; DebitCaptionLbl)            {            }            column(CreditCaption; CreditCaptionLbl)            {            }            column(Closing_BalanceCaption; Closing_BalanceCaptionLbl)            {            }            column(Fiscal_Year_untilCaption; Fiscal_Year_untilCaptionLbl)            {            }            column(This_periodCaption; This_periodCaptionLbl)            {            }            column(BalanceCaption; BalanceCaptionLbl)            {            }            column(Profit___LossCaption; Profit___LossCaptionLbl)            {            }            column(ProfitCaption; ProfitCaptionLbl)            {            }            column(ResultCaption; ResultCaptionLbl)            {            }            column(IndentTxtML; SelectStr(Indent + 1, IndentTxt))            {            }            column(ProfitValue1; ProfitValueAsText[1])            {            }            column(ProfitValue2; ProfitValueAsText[2])            {            }            column(ProfitValue3; ProfitValueAsText[3])            {            }            column(ProfitValue4; ProfitValueAsText[4])            {            }            column(ProfitValue5; ProfitValueAsText[5])            {            }            column(ProfitValue6; ProfitValueAsText[6])            {            }            column(ProfitValue7; ProfitValueAsText[7])            {            }            column(ProfitValue8; ProfitValueAsText[8])            {            }            column(ProfitValue9; ProfitValueAsText[9])            {            }            column(ProfitValue10; ProfitValueAsText[10])            {            }            column(SumValue1; SumValueAsText[1])            {            }            column(SumValue2; SumValueAsText[2])            {            }            column(SumValue3; SumValueAsText[3])            {            }            column(SumValue4; SumValueAsText[4])            {            }            column(SumValue5; SumValueAsText[5])            {            }            column(SumValue6; SumValueAsText[6])            {            }            column(SumValue7; SumValueAsText[7])            {            }            column(SumValue8; SumValueAsText[8])            {            }            column(SumValue9; SumValueAsText[9])            {            }            column(SumValue10; SumValueAsText[10])            {            }            column(FinalTotal1; FinalTotalAsText[1])            {            }            column(FinalTotal2; FinalTotalAsText[2])            {            }            column(FinalTotal3; FinalTotalAsText[3])            {            }            column(FinalTotal4; FinalTotalAsText[4])            {            }            column(FinalTotal5; FinalTotalAsText[5])            {            }            column(FinalTotal6; FinalTotalAsText[6])            {            }            column(FinalTotal7; FinalTotalAsText[7])            {            }            column(FinalTotal8; FinalTotalAsText[8])            {            }            column(FinalTotal9; FinalTotalAsText[9])            {            }            column(FinalTotal10; FinalTotalAsText[10])            {            }            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(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(ColumnValuesAsText_1_; ColumnValuesAsText[1])                {                    AutoCalcField = false;                }                column(ColumnValuesAsText_2_; ColumnValuesAsText[2])                {                    AutoCalcField = false;                }                column(ColumnValuesAsText_3_; ColumnValuesAsText[3])                {                    AutoCalcField = false;                }                column(ColumnValuesAsText_4_; ColumnValuesAsText[4])                {                    AutoCalcField = false;                }                column(ColumnValuesAsText_5_; ColumnValuesAsText[5])                {                    AutoCalcField = false;                }                column(ColumnValuesAsText_6_; ColumnValuesAsText[6])                {                    AutoCalcField = false;                }                column(ColumnValuesAsText_7_; ColumnValuesAsText[7])                {                    AutoCalcField = false;                }                column(ColumnValuesAsText_8_; ColumnValuesAsText[8])                {                    AutoCalcField = false;                }                column(ColumnValuesAsText_9_; ColumnValuesAsText[9])                {                    AutoCalcField = false;                }                column(ColumnValuesAsText_10_; ColumnValuesAsText[10])                {                    AutoCalcField = false;                }            }            trigger OnAfterGetRecord()            var                I: Integer;            begin                PageGroupNo := NextPageGroupNo;                if "New Page" then                    NextPageGroupNo := PageGroupNo + 1;                Clear(ColumnValuesAsText);                Clear(ColumnValuesAsTot);                GLAccount2.Copy("G/L Account");                // Opening Balance                GLAccount2.SetRange("Date Filter", 0D, ClosingDate(FYStartingDate - 1));                GLAccount2.CalcFields("Net Change");                if GLAccount2."Net Change" > 0 then begin                    ColumnValuesAsText[1] := RoundAmount(GLAccount2."Net Change");                    ColumnValuesAsTot[1] := GLAccount2."Net Change";                end else begin                    ColumnValuesAsText[2] := RoundAmount(Abs(GLAccount2."Net Change"));                    ColumnValuesAsTot[2] := Abs(GLAccount2."Net Change");                end;                // Fiscal YTD                if FYStartingDate <> PeriodStartingDate then begin                    GLAccount2.SetRange("Date Filter", FYStartingDate, PeriodStartingDate - 1);                    GLAccount2.CalcFields("Debit Amount", "Credit Amount");                    ColumnValuesAsText[3] := RoundAmount(GLAccount2."Debit Amount");                    ColumnValuesAsTot[3] := GLAccount2."Debit Amount";                    ColumnValuesAsText[4] := RoundAmount(GLAccount2."Credit Amount");                    ColumnValuesAsTot[4] := GLAccount2."Credit Amount";                end;                // This period                GLAccount2.SetRange("Date Filter", PeriodStartingDate, PeriodEndingDate);                GLAccount2.CalcFields("Debit Amount", "Credit Amount");                ColumnValuesAsText[5] := RoundAmount(GLAccount2."Debit Amount");                ColumnValuesAsTot[5] := GLAccount2."Debit Amount";                ColumnValuesAsText[6] := RoundAmount(GLAccount2."Credit Amount");                ColumnValuesAsTot[6] := GLAccount2."Credit Amount";                // Fiscal Year Cumulative Balance                GLAccount2.SetRange("Date Filter", 0D, PeriodEndingDate);                GLAccount2.CalcFields("Balance at Date");                case GLAccount2."Income/Balance" of                    GLAccount2."Income/Balance"::"Balance Sheet":                        if GLAccount2."Balance at Date" > 0 then begin                            ColumnValuesAsText[7] := RoundAmount(GLAccount2."Balance at Date");                            ColumnValuesAsTot[7] := GLAccount2."Balance at Date";                        end                        else begin                            ColumnValuesAsText[8] := RoundAmount(Abs(GLAccount2."Balance at Date"));                            ColumnValuesAsTot[8] := Abs(GLAccount2."Balance at Date");                        end;                    GLAccount2."Income/Balance"::"Income Statement":                        if GLAccount2."Balance at Date" > 0 then begin                            ColumnValuesAsText[9] := RoundAmount(GLAccount2."Balance at Date");                            ColumnValuesAsTot[9] := GLAccount2."Balance at Date";                        end                        else begin                            ColumnValuesAsText[10] := RoundAmount(Abs(GLAccount2."Balance at Date"));                            ColumnValuesAsTot[10] := Abs(GLAccount2."Balance at Date");                        end;                end;                for I := 1 to 10 do                    SummTotals[I] += ColumnValuesAsTot[I];                for I := 1 to 10 do                    if I mod 2 <> 0 then                        SummProfit[I] := SummTotals[I + 1] - SummTotals[I]                    else                        SummProfit[I] := SummTotals[I - 1] - SummTotals[I];                FillTotals();                GLAccountTypePosting := "Account Type" = "Account Type"::Posting;            end;            trigger OnPreDataItem()            begin                PageGroupNo := 1;                NextPageGroupNo := 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;                    Indent::None:                        if ShowResults = true then                            SetFilter("Account Type", '%1', "Account Type"::Posting);                end;                if PeriodStartingDate = 0D then                    Error(Text003);                if not AccountingPeriod.Get(PeriodStartingDate) then                    Error(Text004);                case RoundingFactor of                    RoundingFactor::"1":                        RoundingText := Text011;                    RoundingFactor::"1000":                        RoundingText := Text012;                    RoundingFactor::"1000000":                        RoundingText := Text013;                end;                AccountingPeriod.SetFilter("Starting Date", '<=%1', PeriodStartingDate);                AccountingPeriod.SetRange("New Fiscal Year", true);                AccountingPeriod.FindLast();                FYStartingDate := AccountingPeriod."Starting Date";            end;        }    }    requestpage    {        AboutTitle = 'About Fiscal Year Balance';        AboutText = 'The **Fiscal Year Balance** report displays the opening and closing balances for each general ledger account within a selected fiscal year. Use it for fiscal year-end closing or financial analysis to verify that balances are correctly carried forward and to support reconciliation and trend review.';        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.';                        trigger OnValidate()                        begin                            SetEndingDate();                        end;                    }                    field(EndingDate; PeriodEndingDate)                    {                        ApplicationArea = Basic, Suite;                        Caption = 'Ending Date';                        ToolTip = 'Specifies the date to which the report or batch job processes information.';                    }                    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                            CheckIndentationLevel();                        end;                    }                    field(ShowResults; ShowResults)                    {                        ApplicationArea = Basic, Suite;                        Caption = 'Show Totals';                        ToolTip = 'Specifies if you want to show totals at the end of the report. If you select None in the Indentation Level field, and you select this field, the report will only show the posting account types.';                        trigger OnValidate()                        begin                            if (Indent = Indent::None) and ShowResults then                                Message(Text016);                        end;                    }                }            }        }        actions        {        }        trigger OnOpenPage()        begin            AccountingPeriod.SetFilter("Starting Date", '<=%1', WorkDate());            AccountingPeriod.SetRange("New Fiscal Year", true);            if AccountingPeriod.FindLast() then                PeriodStartingDate := AccountingPeriod."Starting Date";            SetEndingDate();        end;    }    rendering    {        layout(RDLCLayout)        {            Type = RDLC;            LayoutFile = './Finance/GeneralLedger/Reports/FiscalYearBalance.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        AccountingPeriod: Record "Accounting Period";        GLIndent: Record "G/L Account";        GLAccount2: Record "G/L Account";        MatrixMgt: Codeunit "Matrix Management";        RoundingFactor: Enum "Analysis Rounding Factor";        GLFilter: Text;        ColumnValuesAsText: array[13] of Text[30];        ProfitValueAsText: array[10] of Text;        SumValueAsText: array[10] of Text;        FinalTotalAsText: array[10] of Text;        RoundingText: Text[80];        PeriodStartingDate: Date;        PeriodEndingDate: Date;        FYStartingDate: Date;#pragma warning disable AA0074#pragma warning disable AA0470        Text000: Label 'Period : %1..%2';        Text002: Label 'Fiscal Year Starting Date : %1';#pragma warning restore AA0470        Text003: Label 'Enter the starting date for the first period.';        Text004: 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.';        Text015: Label 'Indentation Level : %1';#pragma warning restore AA0470        Text016: Label 'In case of no indentation, the Chart of Accounts will show only postings.';#pragma warning restore AA0074        IndentTxt: Label 'None,0,1,2,3,4,5';        Indent: Option "None","0","1","2","3","4","5";        MaxIndent: Text[5];        ColumnValuesAsTot: array[10] of Decimal;        ShowResults: Boolean;        PageGroupNo: Integer;        NextPageGroupNo: Integer;#pragma warning disable AA0074        LastPageCap: Label '/ Last Page';        ContinuedCap: Label '/ Continued';#pragma warning restore AA0074        Fiscal_Year_BalanceCaptionLbl: Label 'Fiscal Year Balance';#pragma warning disable AA0074        NameCap: Label 'Name';#pragma warning restore AA0074        DebitCaptionLbl: Label 'Debit';        CreditCaptionLbl: Label 'Credit';        Closing_BalanceCaptionLbl: Label 'Closing Balance';        Fiscal_Year_untilCaptionLbl: Label 'Fiscal Year until';        This_periodCaptionLbl: Label 'This period';        BalanceCaptionLbl: Label 'Balance';        Profit___LossCaptionLbl: Label 'Profit & Loss';        ProfitCaptionLbl: Label 'Profit';        ResultCaptionLbl: Label 'Result';        SummProfit: array[10] of Decimal;        SummTotals: array[10] of Decimal;        GLAccountTypePosting: Boolean;    /// <summary>    /// Calculates ending date for the fiscal period based on the starting date and accounting periods.    /// Uses the next accounting period's starting date minus one day as the fiscal period ending date.    /// </summary>    procedure SetEndingDate()    begin        AccountingPeriod.Reset();        AccountingPeriod.SetFilter("Starting Date", '>%1', PeriodStartingDate);        AccountingPeriod.FindFirst();        PeriodEndingDate := AccountingPeriod."Starting Date" - 1;    end;    /// <summary>    /// Formats decimal amounts according to the specified rounding factor for fiscal year balance display.    /// Utilizes Matrix Management codeunit for consistent amount formatting across fiscal year reports.    /// </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;    /// <summary>    /// Initializes report parameters for programmatic execution with fiscal period configuration and display options.    /// Sets period dates, rounding factors, indentation levels, and results filtering for automated fiscal year balance generation.    /// </summary>    /// <param name="NewPeriodStartingDate">Starting date for fiscal period analysis</param>    /// <param name="NewPeriodEndingDate">Ending date for fiscal period analysis</param>    /// <param name="NewRoundingFactor">Rounding factor option for amount display</param>    /// <param name="NewIndent">Indentation level option for account hierarchy display</param>    /// <param name="NewShowResults">Whether to show accounts with zero balances in results</param>    procedure InitializeRequest(NewPeriodStartingDate: Date; NewPeriodEndingDate: Date; NewRoundingFactor: Option; NewIndent: Option; NewShowResults: Boolean)    begin        PeriodStartingDate := NewPeriodStartingDate;        PeriodEndingDate := NewPeriodEndingDate;        RoundingFactor := "Analysis Rounding Factor".FromInteger(NewRoundingFactor);        Indent := NewIndent;        CheckIndentationLevel();        ShowResults := NewShowResults;    end;    local procedure CheckIndentationLevel()    begin        GLIndent.Reset();        MaxIndent := '';        if GLIndent.Find('-') 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;    local procedure FillTotals()    var        I: Integer;    begin        Clear(ProfitValueAsText);        Clear(SumValueAsText);        Clear(FinalTotalAsText);        for I := 1 to 10 do begin            if SummProfit[I] > 0 then                ProfitValueAsText[I] := Format(NonNegative(SummProfit[I]));            if SummTotals[I] > 0 then                SumValueAsText[I] := Format(SummTotals[I]);            if NonNegative(SummProfit[I]) + SummTotals[I] > 0 then                FinalTotalAsText[I] := Format(NonNegative(SummProfit[I]) + SummTotals[I]);        end;    end;    local procedure NonNegative(Value: Decimal): Decimal    begin        if Value < 0 then            exit(0);        exit(Value);    end;}