How to run an AR aging report in NetSuite

You need an AR aging report in NetSuite, you need it before a 10am call, and the number you produce is going to be repeated to someone who will not check it. That is the actual job. NetSuite ships a report that does most of it, and three or four mechanics that will quietly make your buckets wrong if you don't know about them.

AR aging report

A list of open customer receivables grouped by how long each has been outstanding — typically Current, 1–30, 31–60, 61–90 and 90+ days. It answers two questions at once: how much is owed, and how much of it is late enough to worry about.

The standard A/R Aging report

NetSuite has two canned aging reports in the Customers/Receivables report category: A/R Aging Summary (one line per customer) and A/R Aging Detail (one line per open transaction). Both are real reports, both run in seconds, and for most Monday-morning purposes the Summary is the right answer.

• As-of date — the report re-ages open items as at the date you pick, so you can produce last month's aging today. It is not a snapshot that was stored at month end; it is recalculated from the current transaction data every time you run it.
• Aging basis — the buckets can be driven by due date or by transaction date. Due date is what collections wants. Transaction date is what some auditors ask for. They give different answers for the same ledger, so agree on one.
• Subsidiary and currency context — in OneWorld, what you see depends on the subsidiary context the report is run in and the currency you display. Consolidated views translate child-subsidiary balances at a rate, so a consolidated 90+ total is not the sum of the local-currency numbers you were shown last week.
• Customisation — you can change the number of aging periods and the days per period, add columns, and save the result as your own report. That covers a surprising amount of ground before you need a saved search.

Where the standard report runs out

The canned report is a reporting object, not a working list. Four things push teams off it, in roughly this order of frequency:

1. 1.You need a field it doesn't carry. Collector, credit hold flag, sales rep, customer category, dunning stage, PO number, last promise-to-pay date. The aging report has a fixed field vocabulary; a transaction saved search has almost the whole record.
2. 2.You need to act on rows. A saved search result can be sorted, mass-emailed, driven into a dashboard portlet and clicked straight through to the invoice. A report is something you read.
3. 3.Your buckets aren't 30/60/90. If your terms are net 45, a 1–30 bucket mixes not-yet-due and late invoices together and the report stops meaning anything.
4. 4.You need it to tie to the GL. The aging report is transaction-date driven. Your AR control account is posting-period driven. Reconciling those two takes a different query, covered further down.

Build the saved search version

This is the version worth owning. It reproduces the aging buckets from open transaction balances, one row per transaction, with any field you like attached. Six steps.

1. 01

   Start a transaction saved search

   Transaction is the right search type — not Customer. You want one row per open document, and you want the due date, which lives on the transaction.

2. 02

   Filter by open balance, not by status

   Set the criterion Amount Remaining is not equal to 0. Status filters catch people out: a part-paid invoice is still Open, and a fully applied one flips to Paid In Full at slightly different moments than you expect. Amount remaining is the balance itself, so it can't drift from reality.

3. 03

   Include every document type that carries an AR balance

   Invoices, credit memos, and — if your team posts them — customer deposits and journals against the AR account. If you filter to Type = Invoice only, your total will be higher than the ledger by the value of every unapplied credit, which is the single most common reason an aging report and a customer statement disagree.

4. 04

   Keep it to one row per transaction

   Add the criterion Main Line is true. Without it, a 40-line invoice contributes 40 rows and your total is nonsense. This is the mistake that produces an aging report ten times larger than the balance sheet.

5. 05

   Add the bucket formula

   Add a Formula (Text) result column with a CASE expression over Days Overdue (below). Then add Amount Remaining as a Sum. Group by customer and bucket, or use the bucket as a summary column to get the classic grid.

6. 06

   Publish it to the people who work it

   Save it, give the collections role access, and add it as a dashboard view for them. An aging report that lives in someone's export folder gets worked once a month; one on a dashboard gets worked daily.

CASE
  WHEN {daysoverdue} <= 0  THEN 'Current'
  WHEN {daysoverdue} <= 30 THEN '1-30'
  WHEN {daysoverdue} <= 60 THEN '31-60'
  WHEN {daysoverdue} <= 90 THEN '61-90'
  ELSE '90+'
