Report 114 Salesperson - Sales Statistics
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;}