Hello community
this is my source code, the problem is it is not opening a new sheet it is writing them all in one sheet who's name is the last item family description name.
I would appreciate it if you really give me useful tips
report 50152 "Item Brand"
{
ApplicationArea = All;
Caption = 'Item Brand';
UsageCategory = Documents;
ProcessingOnly = true;
dataset
{
dataitem("LSC Item Family"; "LSC Item Family")
{
RequestFilterFields = Code;
dataitem(Item; Item)
{
RequestFilterFields = "No.";
DataItemLink = "LSC Item Family Code" = field(Code);
dataitem("Accounting Period"; "Accounting Period")
{
RequestFilterFields = "Starting Date";
trigger OnAfterGetRecord()
var
Item2: Record Item;
begin
// Set Date Filter
Item2.SetRange("No.", Item."No.");
Item2.SetFilter("Date Filter", '%1..%2', "Accounting Period"."Starting Date", CalcDate('<+1m-1D>', "Accounting Period"."Starting Date"));
if Item2.FindSet() then
repeat
Item2.CalcFields(Item2."LSC Qty. Sold (POS)");
if Item2."LSC Qty. Sold (POS)" <> 0 then
TempExcelBuffer.AddColumn(Item2."LSC Qty. Sold (POS)", false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Number)
else
TempExcelBuffer.AddColumn('-', false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Text);
until Item2.Next() = 0;
end;
}
trigger OnAfterGetRecord()
begin
TempExcelBuffer.NewRow();
TempExcelBuffer.AddColumn(Item."No.", false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Text);
TempExcelBuffer.AddColumn(Item.Description, false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Text);
end;
}
trigger OnAfterGetRecord()
var
AccPer: Record "Accounting Period";
begin
// Create a new sheet for the Item Family
TempExcelBuffer.CreateNewBook(Description); // Creates a separate sheet for each family
// Add Headers
TempExcelBuffer.NewRow();
TempExcelBuffer.AddColumn('Item No.', false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Text);
TempExcelBuffer.AddColumn('Description', false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Text);
// Add Accounting Period Headers
if AccPer.FindSet() then
repeat
TempExcelBuffer.AddColumn(AccPer."Starting Date", false, '', false, false, false, '', TempExcelBuffer."Cell Type"::Date);
until AccPer.Next() = 0;
// Finish setting the current sheet
TempExcelBuffer.SelectOrAddSheet(Description);
end;
trigger OnPostDataItem()
begin
// Write data to a separate sheet for each family
TempExcelBuffer.WriteSheet(Description, CompanyName, UserId);
end;
}
}
requestpage
{
SaveValues = true;
}
trigger OnPreReport()
begin
TempExcelBuffer.Reset();
TempExcelBuffer.DeleteAll();
end;
trigger OnPostReport()
begin
TempExcelBuffer.SetFriendlyFilename(StrSubstNo('Item Brands_%1_%2.xlsx', CurrentDateTime, UserId));
TempExcelBuffer.CloseBook(); // Properly close the book after exporting
TempExcelBuffer.OpenExcel(); // Open the Excel file after report completion
end; var
TempExcelBuffer: Record "Excel Buffer";
}
Best Regards
Noha
