Report 713 Inventory - Customer Sales

Base Application, Microsoft.Inventory.Reports

src/Layers/W1/BaseApp/Inventory/Reports/InventoryCustomerSales.Report.al489 lines, Copyright (c) Microsoft Corporation. MIT

// ------------------------------------------------------------------------------------------------// Copyright (c) Microsoft Corporation. All rights reserved.// Licensed under the MIT License. See License.txt in the project root for license information.// ------------------------------------------------------------------------------------------------namespace Microsoft.Inventory.Reports;using Microsoft.Inventory.Item;using Microsoft.Inventory.Ledger;using Microsoft.Sales.Customer;using System.Utilities;report 713 "Inventory - Customer Sales"{    ApplicationArea = Basic, Suite;    Caption = 'Inventory Customer Sales';    ToolTip = 'View, print, or save a list of customers that have purchased selected items within a selected period, for example, to analyze customers'' purchasing patterns. The report specifies quantity, amount, discount, profit percentage, and profit.';    DefaultRenderingLayout = Excel;    UsageCategory = ReportsAndAnalysis;    dataset    {        dataitem(ReportHeader; "Integer")        {            DataItemTableView = sorting(Number) where(Number = const(0));#if not CLEAN27            column(CompanyName; COMPANYPROPERTY.DisplayName())            {                ObsoleteState = Pending;                ObsoleteReason = 'RDLC Only layout column. To be removed along with the RDLC layout.';                ObsoleteTag = '27.0';            }#endif#if not CLEAN28            column(PeriodText; PeriodText)            {                ObsoleteState = Pending;                ObsoleteReason = 'RDLC Only layout column. To be removed along with the RDLC layout.';                ObsoleteTag = '28.0';            }#endif            column(ItemFilter; ItemFilter)            {            }            column(ItemLedgEntryFilter; ItemLedgEntryFilter)            {            }        }        dataitem(Item; Item)        {            DataItemTableView = sorting("No.");            PrintOnlyIfDetail = true;            RequestFilterFields = "No.", "No. 2", "Search Description", "Assembly BOM", "Inventory Posting Group";            column(No_Item; "No.")            {                IncludeCaption = true;            }            column(Description_Item; Description)            {                IncludeCaption = true;            }            column(BaseUnitofMeasure_Item; "Base Unit of Measure")            {                IncludeCaption = true;            }            dataitem("Item Ledger Entry"; "Item Ledger Entry")            {                DataItemLink = "Item No." = field("No."), "Variant Code" = field("Variant Filter"), "Location Code" = field("Location Filter"), "Global Dimension 1 Code" = field("Global Dimension 1 Filter"), "Global Dimension 2 Code" = field("Global Dimension 2 Filter");                DataItemTableView = sorting("Item No.", "Source No.", "Posting Date", "Source Type") where("Source Type" = const(Customer));                RequestFilterFields = "Posting Date", "Source No.";                dataitem("Integer"; "Integer")                {                    column(SourceNo_ItemLedgEntry; TempValueEntryBuf."Source No.")                    {                        IncludeCaption = true;                    }                    column(CustName; GetCustName(TempValueEntryBuf."Source No."))                    {                    }                    column(InvQty_ItemLedgEntry; -TempValueEntryBuf."Invoiced Quantity")                    {                        DecimalPlaces = 0 : 5;                    }                    column(SalesAmtActual_ItemLedgEntry; TempValueEntryBuf."Sales Amount (Actual)")                    {                        IncludeCaption = true;                        AutoFormatType = 1;                    }                    column(Profit_ItemLedgEntry; TempValueEntryBuf."Sales Amount (Expected)")                    {                        IncludeCaption = true;                        AutoFormatType = 1;                    }                    column(DiscountAmount; -TempValueEntryBuf."Purchase Amount (Expected)")                    {                        AutoFormatType = 1;                    }                    column(ProfitPct_ItemLedgEntry; ProfitPct)                    {                        DecimalPlaces = 1 : 1;                    }                    trigger OnAfterGetRecord()                    begin                        if Number = 1 then                            TempValueEntryBuf.FindFirst()                        else                            TempValueEntryBuf.Next();                        ProfitPct := 0;                        if TempValueEntryBuf."Sales Amount (Actual)" <> 0 then                            ProfitPct := TempValueEntryBuf."Sales Amount (Expected)" / TempValueEntryBuf."Sales Amount (Actual)" * 100;                    end;                    trigger OnPostDataItem()                    begin                        TempValueEntryBuf.DeleteAll();                    end;                    trigger OnPreDataItem()                    begin                        TempValueEntryBuf.Reset();                        SetRange(Number, 1, TempValueEntryBuf.Count());                    end;                }                trigger OnAfterGetRecord()                begin                    if IsNewGroup() then                        AddReportLine(ValueEntryBuf);                    IncrLineAmounts(ValueEntryBuf, "Item Ledger Entry");                    if IsLastEntry() then                        AddReportLine(ValueEntryBuf);                    if not ReportHasData then                        ReportHasData := true;                    if TempValueEntryBuf.IsEmpty() then                        CurrReport.Skip();                end;                trigger OnPreDataItem()                begin                    LastItemLedgEntryNo := GetLastItemLedgerEntryNo("Item Ledger Entry");                    Clear(ValueEntryBuf);                    ReportLineNo := 0;                end;            }            dataitem(SubTotals; Integer)            {                DataItemTableView = sorting(Number) where(Number = const(1));                column(SubTotals_InvQty; SubtotalsInvQty)                {                    DecimalPlaces = 0 : 5;                }                column(SubTotals_SalesAmtActual; SubtotalsSalesAmtActual)                {                    DecimalPlaces = 2 : 2;                }                column(SubTotals_DiscountAmount; SubtotalsDiscountAmount)                {                    DecimalPlaces = 2 : 2;                }                column(SubTotals_Profit; SubtotalsItemProfit)                {                    DecimalPlaces = 2 : 2;                }                column(SubTotals_ProfitPct; SubtotalsItemProfitPct)                {                    DecimalPlaces = 1 : 1;                }                column(SubTotals_Description; Item.Description)                {                }                trigger OnPreDataItem()                begin                    if TempValueEntryBuf2.IsEmpty() then                        CurrReport.Break();                    SubtotalsInvQty := 0;                    SubtotalsSalesAmtActual := 0;                    SubtotalsDiscountAmount := 0;                    SubtotalsItemProfit := 0;                    SubtotalsItemProfitPct := 0;                    TempValueEntryBuf2.Reset();                    if TempValueEntryBuf2.FindSet() then                        repeat                            SubtotalsInvQty += (-TempValueEntryBuf2."Invoiced Quantity");                            SubtotalsSalesAmtActual += TempValueEntryBuf2."Sales Amount (Actual)";                            SubtotalsDiscountAmount += (-TempValueEntryBuf2."Purchase Amount (Expected)");                            SubtotalsItemProfit += TempValueEntryBuf2."Sales Amount (Expected)";                        until TempValueEntryBuf2.Next() = 0;                    if SubtotalsSalesAmtActual <> 0 then                        SubtotalsItemProfitPct := SubtotalsItemProfit / SubtotalsSalesAmtActual * 100;                end;                trigger OnAfterGetRecord()                begin                    TotalsSalesAmtActual += SubtotalsSalesAmtActual;                    TotalsDiscountAmount += SubtotalsDiscountAmount;                    TotalsProfit += SubtotalsItemProfit;                end;                trigger OnPostDataItem()                begin                    TempValueEntryBuf2.DeleteAll();                end;            }        }        dataitem(Totals; Integer)        {            DataItemTableView = sorting(Number) where(Number = const(1));            column(Totals_SalesAmtActual; TotalsSalesAmtActual)            {                DecimalPlaces = 2 : 2;            }            column(Totals_DiscountAmount; TotalsDiscountAmount)            {                DecimalPlaces = 2 : 2;            }            column(Totals_Profit; TotalsProfit)            {                DecimalPlaces = 2 : 2;            }            column(Totals_ProfitPct; TotalsProfitPct)            {                DecimalPlaces = 1 : 1;            }            trigger OnPreDataItem()            begin                if not ReportHasData then                    CurrReport.Break();                if TotalsSalesAmtActual <> 0 then                    TotalsProfitPct := TotalsProfit / TotalsSalesAmtActual * 100;            end;        }    }    requestpage    {        AboutTitle = 'About Inventory Customer Sales';        AboutText = 'Analyse your customer sales per item to understand sales trends, optimise inventory management and improve marketing efforts. Assess the relationship between discounts, sales amount and volume of sales for each customer/item combination in the given period.';        layout        {            area(content)            {                group(Options)                {                    Visible = false;                    Caption = 'Options';#if not CLEAN28                    field(PostingDateFilter; PostingDateFilter)                    {                        ApplicationArea = Basic, Suite;                        Caption = 'Posting Date Filter';                        ObsoleteState = Pending;                        ObsoleteReason = 'This request page field will be removed in a future release.';                        ObsoleteTag = '28.0';                    }#endif                    // Used to set report headers across multiple languages                    field(RequestItemFilter; ItemFilter)                    {                        ApplicationArea = All;                        Caption = 'Item Filter';                        ToolTip = 'Specifies the Item Filters applied to this report.';                        Visible = false;                    }                    field(RequestItemLedgEntryFilter; ItemLedgEntryFilter)                    {                        ApplicationArea = All;                        Caption = 'Item Ledger Entry Filter';                        ToolTip = 'Specifies the Item Ledger Entry Filters applied to this report.';                        Visible = false;                    }                }            }        }        actions        {        }#if not CLEAN28                trigger OnQueryClosePage(CloseAction: Action): Boolean        begin            PostingDateFilter := "Item Ledger Entry".GetFilter("Posting Date");        end;#endif        trigger OnClosePage()        begin            UpdateRequestPageFilterValues();        end;    }    rendering    {        layout(Excel)        {            Caption = 'Inventory Customer Sales Excel';            LayoutFile = '.\Inventory\Reports\InventoryCustomerSales.xlsx';            Type = Excel;            Summary = 'Built in layout for the Inventory Customer Sales Excel report.';        }        layout(Word)        {            Caption = 'Inventory Customer Sales Word';            LayoutFile = '.\Inventory\Reports\InventoryCustomerSales.docx';            Type = Word;            Summary = 'Built in layout for the Inventory Customer Sales Word report.';        }#if not CLEAN27        layout(RDLC)        {            Caption = 'Inventory Customer Sales RDLC (Obsolete)';            Type = RDLC;            LayoutFile = '.\Inventory\Reports\InventoryCustomerSales.rdlc';            ObsoleteState = Pending;            ObsoleteReason = 'The RDLC layout has been replaced by the Excel and Word layouts and will be removed in a future release.';            ObsoleteTag = '27.0';            Summary = 'Built in layout for the Inventory Customer Sales RDLC (Obsolete) report.';        }#endif    }    labels    {        DataRetrievedLbl = 'Data retrieved:';        InventoryCustomerSalesLbl = 'Inventory Customer Sales';        InvCustomerSalesPrintLbl = 'Inv. Cust. Sales (Print)', MaxLength = 31, Comment = 'Excel worksheet name.';        InvCustomerSalesAnalysisLbl = 'Inv. Cust. Sales (Analysis)', MaxLength = 31, Comment = 'Excel worksheet name.';        CustomerNoLbl = 'Customer No.';        CustNameLbl = 'Name';        InvQtyLbl = 'Invoiced Quantity';        AmountLbl = 'Amount';        DiscountAmtLbl = 'Discount Amount';        ProfitLbl = 'Profit';        ProfitPctLbl = 'Profit %';        PeriodLbl = 'Period';        // About the report labels        AboutTheReportLbl = 'About the report', MaxLength = 31, Comment = 'Excel worksheet name.';        EnvironmentLbl = 'Environment';        CompanyLbl = 'Company';        UserLbl = 'User';        RunOnLbl = 'Run on';        ReportNameLbl = 'Report name';        DocumentationLbl = 'Documentation';#if not CLEAN27        ReportTitle = 'Inventory - Customer Sales';        Page = 'Page';        CustomerNo = 'Customer No.';        Name = 'Name';        InvoicedQty = 'Invoiced Quantity';        Amount = 'Amount';        DiscountAmt = 'Discount Amount';        Profit = 'Profit';        ProfitPct = 'Profit %';        Total = 'Total';#endif    }    trigger OnPreReport()    begin        UpdateRequestPageFilterValues();    end;    var        ValueEntryBuf: Record "Value Entry";        TempValueEntryBuf: Record "Value Entry" temporary;        TempValueEntryBuf2: Record "Value Entry" temporary;        ItemFilter: Text;        ItemLedgEntryFilter: Text;#if not CLEAN28        PostingDateFilter: Text;#endif        LastItemLedgEntryNo: Integer;        ReportLineNo: Integer;        ProfitPct: Decimal;        SubtotalsInvQty: Decimal;        SubtotalsSalesAmtActual: Decimal;        SubtotalsDiscountAmount: Decimal;        SubtotalsItemProfit: Decimal;        SubtotalsItemProfitPct: Decimal;        TotalsSalesAmtActual: Decimal;        TotalsDiscountAmount: Decimal;        TotalsProfit: Decimal;        TotalsProfitPct: Decimal;        ReportHasData: Boolean;#if not CLEAN28        PeriodText: Text;        PeriodInfoTxt: Label 'Period: %1', Comment = '%1 - period name';#endif        TableFiltersTxt: Label '%1: %2', Locked = true;    local procedure CalcDiscountAmount(ItemLedgerEntryNo: Integer): Decimal    var        ValueEntry: Record "Value Entry";    begin        ValueEntry.SetCurrentKey("Item Ledger Entry No.");        ValueEntry.SetRange("Item Ledger Entry No.", ItemLedgerEntryNo);        ValueEntry.CalcSums("Discount Amount");        exit(ValueEntry."Discount Amount");    end;    local procedure GetLastItemLedgerEntryNo(var ItemLedgerEntry: Record "Item Ledger Entry"): Integer    var        LastItemLedgerEntry: Record "Item Ledger Entry";    begin        LastItemLedgerEntry.Copy(ItemLedgerEntry);        if LastItemLedgerEntry.FindLast() then            exit(LastItemLedgerEntry."Entry No.");        exit(0);    end;    local procedure IncrLineAmounts(var ValueEntryBuf2: Record "Value Entry"; CurrItemLedgerEntry: Record "Item Ledger Entry")    var        Profit: Decimal;        DiscountAmount: Decimal;    begin        CurrItemLedgerEntry.CalcFields("Sales Amount (Actual)", "Cost Amount (Actual)", "Cost Amount (Non-Invtbl.)");        Profit := CurrItemLedgerEntry."Sales Amount (Actual)" + CurrItemLedgerEntry."Cost Amount (Actual)" + CurrItemLedgerEntry."Cost Amount (Non-Invtbl.)";        DiscountAmount := CalcDiscountAmount(CurrItemLedgerEntry."Entry No.");        if ValueEntryBuf2."Item No." = '' then begin            ValueEntryBuf2.Init();            ValueEntryBuf2."Item No." := CurrItemLedgerEntry."Item No.";            ValueEntryBuf2."Source No." := CurrItemLedgerEntry."Source No.";        end;        ValueEntryBuf2."Invoiced Quantity" += CurrItemLedgerEntry."Invoiced Quantity";        ValueEntryBuf2."Sales Amount (Actual)" += CurrItemLedgerEntry."Sales Amount (Actual)";        ValueEntryBuf2."Sales Amount (Expected)" += Profit;        ValueEntryBuf2."Purchase Amount (Expected)" += DiscountAmount;    end;    local procedure AddReportLine(var ValueEntryBuf2: Record "Value Entry")    begin        TempValueEntryBuf := ValueEntryBuf2;        ReportLineNo += 1;        TempValueEntryBuf."Entry No." := ReportLineNo;        TempValueEntryBuf.Insert();        TempValueEntryBuf2.Init();        TempValueEntryBuf2.TransferFields(TempValueEntryBuf);        TempValueEntryBuf2.Insert();        Clear(ValueEntryBuf2);    end;    local procedure IsNewGroup(): Boolean    begin        exit(("Item Ledger Entry"."Source No." <> ValueEntryBuf."Source No.") and (ValueEntryBuf."Source No." <> ''));    end;    local procedure IsLastEntry(): Boolean    begin        exit("Item Ledger Entry"."Entry No." = LastItemLedgEntryNo);    end;    local procedure GetCustName(CustNo: Code[20]): Text[100]    var        Customer: Record Customer;    begin        if Customer.Get(CustNo) then            exit(Customer.Name);        exit('');    end;    local procedure GetTableFilters(TableName: Text; Filters: Text): Text    begin        if Filters <> '' then            exit(StrSubstNo(TableFiltersTxt, TableName, Filters));        exit('');    end;    // Ensures Layout Filter Headings are up to date    local procedure UpdateRequestPageFilterValues()    begin        ItemFilter := GetTableFilters(Item.TableCaption(), Item.GetFilters);        ItemLedgEntryFilter := GetTableFilters("Item Ledger Entry".TableCaption(), "Item Ledger Entry".GetFilters);#if not CLEAN28        PeriodText := StrSubstNo(PeriodInfoTxt, "Item Ledger Entry".GetFilter("Posting Date"));#endif    end;}