Report 113 Customer/Item Sales, source in 29
Source29
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;
}