01
About this report
Items ranked by net sales over the last 365 days. Net sales are invoices and cash sales less credit memos, before tax, in base currency. Average price is net sales divided by net quantity, so credit memos count against both.
Filter on Item type to separate services from goods. Sort by Quantity to find high-volume items that bring in little revenue.
02
Sample preview
Fictional figures in the shape you will see. Yours come from your account.
| Item | Item type | Transactions | Quantity | Net sales | Average price |
|---|---|---|---|---|---|
| Implementation services | Service | 212 | 4,180 | 794,200 | 190 |
| Annual support plan | Service | 58 | 58 | 696,000 | 12,000 |
| Training day | Service | 44 | 61 | 118,950 | 1,950 |
| Integration connector licence | NonInvtPart | 19 | 23 | 103,500 | 4,500 |
| Travel | OthCharge | 87 | 87 | 41,230.60 | 473.92 |
03
The SuiteQL
This is the query each report runs. Read it, copy it, or change it after import.
SELECT BUILTIN.DF(tl.item) AS item,
tl.itemtype AS item_type,
COUNT(DISTINCT t.id) AS transactions,
SUM(-tl.quantity) AS quantity,
SUM(-tl.foreignamount * t.exchangerate) AS sales,
SUM(-tl.foreignamount * t.exchangerate) / NULLIF(SUM(-tl.quantity), 0) AS avg_price
FROM transaction t
JOIN transactionline tl ON tl.transaction = t.id
WHERE t.type IN ('CustInvc', 'CashSale', 'CustCred')
AND t.posting = 'T'
AND tl.mainline = 'F'
AND tl.taxline = 'F'
AND NVL(tl.iscogs, 'F') = 'F'
AND tl.itemtype NOT IN ('TaxGroup', 'TaxItem', 'Subtotal', 'Description', 'EndGroup')
AND t.trandate > TRUNC(SYSDATE) - 365
GROUP BY BUILTIN.DF(tl.item), tl.itemtype
ORDER BY sales DESC