Skip to main content

Notifications

Announcements

No record found.

Small and medium business | Business Central, N...
Suggested answer

Modifying order of columns in excel report

(0) ShareShare
ReportReport
Posted on by 130

Hi All, 

My AL code is below. I would like to order report so that 1st 5 columns are: No. / Description / Location_Code / Bin_Code / Quantity_Base

However, since this information is in 2 separate tables, using datalink, it seems like it restricts my ability to format order of columns. So I must request all columns from Table 1....then Table 2, and can not configure the order of columns from 2 separate tables.

Is this correct?

report 60002 customInventoryValuation
{
    ApplicationArea = All;
    Caption = 'Inventory Report';
    ExcelLayout = 'customInventoryReportValuation.xlsx';
    DefaultLayout = Excel;
    UsageCategory = ReportsAndAnalysis;
    dataset
    {
        dataitem(Item; Item)
        {
            column(No_; "No.")
            {
                IncludeCaption = true;
            }
            column(Vendor_Item_No_; "Vendor Item No.")
            {
                IncludeCaption = true;
            }
            column(Description; Description)
            {
                IncludeCaption = true;
            }
            column(Base_Unit_of_Measure; "Base Unit of Measure")
            {
                IncludeCaption = true;
            }

            dataitem("BinContent"; "Bin Content")
            {
                DataItemLink = "Item No." = field("No.");

                column(Location_Code; "Location Code")
                {
                    IncludeCaption = true;
                }
                column(Bin_Code; "Bin Code")
                {
                    IncludeCaption = true;
                }
                column(Quantity__Base_; "Quantity (Base)")
                {
                    IncludeCaption = true;
                    DecimalPlaces = 0;
                }
                column(Inventory_Value; "Inventory Value")
                {
                    IncludeCaption = true;

                }
                trigger OnPreDataItem()
                var
                begin
                    SetFilter("Quantity (Base)", '>%1', 0);
                    SetFilter(Quantity, '>%1', 0);
                end;

                trigger OnAfterGetRecord()
                var
                    itemRecord: Record item;
                begin
                    "Inventory Value" := item."Standard Cost" * "Quantity (Base)";
                end;

            }
            trigger OnPreDataItem()
            var
                binContentRecord: Record "Bin Content";
            begin
                SetFilter(Type, '%1', type::Inventory);
                SetFilter("No.", '<>%1', '');
                SetFilter(Inventory, '<>%1', 0);
            end;
        }
    }
    requestpage
    {
        SaveValues = true;
        layout
        {

            area(content)
            {
                group(GroupName)
                {
                }
            }
        }
        actions
        {

            area(processing)
            {

            }
        }
    }

}

  • Suggested answer
    YUN ZHU Profile Picture
    YUN ZHU 74,115 Super User 2024 Season 2 on at
    RE: Modifying order of columns in excel report

    Hi, sorry I may not fully understand your question, if you want to set DataItem sorting, you can use DataItemTableView Property.

    https://learn.microsoft.com/en-us/dynamics365/business-central/dev-itpro/developer/properties/devenv-dataitemtableview-property

    In addition, if you use Excel Layout, I think you can create another sheet and customize the sorting.

    Hope this helps.

    Thanks.

    ZHU

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