Report 723 Item - ABC Analysis

Base Application, Microsoft.Inventory.Reports

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;}