01
About this dashboard
Three tiles give total inventory value, the number of item and location pairs at or below their reorder point, and the value of slow-moving stock. Below them are value by location, value by item type, quantity out by month for each transaction type, the reorder list and the slow-moving list.
The Location filter applies to the value tile, the reorder tile, both value charts and the reorder list. The slow-moving widgets and the monthly chart cover all locations.
02
What it carries
The dashboard reads 4 reports. They travel in the same file, so the import creates them first.
- ReportInventory valuation summaryOn-hand quantity and value by location and item type, from the item location records.
- ReportItems below reorder pointItems whose available quantity at a location is at or below the reorder point, with a suggested order quantity.
- ReportSlow-moving itemsItems with stock on hand that have not been sold or shipped in more than 90 days.
- ReportInventory movements by monthInventory documents per month and transaction type, with the quantity going in and out.
03
Sample preview
Fictional figures in the shape you will see. Yours come from your account.
| Widget | Kind | Reads |
|---|---|---|
| Inventory value | stat | Inventory valuation summary |
| Items at or below reorder point | stat | Items below reorder point |
| Slow-moving stock value | stat | Slow-moving items |
| Inventory value by location | hbar | Inventory valuation summary |
| Inventory value by item type | donut | Inventory valuation summary |
| Quantity out by month | line | Inventory movements by month |
| Items at or below reorder point | table | Items below reorder point |
| Slow-moving items | table | Slow-moving items |
04
The SuiteQL
This is the query each report runs. Read it, copy it, or change it after import.
SELECT BUILTIN.DF(a.location) AS location,
i.itemtype AS item_type,
COUNT(DISTINCT a.item) AS items,
SUM(a.quantityonhand) AS on_hand,
SUM(a.onhandvaluemli) AS value,
SUM(a.onhandvaluemli) / NULLIF(SUM(SUM(a.onhandvaluemli)) OVER (), 0) AS share
FROM aggregateitemlocation a
JOIN item i ON i.id = a.item
WHERE NVL(a.quantityonhand, 0) <> 0
GROUP BY BUILTIN.DF(a.location), i.itemtype
ORDER BY value DESCSELECT i.itemid AS item,
i.displayname AS description,
BUILTIN.DF(a.location) AS location,
a.quantityonhand AS on_hand,
a.quantityavailable AS available,
a.quantityonorder AS on_order,
a.reorderpoint AS reorder_point,
a.preferredstocklevel AS preferred_level,
GREATEST(NVL(a.preferredstocklevel, a.reorderpoint) - NVL(a.quantityavailable, 0) - NVL(a.quantityonorder, 0), 0) AS suggested_qty
FROM aggregateitemlocation a
JOIN item i ON i.id = a.item
WHERE i.isinactive = 'F'
AND a.reorderpoint > 0
AND NVL(a.quantityavailable, 0) <= a.reorderpoint
ORDER BY location, i.itemidSELECT i.itemid AS item,
i.displayname AS description,
i.itemtype AS item_type,
oh.on_hand,
oh.value,
NVL(TO_CHAR(lo.last_out, 'YYYY-MM-DD'), 'Never') AS last_outbound,
TRUNC(SYSDATE) - TRUNC(lo.last_out) AS days_since
FROM (SELECT a.item, SUM(a.quantityonhand) AS on_hand, SUM(a.onhandvaluemli) AS value
FROM aggregateitemlocation a
GROUP BY a.item
HAVING SUM(a.quantityonhand) > 0) oh
JOIN item i ON i.id = oh.item
LEFT JOIN (SELECT tl.item, MAX(t.trandate) AS last_out
FROM transactionline tl
JOIN transaction t ON t.id = tl.transaction
WHERE t.type IN ('CustInvc', 'CashSale', 'ItemShip')
AND tl.mainline = 'F'
GROUP BY tl.item) lo ON lo.item = oh.item
WHERE lo.last_out IS NULL
OR lo.last_out < TRUNC(SYSDATE) - 90
ORDER BY oh.value DESCSELECT TO_CHAR(t.trandate, 'YYYY-MM') AS period,
BUILTIN.DF(t.type) AS type,
COUNT(DISTINCT t.id) AS documents,
SUM(CASE WHEN tl.quantity > 0 THEN tl.quantity ELSE 0 END) AS qty_in,
SUM(CASE WHEN tl.quantity < 0 THEN -tl.quantity ELSE 0 END) AS qty_out
FROM transaction t
JOIN transactionline tl ON tl.transaction = t.id
WHERE t.type IN ('ItemRcpt', 'ItemShip', 'InvAdjst', 'InvTrnfr', 'TrnfrOrd', 'Build', 'Unbuild', 'WOIssue', 'WOCompl', 'InvWksht')
AND tl.mainline = 'F'
AND tl.itemtype IN ('InvtPart', 'Assembly')
AND t.trandate >= ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -23)
GROUP BY TO_CHAR(t.trandate, 'YYYY-MM'), BUILTIN.DF(t.type)
ORDER BY period DESC, type