Report 36 Fiscal Year Balance
- App
- Base Application
- Namespace
- Microsoft.Finance.GeneralLedger.Reports
- Versions
- 17-28
Versions171819202122232425262728
Source29
Source in 29
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;
}