Skip to main content

Notifications

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

Modifying order of columns in excel report

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 73,035 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!

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,134 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 229,928 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans