I have customized a report for bank reconciliation, and I am getting data according to the start date and end date which I select. Now I have to add some kind of filter on request page where I can get data:
If I select particular month, only the reconciled data should be visible.
if I select particular month period it should so me all the un-reconciled data Fallin within the date range.
if I select particular month in start date and end date it should reflect all the pending and reconciled data after that date range.
report 50102 "Bank Reconcillation Statement"
{
UsageCategory = ReportsAndAnalysis;
ApplicationArea = All;
DefaultLayout = RDLC;
RDLCLayout = './BankRecoReport.rdl';
Caption = 'Bank Reconcillation Statement';
PreviewMode = PrintLayout;
dataset
{
dataitem(Bank_Account_Ledger_Entry; "Bank Account Ledger Entry")
{
DataItemTableView = SORTING("Bank Account No.", "Posting Date");
column(Posting_Date; "Posting Date")
{
}
column(Particulars; Description)
{
}
column(Voucher_Type; "Document Type")
{
}
column(Transaction_Type; Transaction_Type)
{
}
column(Instrument_Date; "Cheque Date")
{
}
column(Instrument_No; "Cheque No.")
{
}
column(Bank_Date; BankAccountLedgerEntry."Bank Date")
{
}
column(Debit_Amount; "Debit Amount")
{
}
column(Credit_Amount; "Credit Amount")
{
}
column(CompanyInformation_Name; CompanyInformation.Name)
{
}
column(CompanyInformation_Name_2; CompanyInformation."Name 2")
{
}
column(CompanyInformation_Address; CompanyInformation.Address)
{
}
column(CompanyInformation_Address_2; CompanyInformation."Address 2")
{
}
column(CompanyInformation_City; CompanyInformation.City)
{
}
column(CompanyInformation_Post_Code; CompanyInformation."Post Code")
{
}
column(Bank_Name; GetBankAccount.Name)
{
}
column(Start_Date; StartDate)
{
}
column(End_Date; EndDate)
{
}
column(BalAsPerBank_DR; BalAsPerBankDR)
{
}
column(AmtyNotRefinBank_DR; AmtyNotRefinBankDR)
{
}
column(BalAsPerBook_DR; BalAsPerBookDR)
{
}
column(BalAsPerBank_CR; BalAsPerBankCR)
{
}
column(AmtyNotRefinBank_CR; AmtyNotRefinBankCR)
{
}
column(BalAsPerBook_CR; BalAsPerBookCR)
{
}
column(Document_No_; "Document No.")
{
}
trigger OnPreDataItem()
begin
CompanyInformation.get();
SetFilter("Bank Account No.", '%1', BankAccountNo);
SetFilter("Posting Date", '%1..%2', StartDate, EndDate);
BalAsPerBook := 0;
AmtyNotRefinBank := 0;
BalAsPerBank := 0;
BalAsPerBookDR := 0;
AmtyNotRefinBankDR := 0;
BalAsPerBankDR := 0;
BalAsPerBookCR := 0;
AmtyNotRefinBankCR := 0;
BalAsPerBankCR := 0;
BankAccountLedgerEntry1.Reset();
BankAccountLedgerEntry2.SetCurrentKey("Bank Account No.", "Posting Date");
BankAccountLedgerEntry1.SetRange("Bank Account No.", BankAccountNo);
BankAccountLedgerEntry1.SetFilter("Posting Date", '..%1', EndDate);
if BankAccountLedgerEntry1.FindSet then begin
repeat
BalAsPerBook += BankAccountLedgerEntry1.Amount;
until BankAccountLedgerEntry1.Next() = 0;
end;
BankAccountLedgerEntry2.Reset();
BankAccountLedgerEntry2.SetCurrentKey("Bank Account No.", "Posting Date");
BankAccountLedgerEntry2.SetRange("Bank Account No.", BankAccountNo);
BankAccountLedgerEntry2.SetFilter("Posting Date", '..%1', EndDate);
if BankAccountLedgerEntry2.FindSet() then begin
repeat
AmtyNotRefinBank += BankAccountLedgerEntry2.Amount;
until BankAccountLedgerEntry2.Next() = 0;
end;
IF BalAsPerBook > 0 THEN
BalAsPerBookDR := BalAsPerBook;
IF BalAsPerBook < 0 THEN
BalAsPerBookCR := BalAsPerBook;
IF AmtyNotRefinBank > 0 THEN
AmtyNotRefinBankDR := AmtyNotRefinBank;
IF AmtyNotRefinBank < 0 THEN
AmtyNotRefinBankCR := AmtyNotRefinBank;
end;
trigger OnAfterGetRecord()
begin
GetBankAccount.Get("Bank Account No.");
SetRange("Posting Date", StartDate, EndDate);
BankaccstatementLine.Reset();
BankaccstatementLine.SetRange("Bank Account No.", "Bank Account No.");
if BankaccstatementLine.FindSet() then begin
if "Statement Status" = "Statement Status"::Open then
if "Statement Status" = "Statement Status"::"Bank Acc. Entry Applied" then
if "Statement Status" = "Statement Status"::"Check Entry Applied" then
BankaccstatementLine.TestField("Entry No.");
StartDate := BankaccstatementLine."Posting Date";
EndDate := BankaccstatementLine."Posting Date";
end;
end;
BankAccountLedgerEntry.SetRange("Bank Account No.", "Bank Account No.");
BankAccountLedgerEntry.SetRange(Open, true);
BankAccountLedgerEntry.SetFilter("Statement Status", Format(BankAccountLedgerEntry."Statement Status"::Open) + '|' + Format(BankAccountLedgerEntry."Statement Status"::"Bank Acc. Entry Applied") + '|' + Format(BankAccountLedgerEntry."Statement Status"::"Check Entry Applied"));
if bankaccrecon."Statement Date" > FilterDate then
FilterDate := bankaccrecon."Statement Date";
if FilterDate <> 0D then
BankAccountLedgerEntry.SetFilter("Posting Date", '<=' + Format(FilterDate));
if BankAccountLedgerEntry.FindSet() then
if ExcludeReversedEntries then begin
repeat
if (BankAccountLedgerEntry."Statement Status" = BankAccountLedgerEntry."Statement Status"::Open) and (BankAccountLedgerEntry.Reversed = true) then
BankAccountLedgerEntry.Mark(false)
else
BankAccountLedgerEntry.Mark(true);
until BankAccountLedgerEntry.Next() = 0;
BankAccountLedgerEntry.MarkedOnly(true); */
}
}
requestpage
{
layout
{
area(Content)
{
group(Options)
{
Caption = 'Options';
field(BankAccountNo; BankAccountNo)
{
ApplicationArea = Basic, Suite;
Caption = 'BankAccount No.';
TableRelation = "Bank Account";
}
field(StartDate; StartDate)
{
ApplicationArea = Basic, Suite;
Caption = 'Start Date';
}
field(EndDate; EndDate)
{
ApplicationArea = Basic, Suite;
Caption = 'End Date';
}
}
}
}
actions
{
area(processing)
{
action(ActionName)
{
ApplicationArea = All;
}
}
}
}
trigger OnPreReport()
begin
IF BankAccountNo = '' THEN
ERROR('Bank Account No. must not be blank');
IF StartDate = 0D THEN
ERROR('Start Date must not be blank');
IF EndDate = 0D THEN
ERROR('End Date must not be blank');
end;
trigger OnPostReport()
begin
end;
var
BankAccountNo: Text[250];
StartDate: Date;
EndDate: Date;
GetBankAccount: Record "Bank Account";
BalAsPerBook: Decimal;
AmtyNotRefinBank: Decimal;
BalAsPerBank: Decimal;
BalAsPerBookDR: Decimal;
AmtyNotRefinBankDR: Decimal;
BalAsPerBankDR: Decimal;
BalAsPerBookCR: Decimal;
AmtyNotRefinBankCR: Decimal;
BalAsPerBankCR: Decimal;
BankAccountLedgerEntry1: Record "Bank Account Ledger Entry";
BankAccountLedgerEntry2: Record "Bank Account Ledger Entry";
CompanyInformation: Record "Company Information";
bankaccrecon: Record "Bank Acc. Reconciliation";
BankAcc: page "Bank Acc. Reconciliation";
Reconciled: Boolean;
BankAccountLedgerEntry: Record "Bank Account Ledger Entry";
FilterDate: Date;
ExcludeReversedEntries: Boolean;
MatchCandidateFilterDate: Date;
Bank_Date: Date;
BankaccstatementLine: Record "Bank Account Ledger Entry";
}
