Here is the code of processing only report there are above 6 lac FA entries , Rdlc taking time so i make processing only excel report to download instantly, here is the code but it still taking long time estimate 2 hours to download , please anyone suggest any workaround to get report Fastly.
report 80006 "SW FA Register Report"
{
UsageCategory = ReportsAndAnalysis;
ApplicationArea = All;
Caption = 'SW FA Register Report';
ProcessingOnly = true;
dataset
{
dataitem("Fixed Asset"; "Fixed Asset")
{
RequestFilterFields = "No.", "FA Class Code", "FA Subclass Code";
trigger OnPreDataItem()
begin
TempExcelBuffer.DeleteAll();
InitPostingTypeList();
WriteHeader();
end;
trigger OnAfterGetRecord()
begin
Clear(FADepBook);
if FADepBook.Get("Fixed Asset"."No.", 'COMPANY') then;
ComputeAmountsForFA("Fixed Asset"."No.");
WriteRow();
end;
trigger OnPostDataItem()
begin
CreateExcelBook();
end;
}
}
requestpage
{
layout
{
area(Content)
{
group(Options)
{
Caption = 'Options';
field(PostingDateFilterField; PostingDateFilter)
{
ApplicationArea = All;
Caption = 'Posting Date Filter';
ToolTip = 'Filters FA Ledger Entries by Posting Date. Example: 01/04/2024..31/03/2025';
}
}
}
}
}
var
FADepBook: Record "FA Depreciation Book";
TempExcelBuffer: Record "Excel Buffer" temporary;
CurrentAmounts: Dictionary of [Integer, Decimal];
PostingTypeList: List of [Integer];
PostingDateFilter: Text;
SheetNameTxt: Label 'FA Register';
ReportNameTxt: Label 'FA Register';
local procedure InitPostingTypeList()
begin
Clear(PostingTypeList);
PostingTypeList.Add(0); // Acquisition Cost
PostingTypeList.Add(1); // Depreciation
PostingTypeList.Add(2); // Write-Down
PostingTypeList.Add(3); // Appreciation
PostingTypeList.Add(4); // Custom 1
PostingTypeList.Add(5); // Custom 2
PostingTypeList.Add(6); // Proceeds on Disposal
PostingTypeList.Add(7); // Salvage Value
PostingTypeList.Add(8); // Gain/Loss
PostingTypeList.Add(9); // Book Value on Disposal
end;
// ------------------------------------------------------------------
// ONE round-trip per FA. Read only the 3 fields we need.
// In-memory aggregation by posting type into CurrentAmounts.
// ------------------------------------------------------------------
local procedure ComputeAmountsForFA(FANo: Code[20])
var
FALedgEntry: Record "FA Ledger Entry";
SignedAmt: Decimal;
ExistingAmt: Decimal;
PT: Integer;
begin
Clear(CurrentAmounts);
FALedgEntry.Reset();
FALedgEntry.ReadIsolation := IsolationLevel::ReadUncommitted;
FALedgEntry.SetCurrentKey("FA No.", "Depreciation Book Code", "FA Posting Date");
FALedgEntry.SetLoadFields("FA Posting Type", Amount);
FALedgEntry.SetRange("FA No.", FANo);
if PostingDateFilter <> '' then
FALedgEntry.SetFilter("Posting Date", PostingDateFilter);
if not FALedgEntry.FindSet() then
exit;
repeat
if FALedgEntry."FA Posting Type" IN
[FALedgEntry."FA Posting Type"::Depreciation,
FALedgEntry."FA Posting Type"::"Salvage Value"] then
SignedAmt := -1 * FALedgEntry.Amount
else
SignedAmt := FALedgEntry.Amount;
PT := FALedgEntry."FA Posting Type".AsInteger();
if CurrentAmounts.Get(PT, ExistingAmt) then
CurrentAmounts.Set(PT, ExistingAmt + SignedAmt)
else
CurrentAmounts.Add(PT, SignedAmt);
until FALedgEntry.Next() = 0;
end;
local procedure PostingTypeName(PT: Integer): Text
var
Enum: Enum "FA Ledger Entry FA Posting Type";
begin
Enum := "FA Ledger Entry FA Posting Type".FromInteger(PT);
exit(Format(Enum));
end;
local procedure WriteHeader()
var
PT: Integer;
begin
TempExcelBuffer.NewRow();
AddTextCol('FA No.');
AddTextCol('Description');
AddTextCol('Description 2');
AddTextCol('FA Class Code');
AddTextCol('FA Subclass Code');
AddTextCol('Project Code');
AddTextCol('Centre Code');
AddTextCol('Location Code');
AddTextCol('FA Location Code');
AddTextCol('Vendor No.');
AddTextCol('Serial No.');
AddTextCol('Last Date Modified');
AddTextCol('FA Posting Group');
AddTextCol('GST Group Code');
AddTextCol('HSN/SAC Code');
AddTextCol('Gen. Prod. Posting Group');
AddTextCol('Model No.');
AddTextCol('Make');
AddTextCol('Capacity');
AddTextCol('Old FA No.');
AddTextCol('Unit Of Measure');
AddTextCol('Purchase Order No.');
AddTextCol('Purchase Invoice No.');
AddTextCol('Vendor Invoice No.');
AddTextCol('Vendor Invoice Date');
AddTextCol('Parent FA No.');
AddTextCol('Predecessor FA No.');
AddTextCol('Item/Service No.');
AddTextCol('FA Category');
AddTextCol('Original Purchase Location');
AddTextCol('Vendor Name');
AddTextCol('FA Qty.');
AddTextCol('Dep. Start Date');
AddTextCol('Dep. End Date');
AddTextCol('No. of Dep. Years');
foreach PT in PostingTypeList do
AddTextCol(PostingTypeName(PT));
end;
local procedure WriteRow()
var
FA: Record "Fixed Asset";
PT: Integer;
Amt: Decimal;
begin
FA := "Fixed Asset";
TempExcelBuffer.NewRow();
AddTextCol(FA."No.");
AddTextCol(FA.Description);
AddTextCol(FA."Description 2");
AddTextCol(FA."FA Class Code");
AddTextCol(FA."FA Subclass Code");
AddTextCol(FA."Global Dimension 1 Code");
AddTextCol(FA."Global Dimension 2 Code");
AddTextCol(FA."Location Code");
AddTextCol(FA."FA Location Code");
AddTextCol(FA."Vendor No.");
AddTextCol(FA."Serial No.");
AddDateCol(FA."Last Date Modified");
AddTextCol(FA."FA Posting Group");
AddTextCol(FA."GST Group Code");
AddTextCol(FA."HSN/SAC Code");
AddTextCol(FA."Gen. Prod. Posting Group");
AddTextCol(FA."Model No.");
AddTextCol(FA.Make);
AddTextCol(FA.Capacity);
AddTextCol(FA."Predecessor FA No.");
AddTextCol(FA."Unit Of Measure");
AddTextCol(FA."Purchase Order No.");
AddTextCol(FA."Purchase Invoice No.");
AddTextCol(FA."Vendor Invoice No.");
AddDateCol(FA."Vendor Invoice Date");
AddTextCol(FA."Parent FA No.");
AddTextCol(FA."Predecessor FA No.");
AddTextCol(FA."Item/Service No.");
AddTextCol(Format(FA."FA Category"));
AddTextCol(FA."Original Purchase Location");
AddTextCol(FA."Vendor Name");
AddDecCol(FA."FA Qty.");
AddDateCol(FADepBook."Depreciation Starting Date");
AddDateCol(FADepBook."Depreciation Ending Date");
AddDecCol(FADepBook."No. of Depreciation Years");
foreach PT in PostingTypeList do begin
Clear(Amt);
if CurrentAmounts.Get(PT, Amt) then
AddDecCol(Amt)
else
AddDecCol(0);
end;
end;
local procedure AddTextCol(Value: Variant)
var
T: Text;
begin
T := Format(Value);
TempExcelBuffer.AddColumn(T, false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Text);
end;
local procedure AddDecCol(Value: Decimal)
begin
TempExcelBuffer.AddColumn(Value, false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Number);
end;
local procedure AddDateCol(Value: Date)
begin
if Value = 0D then
TempExcelBuffer.AddColumn('', false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Text)
else
TempExcelBuffer.AddColumn(Value, false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Date);
end;
local procedure CreateExcelBook()
var
OutFileName: Text;
begin
OutFileName := ReportNameTxt + '_' + Format(Today, 0, '<Year4><Month,2><Day,2>') + '.xlsx';
TempExcelBuffer.CreateNewBook(SheetNameTxt);
TempExcelBuffer.WriteSheet(ReportNameTxt, CompanyName, UserId);
TempExcelBuffer.CloseBook();
TempExcelBuffer.SetFriendlyFilename(OutFileName);
TempExcelBuffer.OpenExcel();
end;
}
