Report 114 Salesperson - Sales Statistics

Base Application, Microsoft.Sales.Reports

src/Layers/W1/BaseApp/Sales/Reports/SalespersonSalesStatistics.Report.al375 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 sales statistics by salesperson showing sales amounts, profits, discounts, and adjusted profit calculations./// </summary>using Microsoft.CRM.Team;using Microsoft.Inventory.Costing;using Microsoft.Sales.Receivables;using System.Utilities;report 114 "Salesperson - Sales Statistics"{    ApplicationArea = Basic, Suite;    Caption = 'Salesperson - Sales Statistics';    ToolTip = 'View amounts for sales, profit, invoice discount, and payment discount, as well as profit percentage, for each salesperson for a selected period. The report also shows the adjusted profit and adjusted profit percentage, which reflect any changes to the original costs of the items in the sales.';    DefaultRenderingLayout = Excel;    PreviewMode = PrintLayout;    UsageCategory = ReportsAndAnalysis;    dataset    {        dataitem("Salesperson/Purchaser"; "Salesperson/Purchaser")        {            DataItemTableView = sorting(Code);            RequestFilterFields = "Code";            column(STRSUBSTNO_Text000_PeriodText_; StrSubstNo(PeriodTxt, PeriodText))            {            }            column(Salesperson_Purchaser__TABLECAPTION__________SalespersonFilter; SalespersonFilterHeading)            {            }            column(Cust__Ledger_Entry__TABLECAPTION__________CustLedgEntryFilter; CustLedgEntryFilterHeading)            {            }            column(Cust__Ledger_Entry___Sales__LCY__; SalesLCY)            {                AutoFormatType = 1;            }            column(Cust__Ledger_Entry___Profit__LCY__; ProfitLCY)            {                AutoFormatType = 1;            }            column(ProfitPercent; ProfitPercent)            {                AutoFormatType = 1;            }            column(Cust__Ledger_Entry___Inv__Discount__LCY__; InvDiscLCY)            {                AutoFormatType = 1;            }            column(Cust__Ledger_Entry___Pmt__Disc__Given__LCY__; PmtDiscGivenLCY)            {                AutoFormatType = 1;            }            column(Cust__Ledger_Entry___Pmt__Tolerance__LCY__; PmtToleranceLCY)            {                AutoFormatType = 1;            }            column(AdjProfit; AdjProfit)            {                AutoFormatType = 1;            }            column(AdjProfitPercent; AdjProfitPercent)            {                AutoFormatType = 1;            }            column(Salesperson_Purchaser_Code; Code)            {                IncludeCaption = true;            }#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(SalespersonFilter; SalespersonFilter)            {                ObsoleteState = Pending;                ObsoleteReason = 'RDLC Only layout column. To be removed along with the RDLC layout.';                ObsoleteTag = '27.0';            }            column(CustLedgEntryFilter; CustLedgEntryFilter)            {                ObsoleteState = Pending;                ObsoleteReason = 'RDLC Only layout column. To be removed along with the RDLC layout.';                ObsoleteTag = '27.0';            }#endif            trigger OnAfterGetRecord()            var                [SecurityFiltering(SecurityFilter::Filtered)]                CustLedgEntry: Record "Cust. Ledger Entry";                CostCalculationMgt: Codeunit "Cost Calculation Management";            begin                SalesLCY := 0;                ProfitLCY := 0;                InvDiscLCY := 0;                PmtDiscGivenLCY := 0;                PmtToleranceLCY := 0;                AdjProfit := 0;                CustLedgEntry.CopyFilters("Cust. Ledger Entry");                CustLedgEntry.SetRange("Salesperson Code", Code);                if CustLedgEntry.FindSet() then begin                    repeat                        SalesLCY += CustLedgEntry."Sales (LCY)";                        ProfitLCY += CustLedgEntry."Profit (LCY)";                        InvDiscLCY += CustLedgEntry."Inv. Discount (LCY)";                        PmtDiscGivenLCY += CustLedgEntry."Pmt. Disc. Given (LCY)";                        PmtToleranceLCY += CustLedgEntry."Pmt. Tolerance (LCY)";                        if CustLedgEntry."Document Type" in [CustLedgEntry."Document Type"::Invoice,                                                             CustLedgEntry."Document Type"::"Credit Memo"]                        then                            AdjProfit += CustLedgEntry."Profit (LCY)" + CostCalculationMgt.CalcCustLedgAdjmtCostLCY(CustLedgEntry)                        else                            AdjProfit += CustLedgEntry."Profit (LCY)"                    until CustLedgEntry.Next() = 0;                    ProfitPercent := CalculateProfitPercent(SalesLCY, ProfitLCY);                    AdjProfitPercent := CalculateProfitPercent(SalesLCY, AdjProfit);                    // Calculate Totals for Word Layout                    TotalsSales += SalesLCY;                    TotalsProfit += ProfitLCY;                    TotalsAdjProfit += AdjProfit;                    TotalsProfitPct := CalculateProfitPercent(TotalsSales, TotalsProfit);                    TotalsAdjProfitPct := CalculateProfitPercent(TotalsSales, TotalsAdjProfit);                    TotalsInvDiscAmount += InvDiscLCY;                    TotalsPmtDiscGiven += PmtDiscGivenLCY;                    TotalsPmtTolerance += PmtToleranceLCY;                    if not ReportHasData then                        ReportHasData := true;                end else                    CurrReport.Skip();            end;        }        dataitem("Cust. Ledger Entry"; "Cust. Ledger Entry")        {            MaxIteration = 0;            RequestFilterFields = "Posting Date";        }        dataitem(Totals; Integer)        {            DataItemTableView = sorting(Number) where(Number = const(1));            column(Totals_Sales; TotalsSales)            {                AutoFormatType = 1;            }            column(Totals_Profit; TotalsProfit)            {                AutoFormatType = 1;            }            column(Totals_AdjProfit; TotalsAdjProfit)            {                AutoFormatType = 1;            }            column(Totals_ProfitPct; TotalsProfitPct)            {                AutoFormatType = 1;            }            column(Totals_AdjProfitPct; TotalsAdjProfitPct)            {                AutoFormatType = 1;            }            column(Totals_InvDiscAmount; TotalsInvDiscAmount)            {                AutoFormatType = 1;            }            column(Totals_PmtDiscGiven; TotalsPmtDiscGiven)            {                AutoFormatType = 1;            }            column(Totals_PmtTolerance; TotalsPmtTolerance)            {                AutoFormatType = 1;            }            trigger OnPreDataItem()            begin                if not ReportHasData then                    CurrReport.Break();            end;        }    }    requestpage    {        AboutTitle = 'About Salesperson - Sales Statistics';        AboutText = 'Analyze the sales contributions by salesperson. Provides data on Sales, Profits, Discounts and more.';        layout        {            area(content)            {                group(Options)                {                    Caption = 'Options';                    // Used to set the date filter on the 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 the Salesperson Filter on the report header across multiple languages                    field(RequestSalespersonFilterHeading; SalespersonFilterHeading)                    {                        ApplicationArea = All;                        Caption = 'Salesperson Filter';                        ToolTip = 'Specifies the Salesperson Filters applied to this report.';                        Visible = false;                    }                    // Used to set the Cust. Ledg. Entry Filter on the report header across multiple languages                    field(RequestCustLedgEntryFilterHeading; CustLedgEntryFilterHeading)                    {                        ApplicationArea = All;                        Caption = 'Cust. Ledg. Entry Filter';                        ToolTip = 'Specifies the Customer Ledger Entry filters applied to this report.';                        Visible = false;                    }                }            }        }        actions        {        }        trigger OnClosePage()        begin            // Ensures Layout Filter Headings are up to date            UpdateRequestPageFilterValues();        end;    }    rendering    {        layout(Excel)        {            Caption = 'Salesperson Sales Statistics Excel';            Type = Excel;            LayoutFile = './Sales/Reports/SalespersonSalesStatistics.xlsx';            Summary = 'Report layout primarily made for data analysis. Use an Excel editor to modify the layout.';        }        layout(Word)        {            Caption = 'Salesperson Sales Statistics Word';            Type = Word;            LayoutFile = './Sales/Reports/SalespersonSalesStatistics.docx';            Summary = 'Report layout made for print. Use a Word editor to modify the layout.';        }#if not CLEAN27        layout(RDLC)        {            Caption = 'Salesperson Sales Statistics RDLC';            Type = RDLC;            LayoutFile = './Sales/Reports/SalespersonSalesStatistics.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        layout(WordBody)        {            Type = Word;            Subtype = Body;            LayoutFile = './Sales/Reports/SalespersonSalesStatisticsBody.docx';            Caption = 'Body-only: Salesperson Sales Statistics Word';            Summary = 'Landscape orientated. Shows sales, profit, and adjusted profit in LCY and as a percentage for each salesperson, together with invoice discount, payment discount given, and payment tolerance. Includes column totals.';        }    }    labels    {        ReportLabel = 'Salesperson - Sales Statistics';        SalespersonSalesStatsPrint = 'Salesperson Sales Stats (Print)', MaxLength = 31, Comment = 'Excel worksheet name.';        SalespersonSalesStatsAnalysis = 'Salesp. Sales Stats (Analysis)', MaxLength = 31, Comment = 'Excel worksheet name.';        DataRetrieved = 'Data retrieved:';        PeriodCaption = 'Period:';        PageLabel = 'Page';        AmountsInLCYLabel = 'All amounts are in LCY';        ProfitPctLabel = 'Profit %';        InvDiscAmountLabel = 'Invoice Disc. Amount (LCY)';        PmtDiscGivenLabel = 'Payment Disc. Given (LCY)';        PmtToleranceLabel = 'Pmt. Tolerance (LCY)';        AdjProfitPctLabel = 'Adjusted Profit %';        AdjProfitLCYLabel = 'Adjusted Profit (LCY)';        TotalLabel = 'Total';        SalesLCYLabel = 'Sales (LCY)';        ProfitLCYLabel = 'Profit (LCY)';        CodeLabel = 'Code';        // 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        "Cust. Ledger Entry".SecurityFiltering(SecurityFilter::Filtered);        UpdateRequestPageFilterValues();    end;    var        PeriodTxt: Label 'Period: %1', Comment = '%1 - period text';        SalespersonFilter: Text;        CustLedgEntryFilter: Text;        SalespersonFilterHeading: Text;        CustLedgEntryFilterHeading: Text;        PeriodText: Text;        AdjProfit: Decimal;        AdjProfitPercent: Decimal;        SalesLCY: Decimal;        ProfitLCY: Decimal;        ProfitPercent: Decimal;        InvDiscLCY: Decimal;        PmtDiscGivenLCY: Decimal;        PmtToleranceLCY: Decimal;        TotalsSales: Decimal;        TotalsProfit: Decimal;        TotalsAdjProfit: Decimal;        TotalsProfitPct: Decimal;        TotalsAdjProfitPct: Decimal;        TotalsInvDiscAmount: Decimal;        TotalsPmtDiscGiven: Decimal;        TotalsPmtTolerance: Decimal;        ReportHasData: Boolean;    /// <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) ProfitPct: Decimal    begin        if Amount <> 0 then            ProfitPct := Round((100 * ProfitAmt / Amount), 0.1, '=')        else            ProfitPct := 0;    end;    // Ensures Layout Filter Headings are up to date    local procedure UpdateRequestPageFilterValues()    begin        SalespersonFilter := "Salesperson/Purchaser".GetFilters();        CustLedgEntryFilter := "Cust. Ledger Entry".GetFilters();        PeriodText := "Cust. Ledger Entry".GetFilter("Posting Date");        SalespersonFilterHeading := '';        CustLedgEntryFilterHeading := '';        if SalespersonFilter <> '' then            SalespersonFilterHeading := "Salesperson/Purchaser".TableCaption + ': ' + SalespersonFilter;        if CustLedgEntryFilter <> '' then            CustLedgEntryFilterHeading := "Cust. Ledger Entry".TableCaption + ': ' + CustLedgEntryFilter;    end;}