Dynamics Community Forum Thread Details

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