Dynamics Community Forum Thread Details

How to use the query in a report with a dataitem

Hi All,

I created a report with a dataitem bank account ledger entry with grouping shortcut dimension 6, 7 and country region code, for this i need amount lcy for 0D to end datei am getting it correctly, but i want amount lcy for value from start date to end date but i am getting this value wrongly, any one will help me on this, if i use a variable means it is coming correctly, but my report is runnig long time, so i created a query and using this query to get the values but i am getting last value only form the query and it is not coming correctly, can anyone help me on this.

dataset
{
dataitem(/Bank Account Ledger Entry/; /Bank Account Ledger Entry/)
{
//UseTemporary = true;
column(Bank_Account_No_; /Bank Account No./) { }
column(Amount__LCY_; /Amount (LCY)/) { }
column(CountryName; CountryName) { }
column(Sectors; /Shortcut Dimension 7 Code/) { }
column(Categorycode; /Shortcut Dimension 6 Code/) { }

trigger OnPreDataItem()
begin
SetRange(/Posting Date/, 0D, EndDate);

end;

trigger OnAfterGetRecord()
begin
Clear(CountryName);
CountryRegion.Reset();
if CountryRegion.Get(/Country/Region Code/) then
CountryName := CountryRegion.Name;
bankCount := /Bank Account Ledger Entry/.CountApprox;

end;
}

dataitem(Integer; Integer)
{
DataItemTableView = where(Number = const(5));
column(TotalBALE; CreditActivity.AmountLCY) { }
column(FinalBALE; FinalBALE) { }
trigger OnPreDataItem()
begin
// SetRange(Number, 1);
CreditActivity.SetRange(CreditActivity.PostingDateFilter, StartDate, EndDate);
CreditActivity.SetRange(CreditActivity.ShortcutDimension6Code, /Bank Account Ledger Entry/./Shortcut Dimension 6 Code/);
CreditActivity.SetRange(CreditActivity.ShortcutDimension7Code, /Bank Account Ledger Entry/./Shortcut Dimension 7 Code/);
CreditActivity.SetRange(CreditActivity.CountryRegionCode, /Bank Account Ledger Entry/./Country/Region Code/);
CreditActivity.Open();
end;

trigger OnAfterGetRecord()
begin
if not CreditActivity.Read() then begin

CurrReport.Break();
end;
Message('%1', CreditActivity.AmountLCY);
end;

trigger OnPostDataItem()
begin
CreditActivity.Close();
end;
}

requestpage
{
layout
{
area(content)
{
group(Dates)
{
field(StartDate; StartDate)
{
Caption = 'Start Date';
ApplicationArea = All;
}
field(EndDate; EndDate)
{
Caption = 'End Date';
ApplicationArea = All;
}

}
}
}
actions
{
area(processing)
{

}
}
}
trigger OnPreReport()
begin
if StartDate = 0D then
Error('Start Date must have a value');
if EndDate = 0D then
Error('End Date must have a Value');
if EndDate < StartDate then
Error('End Date must not be greater than Start Date');
end;