NetSuite open sales orders report: getting the backlog right

Ask three people in a NetSuite account for the open sales orders report and you get three different totals, all correct. The warehouse counts anything still to pick. The controller counts anything still to invoice. Sales counts anything not yet delivered to the customer. Before you build anything, decide which of those you're being asked for, because NetSuite's sales order statuses were designed to serve all three at once and they do so by being ambiguous.

What each sales order status actually means

A NetSuite sales order moves through a fixed set of statuses. The two columns on the right are the ones people get wrong.

Why there isn't a canned report for this

NetSuite gives you several places to see open orders and none of them is a backlog report. The sales order list view filtered by status is fine for eyeballing a handful. The fulfilment workflow shows you what can be shipped now, which is a different and narrower question — it is filtered by what's actually available. Sales reports total amounts over a date range rather than remaining quantity as at right now.

What nobody ships out of the box is line-level remaining quantity with a value attached, grouped how you want it. That's why practically every NetSuite account ends up with a saved search called something like "Open Order Backlog — DO NOT EDIT".

The saved search that works

Build it as a transaction saved search on sales orders, at line level. The criteria matter more than the results.

• Type is Sales Order. Not "sales transactions" — that pulls in invoices raised from these same orders and doubles the backlog.
• Main Line is false. You want item lines, not the header. With Main Line true you get one row per order and no quantity detail.
• Shipping Line and Tax Line are false. Otherwise freight and tax lines arrive with quantities of 1 and pollute your unit counts.
• Closed is false, at line level. This is the important one. Cancelling an order closes its lines, and individual lines can be closed while the rest of the order ships. Line-level Closed handles both cases without you maintaining a status exclusion list.
• Remaining quantity greater than zero, as a formula on ordered quantity minus fulfilled quantity. This is what makes Partially Fulfilled orders report only their remainder.

For results, the four quantity fields on a sales order line are the whole story: ordered quantity, quantity fulfilled or received, quantity billed, and — where inventory is involved — quantity committed and quantity backordered. Put them side by side with a formula for the remainder and a second formula for the value of the remainder, and you have a report you can hand to three different departments.

Remaining qty:   NVL({quantity},0) - NVL({quantityshiprecv},0)
Remaining value: (NVL({quantity},0) - NVL({quantityshiprecv},0)) * NVL({rate},0)

The SuiteQL version

The same definition in SuiteQL, which is easier to schedule and easier to review because the logic sits in one visible place:

SELECT
  BUILTIN.DF(t.entity)                    AS customer,
  t.tranid                                AS order_no,
  t.trandate,
  BUILTIN.DF(t.status)                    AS order_status,
  BUILTIN.DF(tl.item)                     AS item,
  ABS(tl.quantity)                        AS qty_ordered,
  ABS(COALESCE(tl.quantityshiprecv, 0))   AS qty_fulfilled,
  ABS(tl.quantity) - ABS(COALESCE(tl.quantityshiprecv, 0)) AS qty_open,
  ROUND(
    (ABS(tl.quantity) - ABS(COALESCE(tl.quantityshiprecv, 0)))
    * ABS(COALESCE(tl.rate, 0)), 2)       AS open_value
FROM transaction t
JOIN transactionline tl
  ON tl.transaction = t.id
WHERE t.type      = 'SalesOrd'
  AND tl.mainline = 'F'
  AND tl.taxline  = 'F'
  AND tl.isclosed = 'F'
  AND ABS(tl.quantity) - ABS(COALESCE(tl.quantityshiprecv, 0)) > 0
ORDER BY t.trandate, t.tranid

Four things about that query worth knowing before you rely on it.

1. 1.Check the signs in your own account. Sales-side line quantities and amounts commonly come back negative from the transaction tables, which is why ABS() appears. Run the query without it against one order whose quantities you know, and confirm before you publish anything.
2. 2.Status keys are opaque. SuiteQL returns status as a type:key pair rather than a label. Rather than trusting a mapping you found online, run a one-off SELECT DISTINCT status, BUILTIN.DF(status) FROM transaction WHERE type = 'SalesOrd' and read the mapping out of your own data. Then prefer the line-level isclosed filter anyway — it needs no mapping at all.
3. 3.Filter out the line types you don't want. Description lines, subtotals, discounts and shipping items all live in transactionline alongside your item lines. Filtering on the line's item type keeps the unit counts honest; check which type values actually appear in your data rather than assuming.
4. 4.Drop-ship and special-order lines behave differently. They're fulfilled by the supplier against a linked purchase order, so quantity fulfilled moves when the supplier ships, not when your warehouse does. If you sell both ways, report them as two blocks. The purchase order side of that story is in the NetSuite purchase order status report.

If the query arrives in your inbox as one 900-character line — and it will — the SuiteQL formatter puts the clause breaks back in.

Backorders: the part everyone asks for second

"Open" splits into two piles that need completely different responses: quantity you have and haven't shipped, and quantity you don't have. The first is a fulfilment queue problem. The second is a purchasing problem, and it's the one that costs you customers.

• Report committed and backordered quantity as separate columns. Committed means the stock is allocated and the order is a picking task. Backordered means the promise is unfunded. A single "open" number hides which is which.
• Join backordered lines to inbound supply. The useful column next to a backordered quantity is the expected receipt date of the purchase order that will clear it. Without it, the report generates questions instead of answering them.
• Convert coverage into days. On-hand divided by average daily usage tells you which backorders are about to become a second wave — the days of supply calculator does that arithmetic, and reorder point: formula and guide covers the buffer settings that stop it recurring.
• Price the failure. Lost margin plus expedited freight plus the goodwill cost gives you a number per stockout event; the stockout cost calculator turns a fill-rate percentage into money, which is the version that gets a purchasing budget approved.

When the saved search stops coping

Backlog reports have a habit of growing. Someone wants it split by warehouse, then by ship-method, then with last year's same-week figure alongside for comparison, then with a margin column. Saved searches hit real ceilings on joins and summary maths, and the usual symptom is a formula that works until you group it. Where those limits sit, and what to do when you reach one, is in NetSuite saved search limitations.

The one-sentence version

Ask ERPray "what's my open order backlog by expected ship week, split committed versus backordered, with value" and you get the number computed live from your own NetSuite account with the SuiteQL printed underneath — including which status and closed-line filters it applied, so you can check the definition rather than inherit someone else's. Read-only by default; no order is touched.