Skip to main content

Notifications

Small and medium business | Business Central, N...
Suggested answer

How to show runningbalance, opening balance in a report

Posted on by 21
Hi All,
 
              I created a custom report with customer ledger entries, i am trying to get the opening balance and running total for it, in my report grouping is on currency code, customer name, opening balance is coming fine, but running total is coming wrong, can anyone help me on this, i am attaching my code the output of the report, thanks for your advice.
 
report 80520 /Sqit Cust Transactions Report/
{
    ApplicationArea = All;
    Caption = 'Customer Transactions Report';
    UsageCategory = ReportsAndAnalysis;
    DefaultLayout = RDLC;
    RDLCLayout = './Source/Layout/CustomerTransactionReport.rdl';
    dataset
    {
        dataitem(/Cust Ledger Entry/; /Cust. Ledger Entry/)
        {
            RequestFilterFields = /Customer No./;   // /Posting Date/;
            CalcFields = /Credit Amount/, /Debit Amount/;
            column(Document_No_; /Document No./) { }
            column(Document_Type; /Document Type/) { }
            column(Posting_Date; /Posting Date/) { }
            column(External_Document_No_; /External Document No./) { }
            column(Description; Description) { }
            column(Debit_Amount; /Debit Amount/) { }
            column(Credit_Amount; /Credit Amount/) { }
            column(Currency_Code; /Currency Code/) { }
            column(Customer_No_; /Customer No./) { }
            column(Customer_Name; /Customer Name/) { }
            column(Company_Name; /Company Name/) { }
            column(CompName; Companyinformation.Name) { }
            column(Companyphone; Companyinformation./Phone No./) { }
            column(Companypostbox; Companyinformation./Post Code/) { }
            column(CompnayFax; Companyinformation./Fax No./) { }
            column(CompanyEmail; Companyinformation./E-Mail/) { }
            column(CompanyWebsite; Companyinformation./Home Page/) { }
            column(Companycity; Companyinformation.City) { }
            column(companyCountry; Companyinformation./Country/Region Code/) { }
            column(MinDate; StartDate) { }
            column(MaxDate; EndDate) { }
            column(CummulativeAmount; CummulativeAmount) { }
            column(CummulativeAmountCust; CummulativeAmountCust) { }
            trigger OnPreDataItem()
            begin
                SetRange(/Posting Date/, StartDate, EndDate);
                CummulativeAmountCust := 0;
                CummulativeAmount := 0;
                  end;
            trigger OnAfterGetRecord()
            var
            begin
                Clear(CummulativeAmountCust);
                CustLedgEntry.Reset();
                CustLedgEntry.SetCurrentKey(/Customer No./, /Posting Date/, /Currency Code/);
                 CustLedgEntry.SetRange(/Customer No./, /Cust Ledger Entry/./Customer No./);
                 CustLedgEntry.SetFilter(/Posting Date/, '<%1', StartDate);
                 CustLedgEntry.SetRange(/Currency Code/, /Cust Ledger Entry/./Currency Code/);
                 CustLedgEntry.SetFilter(/Remaining Amount/, '<>%1', 0);
                if  CustLedgEntry.FindSet() then
                    repeat
                         CustLedgEntry.CalcFields(Amount);
                        CummulativeAmountCust +=  CustLedgEntry.Amount;
                    until SqitGlobalCustLedgEntry.Next() = 0;
                /Cust Ledger Entry/.CalcFields(Amount);
                CummulativeAmount := CummulativeAmountCust + /Cust Ledger Entry/.Amount;

            end;
        }
    }
    requestpage
    {
        layout
        {
            area(content)
            {
                group(GroupName)
                {
                    field(MinDate; StartDate)
                    {
                        Caption = 'Start Date';
                        ApplicationArea = All;
                    }
                    field(MaxDate; EndDate)
                    {
                        ApplicationArea = All;
                        Caption = 'End Date';
                    }
                }
            }
        }
        actions
        {
            area(processing)
            {
            }
        }
    }
    trigger OnPreReport()
    begin
        Companyinformation.get();
        if StartDate = 0D then
            Error('Start Date must have a value');
        if EndDate = 0D then
            Error('End Date must have a value');
        if StartDate > EndDate then
            Error('Start Date should be greater than End Date');
    end;
    var
        Companyinformation: Record /Company Information/;
        CustLedgEntry: Record /Cust. Ledger Entry/;
        CummulativeAmount: Decimal;
        CummulativeAmountCust: Decimal;
        EndDate: Date;
        StartDate: Date;
        CustomerFilter: Code[20];
        i: Integer;
        openingBalance: Decimal;
}
 
 
 
 
 
Thanks & Regards,
Sarath.
  • Suggested answer
    gdrenteria Profile Picture
    gdrenteria 11,988 Most Valuable Professional on at
    How to show runningbalance, opening balance in a report

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

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Community AMA December 12th

Join us as we continue to demystify the Dynamics 365 Contact Center

New! Quick response templatesâš¡

Save time with the new custom templates!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

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

#2
Martin Dráb Profile Picture

Martin Dráb 229,963 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans