Dynamics Community Forum Thread Details

Modifying order of columns in excel report

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)
            {

            }
        }
    }

}

Quick Links