Skip to main content

Notifications

Announcements

Join us in the Community for an AMA: December 12th Register Today
News and Announcements icon
Dynamics 365 Community / Forums / Small and medium business | Business Central, N... / How to sort data using...
Small and medium business | Business Central, N...
Unanswered

How to sort data using excel buffer in business central

(0) ShareShare
ReportReport
Posted on by Nilam Bhor 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.
Categories:
Business Central

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

News and Announcements

Join us in the Community for an AMA: December 12th Register Today

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Community AMA December 12th

Join us as we continue to demystify the Dynamics 365 Contact Center

New! Quick response templatesâš¡

Save time with the new custom templates!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

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

#2
Martin Dráb Profile Picture

Martin Dráb 230,030 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans