Report 113 Customer/Item Sales
src/Layers/W1/BaseApp/Sales/Reports/CustomerItemSales.Report.al609 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.Sales.Reports;/// <summary>/// Generates an item-level sales analysis showing quantities and amounts sold to each customer for a specified period./// </summary>using Microsoft.Inventory.Item;using Microsoft.Inventory.Ledger;using Microsoft.Sales.Customer;using Microsoft.Utilities;using System.Utilities;report 113 "Customer/Item Sales"{ ApplicationArea = Basic, Suite; Caption = 'Customer/Item Sales'; ToolTip = 'View a list of item sales for each customer during a selected time period. The report contains information on quantity, sales amount, profit, and possible discounts. It can be used, for example, to analyze a company''s customer groups.'; DataAccessIntent = ReadOnly; ExcelLayoutMultipleDataSheets = true; DefaultRenderingLayout = Excel; PreviewMode = PrintLayout; UsageCategory = ReportsAndAnalysis; dataset { dataitem(Customer; Customer) { PrintOnlyIfDetail = true; RequestFilterFields = "No.", "Search Name", "Customer Posting Group"; column(STRSUBSTNO_Text000_PeriodText_; StrSubstNo(PeriodTxt, PeriodText)) { }#if not CLEAN28 column(PrintOnlyOnePerPage; PrintOnlyOnePerPageReq) { ObsoleteState = Pending; ObsoleteReason = 'The New Page per Customer option is only supported by the RDLC layout which has been deprecated.'; ObsoleteTag = '28.0'; }#endif column(Customer_TABLECAPTION__________CustFilter; CustFilterHeading) { } column(Value_Entry__TABLECAPTION__________ItemLedgEntryFilter; ValueEntryFilterHeading) { } column(Customer__No__; "No.") { } column(Customer_Name; Name) { } column(Customer__Phone_No__; "Phone No.") { } column(ValueEntryBuffer__Sales_Amount__Actual__; TempValueEntryBuffer."Sales Amount (Actual)") { } column(ValueEntryBuffer__Discount_Amount_; -TempValueEntryBuffer."Discount Amount") { } column(Profit; Profit) { AutoFormatType = 1; } column(ProfitPct; ProfitPct) { DecimalPlaces = 1 : 1; }#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'; } column(CustFilter; CustFilter) { ObsoleteState = Pending; ObsoleteReason = 'RDLC Only layout column. To be removed along with the RDLC layout.'; ObsoleteTag = '27.0'; } column(ItemLedgEntryFilter; ValueEntryFilter) { ObsoleteState = Pending; ObsoleteReason = 'RDLC Only layout column. To be removed along with the RDLC layout.'; ObsoleteTag = '27.0'; } column(Customer_Item_SalesCaption; Customer_Item_SalesCaptionLbl) { ObsoleteState = Pending; ObsoleteReason = 'RDLC Only layout column. To be removed along with the RDLC layout.'; ObsoleteTag = '27.0'; } column(CurrReport_PAGENOCaption; CurrReport_PAGENOCaptionLbl) { ObsoleteState = Pending; ObsoleteReason = 'RDLC Only layout column. To be removed along with the RDLC layout.'; ObsoleteTag = '27.0'; } column(All_amounts_are_in_LCYCaption; All_amounts_are_in_LCYCaptionLbl) { ObsoleteState = Pending; ObsoleteReason = 'RDLC Only layout column. To be removed along with the RDLC layout.'; ObsoleteTag = '27.0'; } column(ValueEntryBuffer__Item_No__Caption; ValueEntryBuffer__Item_No__CaptionLbl) { ObsoleteState = Pending; ObsoleteReason = 'RDLC Only layout column. To be removed along with the RDLC layout.'; ObsoleteTag = '27.0'; } column(Item_DescriptionCaption; Item_DescriptionCaptionLbl) { ObsoleteState = Pending; ObsoleteReason = 'RDLC Only layout column. To be removed along with the RDLC layout.'; ObsoleteTag = '27.0'; } column(ValueEntryBuffer__Invoiced_Quantity_Caption; ValueEntryBuffer__Invoiced_Quantity_CaptionLbl) { ObsoleteState = Pending; ObsoleteReason = 'RDLC Only layout column. To be removed along with the RDLC layout.'; ObsoleteTag = '27.0'; } column(Item__Base_Unit_of_Measure_Caption; Item__Base_Unit_of_Measure_CaptionLbl) { ObsoleteState = Pending; ObsoleteReason = 'RDLC Only layout column. To be removed along with the RDLC layout.'; ObsoleteTag = '27.0'; } column(ValueEntryBuffer__Sales_Amount__Actual___Control44Caption; ValueEntryBuffer__Sales_Amount__Actual___Control44CaptionLbl) { ObsoleteState = Pending; ObsoleteReason = 'RDLC Only layout column. To be removed along with the RDLC layout.'; ObsoleteTag = '27.0'; } column(ValueEntryBuffer__Discount_Amount__Control45Caption; ValueEntryBuffer__Discount_Amount__Control45CaptionLbl) { ObsoleteState = Pending; ObsoleteReason = 'RDLC Only layout column. To be removed along with the RDLC layout.'; ObsoleteTag = '27.0'; } column(Profit_Control46Caption; Profit_Control46CaptionLbl) { ObsoleteState = Pending; ObsoleteReason = 'RDLC Only layout column. To be removed along with the RDLC layout.'; ObsoleteTag = '27.0'; } column(ProfitPct_Control47Caption; ProfitPct_Control47CaptionLbl) { ObsoleteState = Pending; ObsoleteReason = 'RDLC Only layout column. To be removed along with the RDLC layout.'; ObsoleteTag = '27.0'; } column(Customer__Phone_No__Caption; FieldCaption("Phone No.")) { ObsoleteState = Pending; ObsoleteReason = 'RDLC Only layout column. To be removed along with the RDLC layout.'; ObsoleteTag = '27.0'; } column(TotalCaption; TotalCaptionLbl) { ObsoleteState = Pending; ObsoleteReason = 'RDLC Only layout column. To be removed along with the RDLC layout.'; ObsoleteTag = '27.0'; }#endif dataitem("Value Entry"; "Value Entry") { DataItemLink = "Source No." = field("No."), "Posting Date" = field("Date Filter"), "Global Dimension 1 Code" = field("Global Dimension 1 Filter"), "Global Dimension 2 Code" = field("Global Dimension 2 Filter"); DataItemTableView = sorting("Source Type", "Source No.", "Item No.", "Variant Code", "Posting Date") where("Source Type" = const(Customer), "Item Charge No." = const(''), "Expected Cost" = const(false), Adjustment = const(false)); RequestFilterFields = "Item No.", "Posting Date"; trigger OnAfterGetRecord() var ValueEntry: Record "Value Entry"; EntryInBufferExists: Boolean; begin TempValueEntryBuffer.Init(); TempValueEntryBuffer.SetRange("Item No.", "Item No."); EntryInBufferExists := TempValueEntryBuffer.FindFirst(); if not EntryInBufferExists then TempValueEntryBuffer."Entry No." := "Item Ledger Entry No."; TempValueEntryBuffer."Item No." := "Item No."; TempValueEntryBuffer."Invoiced Quantity" += "Invoiced Quantity"; TempValueEntryBuffer."Sales Amount (Actual)" += "Sales Amount (Actual)"; TempValueEntryBuffer."Cost Amount (Actual)" += "Cost Amount (Actual)"; TempValueEntryBuffer."Cost Amount (Non-Invtbl.)" += "Cost Amount (Non-Invtbl.)"; TempValueEntryBuffer."Discount Amount" += "Discount Amount"; TempItemLedgerEntry.SetRange("Entry No.", "Item Ledger Entry No."); if TempItemLedgerEntry.IsEmpty() then begin TempItemLedgerEntry."Entry No." := "Item Ledger Entry No."; TempItemLedgerEntry.Insert(); // Add item charges regardless of their posting date ValueEntry.SetRange("Item Ledger Entry No.", "Item Ledger Entry No."); ValueEntry.SetFilter("Item Charge No.", '<>%1', ''); ValueEntry.CalcSums("Sales Amount (Actual)", "Cost Amount (Actual)", "Cost Amount (Non-Invtbl.)", "Discount Amount"); TempValueEntryBuffer."Sales Amount (Actual)" += ValueEntry."Sales Amount (Actual)"; TempValueEntryBuffer."Cost Amount (Actual)" += ValueEntry."Cost Amount (Actual)"; TempValueEntryBuffer."Cost Amount (Non-Invtbl.)" += ValueEntry."Cost Amount (Non-Invtbl.)"; TempValueEntryBuffer."Discount Amount" += ValueEntry."Discount Amount"; // Add cost adjustments regardless of their posting date ValueEntry.SetRange("Item Charge No.", ''); ValueEntry.SetRange(Adjustment, true); ValueEntry.CalcSums("Cost Amount (Actual)"); TempValueEntryBuffer."Cost Amount (Actual)" += ValueEntry."Cost Amount (Actual)"; end; OnAfterGetValueEntryOnBeforeTempValueEntryBufferInsertModify("Value Entry", TempValueEntryBuffer); if EntryInBufferExists then TempValueEntryBuffer.Modify() else TempValueEntryBuffer.Insert(); end; trigger OnPreDataItem() begin TempValueEntryBuffer.Reset(); TempValueEntryBuffer.DeleteAll(); end; } dataitem("Integer"; "Integer") { DataItemTableView = sorting(Number); column(CustNo; Customer."No.") { } column(CustName; Customer.Name) { } column(ValueEntryBuffer__Item_No__; TempValueEntryBuffer."Item No.") { } column(Item_Description; Item.Description) { } column(ValueEntryBuffer__Invoiced_Quantity_; -TempValueEntryBuffer."Invoiced Quantity") { DecimalPlaces = 0 : 5; } column(ValueEntryBuffer__Sales_Amount__Actual___Control44; TempValueEntryBuffer."Sales Amount (Actual)") { AutoFormatType = 1; } column(ValueEntryBuffer__Discount_Amount__Control45; -TempValueEntryBuffer."Discount Amount") { AutoFormatType = 1; } column(Profit_Control46; Profit) { AutoFormatType = 1; } column(ProfitPct_Control47; ProfitPct) { DecimalPlaces = 1 : 1; } column(Item__Base_Unit_of_Measure_; Item."Base Unit of Measure") { } trigger OnAfterGetRecord() var Amount: Decimal; begin if Number = 1 then TempValueEntryBuffer.Find('-') else TempValueEntryBuffer.Next(); Profit := TempValueEntryBuffer."Sales Amount (Actual)" + TempValueEntryBuffer."Cost Amount (Actual)" + TempValueEntryBuffer."Cost Amount (Non-Invtbl.)"; Amount := TempValueEntryBuffer."Sales Amount (Actual)"; ProfitPct := CalculateProfitPercent(Amount, Profit); if Item.Get(TempValueEntryBuffer."Item No.") then; SubtotalsAmount += Amount; SubtotalsDiscountAmount += -TempValueEntryBuffer."Discount Amount"; SubtotalsProfit += Profit; SubtotalsProfitPercent := CalculateProfitPercent(SubtotalsAmount, SubtotalsProfit); TotalsAmount += Amount; TotalsDiscountAmount += -TempValueEntryBuffer."Discount Amount"; TotalsProfit += Profit; TotalsProfitPercent := CalculateProfitPercent(TotalsAmount, TotalsProfit); if not ReportHasData then ReportHasData := not TempValueEntryBuffer.IsEmpty(); end; trigger OnPreDataItem() begin TempValueEntryBuffer.Reset(); SetRange(Number, 1, TempValueEntryBuffer.Count()); Clear(Profit); end; } dataitem(Subtotals; Integer) { DataItemTableView = sorting(Number) where(Number = const(1)); column(Subtotals_Amount; SubtotalsAmount) { AutoFormatType = 1; } column(Subtotals_DiscountAmount; SubtotalsDiscountAmount) { AutoFormatType = 1; } column(Subtotals_Profit; SubtotalsProfit) { AutoFormatType = 1; } column(Subtotals_ProfitPercent; SubtotalsProfitPercent) { AutoFormatType = 1; } trigger OnPreDataItem() begin if TempValueEntryBuffer.IsEmpty() or (CurrReport.TargetFormat() = ReportFormat::Excel) then CurrReport.Break(); end; } trigger OnPreDataItem() begin Clear(Profit); end; trigger OnAfterGetRecord() begin Clear(SubtotalsAmount); Clear(SubtotalsDiscountAmount); Clear(SubtotalsProfit); Clear(SubtotalsProfitPercent); end; } dataitem(Totals; Integer) { DataItemTableView = sorting(Number) where(Number = const(1)); column(Totals_Amount; TotalsAmount) { AutoFormatType = 1; } column(Totals_DiscountAmount; TotalsDiscountAmount) { AutoFormatType = 1; } column(Totals_Profit; TotalsProfit) { AutoFormatType = 1; } column(Totals_ProfitPercent; TotalsProfitPercent) { AutoFormatType = 1; } trigger OnPreDataItem() begin if (not ReportHasData) or (CurrReport.TargetFormat() = ReportFormat::Excel) then CurrReport.Break(); end; } } requestpage { AboutTitle = 'About Customer/Item Sales'; AboutText = 'Analyze your item sales per customer to understand sales trends, optimize 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.'; SaveValues = true; layout { area(content) { group(Options) { Caption = 'Options';#if not CLEAN28 field(PrintOnlyOnePerPage; PrintOnlyOnePerPageReq) { ApplicationArea = Basic, Suite; Caption = 'New Page per Customer'; ToolTip = 'Specifies if each customer''s information is printed on a new page if you have chosen two or more customers to be included in the report.'; ObsoleteState = Pending; ObsoleteReason = 'The New Page per Customer option is only supported by the RDLC layout which has been deprecated.'; ObsoleteTag = '28.0';#if CLEAN27 Visible = false;#endif }#endif // Used to set a report header across multiple languages field(RequestPeriodText; PeriodText) { ApplicationArea = All; Caption = 'Period'; ToolTip = 'Specifies the Date Period applied to this report.'; Visible = false; } // Used to set a report header across multiple languages field(RequestCustFilterHeading; CustFilterHeading) { ApplicationArea = All; Caption = 'Customer Filter'; ToolTip = 'Specifies the Customer Filters applied to this report.'; Visible = false; } // Used to set a report header across multiple languages field(RequestValueEntryFilterHeading; ValueEntryFilterHeading) { ApplicationArea = All; Caption = 'Value Entry Filter'; ToolTip = 'Specifies the Value Entry filters applied to this report.'; Visible = false; } } } } actions { } trigger OnClosePage() begin UpdateRequestPageFilterValues(); end; } rendering { layout(Excel) { Caption = 'Customer Item Sales Excel'; Type = Excel; LayoutFile = './Sales/Reports/CustomerItemSales.xlsx'; Summary = 'Report layout primarily made for data analysis. Use an Excel editor to modify the layout.'; } layout(Word) { Caption = 'Customer Item Sales Word'; Type = Word; LayoutFile = './Sales/Reports/CustomerItemSales.docx'; Summary = 'Report layout made for print. Use a Word editor to modify the layout.'; }#if not CLEAN27 layout(RDLC) { Caption = 'Customer Item Sales RDLC (Obsolete)'; Type = RDLC; LayoutFile = './Sales/Reports/CustomerItemSales.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 = 'Report layout made in the legacy RDLC format. Use an RDLC editor to modify the layout.'; }#endif } labels { CustomerItemSalesLabel = 'Customer/Item Sales'; CustomerItemSalesPrint = 'Customer Item Sales (Print)', MaxLength = 31, Comment = 'Excel worksheet name.'; CustomerItemSalesAnalysis = 'Customer Item Sales (Analysis)', MaxLength = 31, Comment = 'Excel worksheet name.'; DataRetrieved = 'Data retrieved:'; PeriodCaption = 'Period:'; CustNoCaption = 'Customer No.'; CustNameCaption = 'Customer Name'; ItemNoCaption = 'Item No.'; ItemDescCaption = 'Item Description'; UnitOfMeasureCaption = 'Unit of Measure'; InvoicedQuantityCaption = 'Invoiced Quantity'; AmountCaption = 'Amount'; DiscountAmountCaption = 'Discount Amount'; ProfitCaption = 'Profit'; ProfitPercentCaption = 'Profit %'; AllAmountsInLCYCaption = 'All amounts are in LCY'; TotalLbl = 'Total'; // About the report labels AboutTheReportLabel = 'About the report'; EnvironmentLabel = 'Environment'; CompanyLabel = 'Company'; UserLabel = 'User'; RunOnLabel = 'Run on'; ReportNameLabel = 'Report name'; DocumentationLabel = 'Documentation'; } trigger OnPreReport() begin UpdateRequestPageFilterValues(); end; trigger OnPostReport() begin if Customer.IsEmpty() and GuiAllowed() then Error(EmptyReportDatasetTxt); end; var TempItemLedgerEntry: Record "Item Ledger Entry" temporary; CustFilter: Text; ValueEntryFilter: Text; CustFilterHeading: Text; ValueEntryFilterHeading: Text; PeriodText: Text;#if not CLEAN28 PrintOnlyOnePerPageReq: Boolean;#endif Profit: Decimal; ProfitPct: Decimal; SubtotalsAmount: Decimal; SubtotalsDiscountAmount: Decimal; SubtotalsProfit: Decimal; SubtotalsProfitPercent: Decimal; TotalsAmount: Decimal; TotalsDiscountAmount: Decimal; TotalsProfit: Decimal; TotalsProfitPercent: Decimal; ReportHasData: Boolean; EmptyReportDatasetTxt: Label 'There is nothing to print for the selected filters.'; PeriodTxt: Label 'Period: %1', Comment = '%1 - period text';#if not CLEAN27 Customer_Item_SalesCaptionLbl: Label 'Customer/Item Sales'; CurrReport_PAGENOCaptionLbl: Label 'Page'; All_amounts_are_in_LCYCaptionLbl: Label 'All amounts are in LCY'; ValueEntryBuffer__Item_No__CaptionLbl: Label 'Item No.'; Item_DescriptionCaptionLbl: Label 'Description'; ValueEntryBuffer__Invoiced_Quantity_CaptionLbl: Label 'Invoiced Quantity'; Item__Base_Unit_of_Measure_CaptionLbl: Label 'Unit of Measure'; ValueEntryBuffer__Sales_Amount__Actual___Control44CaptionLbl: Label 'Amount'; ValueEntryBuffer__Discount_Amount__Control45CaptionLbl: Label 'Discount Amount'; Profit_Control46CaptionLbl: Label 'Profit'; ProfitPct_Control47CaptionLbl: Label 'Profit %'; TotalCaptionLbl: Label 'Total';#endif protected var Item: Record Item; TempValueEntryBuffer: Record "Value Entry" temporary;#if not CLEAN28#pragma warning disable AS0072 /// <summary> /// Initializes the report request options for the Customer Item Sales report. /// </summary> /// <param name="NewPagePerCustomer">True to start a new page per customer.</param> [Obsolete('The New Page per Customer option is only supported by the RDLC layout which has been deprecated.', '28.0')] procedure InitializeRequest(NewPagePerCustomer: Boolean) begin PrintOnlyOnePerPageReq := NewPagePerCustomer; end;#pragma warning restore AS0072#endif /// <summary> /// Calculates the profit percentage based on amount and profit. /// </summary> /// <param name="Amount">The total amount.</param> /// <param name="ProfitAmt">The profit amount.</param> /// <returns>The profit percentage rounded to one decimal.</returns> procedure CalculateProfitPercent(Amount: Decimal; ProfitAmt: Decimal) ProfitPercent: Decimal begin if Amount <> 0 then ProfitPercent := Round((100 * ProfitAmt / Amount), 0.1, '=') else ProfitPercent := 0; end; // Ensures Layout Filter Headings are up to date local procedure UpdateRequestPageFilterValues() var FormatDocument: Codeunit "Format Document"; begin CustFilter := FormatDocument.GetRecordFiltersWithCaptions(Customer); ValueEntryFilter := "Value Entry".GetFilters(); PeriodText := "Value Entry".GetFilter("Posting Date"); CustFilterHeading := ''; ValueEntryFilterHeading := ''; if CustFilter <> '' then CustFilterHeading := Customer.TableCaption + ': ' + CustFilter; if ValueEntryFilter <> '' then ValueEntryFilterHeading := "Value Entry".TableCaption + ': ' + ValueEntryFilter; end; [IntegrationEvent(false, false)] local procedure OnAfterGetValueEntryOnBeforeTempValueEntryBufferInsertModify(ValueEntry: Record "Value Entry"; var TempValueEntry: Record "Value Entry" temporary) begin end;}