END

The SuiteQL version

If you're pulling aging into a spreadsheet, a warehouse or a script, SuiteQL is less fiddly than a saved search export because the bucket logic is visible in one place. Open balances live on the transaction record, so you don't need a line join at all:

SELECT
  BUILTIN.DF(t.entity)                AS customer,
  BUILTIN.DF(t.type)                  AS doc_type,
  t.tranid,
  t.trandate,
  t.duedate,
  BUILTIN.DF(t.currency)              AS currency,
  t.foreignamountunpaid               AS open_amount,
  TRUNC(SYSDATE) - t.duedate          AS days_overdue,
  CASE
    WHEN t.duedate IS NULL                     THEN 'No due date'
    WHEN TRUNC(SYSDATE) - t.duedate <= 0       THEN 'Current'
    WHEN TRUNC(SYSDATE) - t.duedate <= 30      THEN '1-30'
    WHEN TRUNC(SYSDATE) - t.duedate <= 60      THEN '31-60'
    WHEN TRUNC(SYSDATE) - t.duedate <= 90      THEN '61-90'
    ELSE '90+'
  END                                 AS bucket
FROM transaction t
WHERE t.type IN ('CustInvc', 'CustCred', 'CustDep')
  AND t.posting = 'T'
  AND t.foreignamountunpaid <> 0
ORDER BY days_overdue DESC

Two things to know about that query. foreignamountunpaid is the transaction-currency balance, so a multi-currency ledger needs the base-currency equivalent or a conversion step before you total it. And credit memos usually have no due date, which is why they get their own bucket rather than silently landing in Current. If the formatting of these queries in your notes has decayed into one long line, run it through the SuiteQL formatter before you paste it into a ticket. More recipes in the same shape are collected in SuiteQL transaction queries.

Tying aging back to the AR control account

When the aging total and the balance sheet disagree, work from the GL side. The accounting lines are the authority, and they carry the posting period the aging report ignores:

SELECT
  SUM(tal.debit) - SUM(tal.credit)    AS ar_control_balance
FROM transactionaccountingline tal
JOIN transaction t ON t.id = tal.transaction
JOIN account a     ON a.id = tal.account
WHERE a.accttype = 'AcctRec'
  AND tal.posting = 'T'
  AND t.trandate <= TO_DATE('2026-06-30', 'YYYY-MM-DD')

Run it both ways — filtered on transaction date, then on posting period. The difference is your date-basis gap, and it is usually a handful of documents dated at the very end of a month. Anything left after that is journals with no customer, or a subsidiary you forgot was in scope.

How to read the result once you have it

An aging report is not a scoreboard, it's a work queue. Three habits get more cash in than any change to the report itself.

• Work by value inside each bucket, not oldest-first. Sorting oldest-first spends your best collector's morning on a $340 freight dispute. Sort descending by amount within 31–60 and 61–90 and the same hour recovers six figures.
• Separate late from disputed. A disputed invoice doesn't age, it stops. If more than about 5% of overdue value is in dispute, the fix is upstream in billing accuracy, not in collections effort — see what DSO actually measures for the levers that follow.
• Set the reserve from bucket loss rates, not a flat percentage of sales. Bucket-weighted reserves survive audit questions better; the bad debt reserve calculator does the weighting, and AR aging buckets explained covers why the boundaries are where they are.
• Track the mix, not the total. AR growing with sales is fine. The 90+ share creeping from 3% to 7% while the total is flat is the signal, and it shows up a month before DSO does — the DSO calculator will tell you what that costs in cash.

The version where you just ask

Everything above is twenty minutes of work the first time and five minutes every time after, assuming nobody changed the terms, added a subsidiary or started posting journals to AR. This is the class of question ERPray answers directly: ask "show me AR aging by bucket for the UK subsidiary as at 30 June, excluding intercompany" and get the number computed live from your own account, with the SuiteQL it ran printed underneath so you can check the definition instead of trusting it. Read-only by default, so nothing in the ledger moves.