Skip to main content

Notifications

Announcements

Announcing Category Subscriptions!
News and Announcements icon
Dynamics 365 Community / Forums / Small and medium business | Business Central, N... / Adding filter in Bank ...
Small and medium business | Business Central, N...
Suggested answer

Adding filter in Bank reconciliation report for showing reconciled and not reconciled bank statement

(0) ShareShare
ReportReport
Posted on by Jinal bhardwaj 20

Hi,

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.

here is my code:

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";


}

Categories:
Dynamics 365 Business Central
  • Suggested answer
    YUN ZHU Profile Picture
    YUN ZHU 74,036 Super User 2024 Season 2 on at
    RE: Adding filter in Bank reconciliation report for showing reconciled and not reconciled bank statement

    Hi, It is recommended that you refer to standard reports that include date filtering, such as Inventory Valuation.

    pastedimage1668038590780v1.png

    Hope this can give you some hints.

    Thanks

    ZHU

  • Suggested answer
    Andy Sather Profile Picture
    Andy Sather on at
    RE: Adding filter in Bank reconciliation report for showing reconciled and not reconciled bank statement

    Hello  - We currently do not have dedicated Dev support via the Dynamics 365 Business Central forums, but I wanted to provide you some additional resources to assist.  If you need assistance with debugging or coding I would recommend discussing this on one of our communities.

    www.yammer.com/dynamicsnavdev

    I will open this up to the community in case they have something to add.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Announcing Category Subscriptions!

Quick Links

photo of Muhammad Affan

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

thumbnail image of a pie chart section

Top 10 leaders for November!

Congratulations to our November super stars!

thumbnail image of the word Tip

Tips for Writing Effective Verified Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,359 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,370 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans