Cost of Goods Sold Report
Generates a sales report with accurate net values, correct cost calculation (including composite items), and ensures shipping cost is not duplicated.
Overview
The Cost of Goods Sold Report script provides accurate sales data at the item level while avoiding duplication issues.
It ensures correct cost calculation for both normal and composite items and includes shipping cost at the order level without repeating it across rows.
Uses composite item logic and parent item filtering to avoid duplicate data and incorrect cost calculations.
Purpose
This report:
-
Calculates accurate net sales value
-
Handles composite item cost calculation
-
Prevents duplicate shipping cost
-
Filters only parent items (no component duplication)
👉 In short:
Accurate sales + correct cost + no duplication.
When It Runs
Executed on-demand (SQL report)
Typically used:
-
Sales analysis
-
Profit calculation
-
Financial reporting
-
SKU-level performance tracking
Parameters
Start date for the report.
End date for the report.
Filter by order source (use "All" for no filtering).
How It Works
Fetch Orders
Retrieves orders within the selected date range.
Join Order Data
Joins:
-
Order
-
OrderItem
-
StockItem
Filter Parent Items
Uses:
fkCompositeParentRowId IS NULL
👉 Prevents duplicate rows from composite child items.
Calculate Cost Per Unit
-
If composite item → Sum of child item cost × quantity
-
Else → Direct item cost
Calculate Total Cost
Total Cost = Quantity × Cost per unit
Calculate Net Sales
Net Sale = Price per unit × Quantity
Handle Shipping Cost
Shipping cost is taken from order level
👉 Ensures no duplication across items
Apply Source Filter
Filters by source if provided
Else includes all sources
Sort Results
Orders sorted by:
-
Order Number
-
SKU
Final Result
-
Accurate net sales values
-
Correct cost calculation (including composites)
-
No duplicate shipping cost
-
Clean item-level report (parent items only)
-
Reliable data for financial analysis
Usefulness
This report is highly beneficial for:
-
Profit margin analysis
-
SKU-level cost tracking
-
Financial reporting accuracy
-
Eliminating duplicate data issues
-
Understanding true revenue vs cost
Important Notes
-
Composite cost is calculated dynamically (may impact performance)
-
Shipping cost appears per row but represents order-level value
-
Only parent items are included (child components excluded)
-
Requires correct PurchasePrice setup for accuracy
-
Date filter is mandatory
Example Scenario
Key Insight
The most critical part of this report is the combination of:
-
Composite cost calculation
-
Parent item filtering
-
Order-level shipping handling
This ensures accurate financial reporting while eliminating duplicate and misleading data.
Last updated today
Built with Documentation.AI