Skip to main content

Notifications

Announcements

Share Your Journey with Power Pages and Help Shape Its Future!
News and Announcements icon
Dynamics 365 Community / Forums / Business Central forum / Adding filter in Bank ...
Business Central forum
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:
Sales
Other
Reports and Business Intelligence
  • Suggested answer
    YUN ZHU Profile Picture
    YUN ZHU 69,612 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

Share Your Journey with Power Pages and Help Shape Its Future!

Quick Links

Anton Venter – Community Spotlight

Kudos to our October Community Star of the month!

Announcing Our 2024 Season 2 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Dynamics 365 Community Newsletter - September 2024

Check out the latest community news

more Community News

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,524 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 228,469 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans