Skip to main content

Notifications

Announcements

No record found.

Small and medium business | Business Central, N...
Unanswered

How to sort data using excel buffer in business central

(0) ShareShare
ReportReport
Posted on by 153
Hi Guys,
I have below data in my usage statistics page.
I want to show each count for that particular table name.
For example: below table shows the actual result.
 
Table Name(7/5/23)(7/10/23)
Customer105
 
 
I didn't get proper data like below excel:
Here is my code: 
report 50015 /Send sample/
{
    ApplicationArea = All;
    Caption = 'Send sample';
    UsageCategory = Tasks;
    ProcessingOnly = true;
    UseRequestPage = true;
 
    requestpage
    {
        layout
        {
            area(content)
            {
                group(GroupName)
                {
                }
            }
        }
        actions
        {
            area(processing)
            {
            }
        }
    }
    trigger OnInitReport()
    begin
 
    end;
 
    trigger OnPreReport()
    begin
    end;
 
    trigger OnPostReport()
    begin
 
        MakeHeader();
        MakeBody();
 
        MakeExcelSheet();
        // CreateExcelbook();
        // if /Usage Statistics Setup/./Mail Recipients/ = '' then
        //     CurrReport.Quit();
        //   CollectUniqueTableName();
 
    end;
 
    local procedure MakeHeader()
    begin
        Rec_ExcelBuffer.Reset();
        Rec_ExcelBuffer.DeleteAll();
        // Rec_ExcelBuffer.NewRow();
        if Rec_UsageStatistics.FindFirst() then begin
 
            Rec_ExcelBuffer.AddColumn('Table Name', false, '', true, false, false, '', Rec_ExcelBuffer./Cell Type/::Text);
            if Rec_UsageStatistics.FindSet() then
                repeat
                    Rec_UsageStatistics.SetRange(/Table No./);
 
                    if not ListOfDate.Contains(Rec_UsageStatistics./Recorded Date/) then
                        ListOfDate.Add(Rec_UsageStatistics./Recorded Date/);
                until Rec_UsageStatistics.Next() = 0;
 
            foreach Date_D in ListOfDate do begin
                Rec_ExcelBuffer.AddColumn(StrSubstNo('%1 (%2)', Rec_UsageStatistics.FieldCaption(/Recorded Date/), Date_D), false, '', false, false, false, '', Rec_excelbuffer./Cell Type/::Number);
 
            end;
            //   Rec_ExcelBuffer.AddColumn(StrSubstNo('%1 (%2)', Rec_UsageStatistics.FieldCaption(/Recorded Date/), ListOfDate), false, '', false, false, false, '', Rec_excelbuffer./Cell Type/::Number);
        end;
        // Rec_ExcelBuffer.NewRow();
 
        // if Rec_UsageStatistics.FindSet() then
        //     repeat
        //         Rec_UsageStatistics.SetRange(/Table No./);
 
        //         if not ListOfCount.Contains(Rec_UsageStatistics./Record Count/) then
        //             ListOfCount.Add(Rec_UsageStatistics./Record Count/);
 
        //     until Rec_UsageStatistics.Next() = 0;
 
        // foreach Count_R in ListOfCount do begin
 
        //     Rec_ExcelBuffer.AddColumn((Count_R), false, '', false, false, false, '', Rec_ExcelBuffer./Cell Type/::Text);
 
        // end;
 
    end;
 
    local procedure MakeBody()
    begin
        Clear(Rec_UsageStatistics);
        Rec_UsageStatistics.Reset();
        Rec_ExcelBuffer.NewRow();
        //table unique values
        if Rec_UsageStatistics.FindSet() then
            repeat
                Rec_UsageStatistics.SetRange(/Table No./);
                if Rec_UsageStatistics.FindSet() then
                    repeat
                        if not ListOfTableName.Contains(Rec_UsageStatistics./Table Name/) then
                            ListOfTableName.Add(Rec_UsageStatistics./Table Name/);
 
                    until Rec_UsageStatistics.Next() = 0;
 
                foreach TableName in ListOfTableName do begin
                    Rec_ExcelBuffer.NewRow();
                    Rec_ExcelBuffer.AddColumn(TableName, false, '', false, false, false, '', Rec_ExcelBuffer./Cell Type/::Text);
                end;
                //Record count          
                if Rec_UsageStatistics.FindSet() then
                    repeat
                        Rec_UsageStatistics.SetRange(/Entry No./);
 
                        if not ListOfCount.Contains(Rec_UsageStatistics./Record Count/) then
                            ListOfCount.Add(Rec_UsageStatistics./Record Count/);
 
                    until Rec_UsageStatistics.Next() = 0;
 
                foreach Count_R in ListOfCount do begin
                    //   Rec_ExcelBuffer.NewRow();
                    Rec_ExcelBuffer.AddColumn((Count_R), false, '', false, false, false, '', Rec_ExcelBuffer./Cell Type/::Number);
                end;
            //Record date
 
            // Rec_UsageStatisticsNEW./Table Name/ := Rec_ExcelBuffer./Cell Value as Text/;
 
            until Rec_UsageStatistics.Next() = 0;
        //     Rec_ExcelBuffer.AddColumn((Rec_UsageStatistics./Record Count/), false, '', false, false, false, '', Rec_ExcelBuffer./Cell Type/::Number);
 
    end;
 
    local procedure MakeExcelSheet()
    var
        Studentlbl: Label 'raw data';
        ExcelFileName: Label 'raw data';
    begin
        Rec_ExcelBuffer.CreateNewBook(Studentlbl);
        Rec_ExcelBuffer.WriteSheet(Studentlbl, CompanyName, UserId);
        Rec_ExcelBuffer.SetColumnWidth('A', 25);
        Rec_ExcelBuffer.SetColumnWidth('B', 25);
        Rec_ExcelBuffer.SetColumnWidth('C', 25);
        Rec_ExcelBuffer.SetColumnWidth('D', 25);
 
        Rec_ExcelBuffer.CloseBook();
        Rec_ExcelBuffer.SetFriendlyFilename(StrSubstNo(ExcelFileName, CurrentDateTime, UserId));
        Rec_ExcelBuffer.OpenExcel();
        //Rec_ExcelBuffer.ReadSheet();
    end;
 
    var
        Rec_ExcelBuffer: Record /Excel Buffer/;
        Rec_UsageStatistics: Record /Usage Statistics/;
        Rec_UsageStatisticsNEW: Record /Usage Statistics/;
 
        ListOfTableName: List of [Text];
        TableName: Text;
        ListOfDate: List of [Date];
        Count_R: Integer;
        ListOfCount: List of [Integer];
        Date_D: Date;
}
Is any way to do this.

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!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Verified Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

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

#2
Martin Dráb Profile Picture

Martin Dráb 230,445 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans