Report 723 Item - ABC Analysis
src/Layers/W1/BaseApp/Inventory/Reports/ItemABCAnalysis.Report.al394 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.Analysis;using Microsoft.Inventory.Item;report 723 "Item - ABC Analysis"{ ApplicationArea = Basic, Suite; Caption = 'Item - ABC Analysis (Excel)'; DefaultRenderingLayout = Excel; UsageCategory = ReportsAndAnalysis; dataset { dataitem(Item; Item) { RequestFilterFields = "No.", "Inventory Posting Group", "Gen. Prod. Posting Group", "Date Filter", "Location Filter"; trigger OnPreDataItem() begin PopulateBuffer(); Item.SetRange("No.", ''); end; } dataitem(TempItemABCBuffer; "Item ABC Buffer") { DataItemTableView = sorting("Item No."); column(ItemNo; "Item No.") { IncludeCaption = true; } column(Description; Description) { IncludeCaption = true; } column(InventoryPostingGroup; "Inventory Posting Group") { IncludeCaption = true; } column(SalesLCY; "Sales (LCY)") { IncludeCaption = true; } column(ABC; ABC) { } column(Pct; Pct) { } column(NoA; NoA) { } column(NoB; NoB) { } column(NoC; NoC) { } column(ABLimit; ABLimit) { } column(BCLimit; BCLimit) { } column(AminAmt; AminAmt) { } column(BMinAmt; BMinAmt) { } column(Col1TotalAllRec; Col1TotalAllRec) { } column(NoOfItemsInClass; NoOfItemsInClass) { } trigger OnAfterGetRecord() begin if not PrintZeroLines and ("Sales (LCY)" = 0) then CurrReport.Skip(); NoOfItemsInClass := 1; case true of "Sales (LCY)" >= AminAmt: begin ABC := 'A'; NoA := NoA + 1; end; "Sales (LCY)" >= BMinAmt: begin ABC := 'B'; NoB := NoB + 1; end; else begin ABC := 'C'; NoC := NoC + 1; end; end; if not Aprint and (ABC = 'A') then CurrReport.Skip(); if not BPrint and (ABC = 'B') then CurrReport.Skip(); if not CPrint and (ABC = 'C') then CurrReport.Skip(); Col1TotalStatistic := Col1TotalStatistic + "Sales (LCY)"; if Col1TotalAllRec <> 0 then Pct := Round("Sales (LCY)" / Col1TotalAllRec * 100, 0.01, '='); PCTTotalStatistic := PCTTotalStatistic + Pct; OnAfterTempItemABCBuffer(TempItemABCBuffer, ABC); end; trigger OnPostDataItem() begin Window.Close(); end; } } requestpage { AboutTitle = 'About Item ABC Analysis'; AboutText = 'Analyze your inventory items by ranking them into A, B, and C categories based on their Sales (LCY) amounts. Use the results to identify high-value items, focus on top performers, and support better purchasing and replenishment decisions.'; SaveValues = true; layout { area(content) { group(Options) { Caption = 'Options'; group("Ratio Cat. A/B/C") { Caption = 'Ratio Cat. A/B/C'; field(RatioCatA; APct) { AutoFormatExpression = ''; AutoFormatType = 0; Caption = 'A'; DecimalPlaces = 0; MaxValue = 100; MinValue = 0; ToolTip = 'Specifies items with small volume and high value.'; trigger OnValidate() begin CPct := CalcPercentage(APct, BPct); end; } field(RatioCatB; BPct) { AutoFormatExpression = ''; AutoFormatType = 0; Caption = 'B'; DecimalPlaces = 0; MaxValue = 100; MinValue = 0; ToolTip = 'Specifies items with the medium volume and medium value.'; trigger OnValidate() begin CPct := CalcPercentage(APct, BPct); end; } field(RatioCatC; CPct) { AutoFormatExpression = ''; AutoFormatType = 0; Caption = 'C'; DecimalPlaces = 0; MaxValue = 100; MinValue = 0; ToolTip = 'Specifies items with high volume and small value.'; trigger OnValidate() begin BPct := CalcPercentage(APct, CPct); end; } } field(ShowCategoryA; Aprint) { Caption = 'Show Category A'; ToolTip = 'Specifies that this category of items are shown.'; } field(ShowCategoryB; BPrint) { Caption = 'Show Category B'; ToolTip = 'Specifies that this category of items are shown.'; } field(ShowCategoryC; CPrint) { Caption = 'Show Category C'; ToolTip = 'Specifies that this category of items are shown.'; } field(PrintZero; PrintZeroLines) { Caption = 'Print Lines with 0'; ToolTip = 'Specifies that lines with no valuation are included. These lines are often excluded from the analysis.'; } // Used to set a report header across multiple languages field(RequestItemFilterHeading; ItemFilterHeading) { Caption = 'Item Filter'; ToolTip = 'Specifies the Item Filters applied to this report.'; Visible = false; } } } } trigger OnClosePage() begin UpdateRequestPageFilterValues(); end; } rendering { layout(Excel) { Caption = 'Item ABC Analysis'; Type = Excel; LayoutFile = './Inventory/Reports/ItemABCAnalysis.xlsx'; Summary = 'Built in layout for the Item ABC Analysis Excel report.'; } } labels { ItemABCAnalysisPrintHeadingLbl = 'Item ABC Analysis'; ItemABCAnalysisPrintLbl = 'Item ABC Analysis (Print)', MaxLength = 31, Comment = 'Excel worksheet name.'; ItemABCAnalysisLbl = 'Item ABC (Analysis)', MaxLength = 31, Comment = 'Excel worksheet name.'; ItemABCAnalysisStructureLbl = 'Item ABC Analysis (Structure)', MaxLength = 31, Comment = 'Excel worksheet name.'; ItemABCAnalysisStructureHeadingLbl = 'Item ABC Analysis Structure'; DataRetrievedLbl = 'Data retrieved:'; PercentageABCLbl = 'Percentage A/B/C:'; ABCLbl = 'A/B/C'; PercentageLbl = '%'; TotalLbl = 'Total'; ALbl = 'A'; BLbl = 'B'; CLbl = 'C'; RangeLbl = 'Range'; ShareLbl = 'Share (%)'; NoLbl = 'No'; FromCumValueLbl = 'From Cumulative Value'; FromSingleValueLbl = 'From Single Value'; // About the report labels AboutTheReportLbl = 'About the report'; EnvironmentLbl = 'Environment'; CompanyLbl = 'Company'; UserLbl = 'User'; RunOnLbl = 'Run on'; ReportNameLbl = 'Report name'; DocumentationLbl = 'Documentation'; NoOfItemsInClassLbl = 'No. of items in class'; } trigger OnInitReport() var ABCAnalysisSetup: Record "ABC Analysis Setup"; begin ABCAnalysisSetup.Get(); if APct = 0 then begin APct := ABCAnalysisSetup."Category A"; BPct := ABCAnalysisSetup."Category B"; CPct := ABCAnalysisSetup."Category C"; Aprint := true; BPrint := true; CPrint := true; end; end; trigger OnPreReport() begin UpdateRequestPageFilterValues(); end; var PreparingLbl: Label 'Preparing Analysis\'; TotalInAnalysisLbl: Label 'Total in Analysis #1#########\', Comment = '#1 = Number of Items to calculate'; CalculatedLbl: Label 'Calculated #2#########\', Comment = '#2 = Number of Items calculated.'; ItemNoLbl: Label 'Item No. #3#########', Comment = '#3 = Item No.'; Window: Dialog; NoCalculated: Integer; APct: Decimal; BPct: Decimal; CPct: Decimal; ABLimit: Decimal; BCLimit: Decimal; AminAmt: Decimal; BMinAmt: Decimal; CumAmt: Decimal; Aprint: Boolean; BPrint: Boolean; CPrint: Boolean; NoA: Integer; NoB: Integer; NoC: Integer; PrintZeroLines: Boolean; Col1TotalAllRec: Decimal; Col1TotalStatistic: Decimal; Pct: Decimal; PCTTotalStatistic: Decimal; ItemFilterHeading: Text; NoOfItemsInClass: Integer; protected var ABC: Text[1]; // Ensures Layout Filter Headings are up to date local procedure UpdateRequestPageFilterValues() var ItemFilter: Text; begin ItemFilter := Item.GetFilters(); if ItemFilter <> '' then ItemFilterHeading := Item.TableCaption + ': ' + ItemFilter else ItemFilterHeading := ''; end; local procedure PopulateBuffer() begin Window.Open( PreparingLbl + TotalInAnalysisLbl + CalculatedLbl + ItemNoLbl); Window.Update(1, Format(Item.Count)); Item.SetLoadFields(Description, "Inventory Posting Group"); if Item.FindSet() then repeat Item.CalcFields("Sales (LCY)"); TempItemABCBuffer.Init(); TempItemABCBuffer."Sales (LCY)" := Item."Sales (LCY)"; TempItemABCBuffer."Item No." := Item."No."; TempItemABCBuffer.Description := Item.Description; TempItemABCBuffer."Inventory Posting Group" := Item."Inventory Posting Group"; TempItemABCBuffer.Insert(); Col1TotalAllRec := Col1TotalAllRec + Item."Sales (LCY)"; NoCalculated := NoCalculated + 1; if NoCalculated mod 100 = 0 then begin Window.Update(2, Format(NoCalculated)); Window.Update(3, Format(Item."No.")); end; until Item.Next() = 0; Item.FindFirst(); ABLimit := Col1TotalAllRec / 100 * (BPct + CPct); BCLimit := Col1TotalAllRec / 100 * CPct; TempItemABCBuffer.SetCurrentKey("Sales (LCY)", "Item No."); if TempItemABCBuffer.FindSet() then repeat CumAmt := CumAmt + TempItemABCBuffer."Sales (LCY)"; if (CumAmt > BCLimit) and (BMinAmt = 0) then BMinAmt := TempItemABCBuffer."Sales (LCY)"; if (CumAmt > ABLimit) and (AminAmt = 0) then AminAmt := TempItemABCBuffer."Sales (LCY)"; until TempItemABCBuffer.Next() = 0; end; local procedure CalcPercentage(Value1: Decimal; Value2: Decimal): Decimal var MaxValueErrorErr: Label 'The sum of percentages cannot exceed 100%. Please adjust the values and try again.'; begin if Value1 + Value2 >= 100 then Error(MaxValueErrorErr); exit(100 - Value1 - Value2); end; [IntegrationEvent(true, false)] local procedure OnAfterTempItemABCBuffer(var ItemABCBuffer: Record "Item ABC Buffer"; ABC: Text[1]) begin end;}