Get your business online with free website builder (en)

Bank Reconciliation Report Development and Format in D365 BC

Bank Reconciliation Report Development and Format:

(1) In this blog we will do development of “Bank Reconciliation” or “Bank Reco.” report inD365 BC.

(2) For developing Bank Reco. Report: Use Dataitem : Bank Account Ledger Entry as shown. Note: The below marked field is not available in “Bank Account Ledger” entry table, for using the below format, develop these fields in “Bank Account Ledger” entry table.

Bank Reconciliation Report Development codeunit 1
Bank Reconciliation Report Development codeunit 2
Bank Reconciliation Report Development codeunit 3
Bank Reconciliation Report Development codeunit 4
Bank Reconciliation Report Development codeunit 5
Bank Reconciliation Report Development codeunit 6
Bank Reconciliation Report Development codeunit 7
Bank Reconciliation Report Development codeunit 8
Bank Reconciliation Report Development codeunit 9
Bank Reconciliation Report Development codeunit 10
Bank Reconciliation Report Development codeunit 11

(3) RDLC format as shown.

Bank Reconciliation Report Development and Format in D365 BC

(4) After doing the above steps, publish the report.

Source Code:

report 50011 “Bank Reconcillation Statement”
{
    UsageCategory = Administration;
    ApplicationArea = All;
    RDLCLayout = ‘./BankRecoReport.rdl’;
    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; ‘Payment Mehod’)
            {
 
            }
            column(Instrument_No; ‘Cheque No.’)
            {
 
            }
            column(Instrument_Date; ‘Cheque Date’)
            {
 
            }
            column(Bank_Date; ‘Clearing 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(CompanyInformation_State; CompanyInformation.”State 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)
            {
 
            }
            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;
                BankAccountLedgerEntry1.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;
 
 
                IF AmtyNotRefinBankDR <> 0 THEN
                    BalAsPerBank := BalAsPerBook – AmtyNotRefinBankDR;
 
                IF AmtyNotRefinBankCR <> 0 THEN
                    BalAsPerBank := BalAsPerBook + ABS(AmtyNotRefinBankCR);
 
 
                IF (AmtyNotRefinBankDR = 0) AND (AmtyNotRefinBankCR = 0) THEN
                    BalAsPerBank := BalAsPerBook;
 
                IF BalAsPerBank > 0 THEN
                    BalAsPerBankDR := BalAsPerBank;
 
                IF BalAsPerBank < 0 THEN
                    BalAsPerBankCR := BalAsPerBank;
            end;
 
            trigger OnAfterGetRecord()
            begin
                GetBankAccount.GET(“Bank Account No.”);
            end;
        }
 
    }
    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”;
}

TCS Register Report Development and Format in BC D365
TDS Register Report Development and Format in BC D365
How to Create or Setup Accounting Period in BC D365
Purchase Received not Invoiced Report in BC D365
Bank reconciliation report (BankReconciliation)– Microsoft Docs

Leave a Reply