Report 38 Trial Balance by Period
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;}