Receivables overview · Report library | HiScale Advanced Reports, native NetSuite reporting

Screenshot of the Receivables overview dashboard in HiScale Advanced Reports, filled with fictional sample data — Sample preview, fictional dataYour own figures appear after import.

About this dashboard

Four tiles give the receivables balance, the overdue part, the amount over 90 days and last month's DSO. Below them are receivables by aging bucket, the ten largest balances, DSO by month, cash collected by month, and the overdue invoices table.

The Customer filter applies to the balance, overdue and over-90 tiles, the aging and largest-balance charts, and the overdue invoices table. The aging chart reads Open receivables. The largest-balance chart reads AR aging by customer, whose buckets drill to the documents.

What it carries

The dashboard reads 5 reports. They travel in the same file, so the import creates them first.

Sample preview

Fictional figures in the shape you will see. Yours come from your account.

Widget	Kind	Reads
Receivables balance	stat	Open receivables
Overdue	stat	Open receivables
Over 90 days	stat	AR aging by customer
DSO, last month	stat	DSO trend
Receivables by aging bucket	bar	Open receivables
Largest balances	hbar	AR aging by customer
DSO by month	line	DSO trend
Cash collected by month	line	Customer payments by month

The SuiteQL

This is the query each report runs. Read it, copy it, or change it after import.

Open receivables

SELECT o.document__id,
       o.document,
       o.type,
       NVL(c.altname, c.entityid) AS customer,
       TO_CHAR(o.tran_date, 'YYYY-MM-DD') AS tran_date,
       TO_CHAR(o.due_date, 'YYYY-MM-DD') AS due_date,
       CASE WHEN o.open_amount < 0 THEN NULL ELSE TRUNC(SYSDATE) - TRUNC(o.due_date) END AS days_overdue,
       CASE WHEN o.open_amount < 0 THEN 'Unapplied credit'
            WHEN TRUNC(SYSDATE) - TRUNC(o.due_date) <= 0 THEN 'Current'
            WHEN TRUNC(SYSDATE) - TRUNC(o.due_date) <= 30 THEN '1-30'
            WHEN TRUNC(SYSDATE) - TRUNC(o.due_date) <= 60 THEN '31-60'
            WHEN TRUNC(SYSDATE) - TRUNC(o.due_date) <= 90 THEN '61-90'
            ELSE 'Over 90' END AS aging_bucket,
       o.currency,
       o.open_fx,
       o.open_amount
  FROM (SELECT e.toplevelparent AS customer__id,
               t.id AS document__id,
               t.tranid AS document,
               BUILTIN.DF(t.type) AS type,
               t.trandate AS tran_date,
               NVL(t.duedate, t.trandate) AS due_date,
               BUILTIN.DF(t.currency) AS currency,
               t.foreignamountunpaid AS open_fx,
               t.foreignamountunpaid * t.exchangerate AS open_amount
          FROM transaction t
          JOIN entity e ON e.id = t.entity
         WHERE t.type = 'CustInvc'
           AND t.posting = 'T'
           AND t.foreignamountunpaid > 0
        UNION ALL
        SELECT e.toplevelparent,
               t.id,
               t.tranid,
               BUILTIN.DF(t.type),
               t.trandate,
               t.trandate,
               BUILTIN.DF(t.currency),
               -t.foreignpaymentamountunused,
               -t.foreignpaymentamountunused * t.exchangerate
          FROM transaction t
          JOIN entity e ON e.id = t.entity
         WHERE t.type IN ('CustCred', 'CustPymt')
           AND t.foreignpaymentamountunused > 0) o
  JOIN entity c ON c.id = o.customer__id
 ORDER BY customer, o.due_date

AR aging by customer

SELECT o.customer__id,
       NVL(c.altname, c.entityid) AS customer,
       SUM(CASE WHEN o.open_amount >= 0 AND TRUNC(SYSDATE) - TRUNC(o.due_date) <= 0 THEN o.open_amount ELSE 0 END) AS current_due,
       SUM(CASE WHEN o.open_amount >= 0 AND TRUNC(SYSDATE) - TRUNC(o.due_date) BETWEEN 1 AND 30 THEN o.open_amount ELSE 0 END) AS days_1_30,
       SUM(CASE WHEN o.open_amount >= 0 AND TRUNC(SYSDATE) - TRUNC(o.due_date) BETWEEN 31 AND 60 THEN o.open_amount ELSE 0 END) AS days_31_60,
       SUM(CASE WHEN o.open_amount >= 0 AND TRUNC(SYSDATE) - TRUNC(o.due_date) BETWEEN 61 AND 90 THEN o.open_amount ELSE 0 END) AS days_61_90,
       SUM(CASE WHEN o.open_amount >= 0 AND TRUNC(SYSDATE) - TRUNC(o.due_date) > 90 THEN o.open_amount ELSE 0 END) AS over_90,
       SUM(CASE WHEN o.open_amount < 0 THEN o.open_amount ELSE 0 END) AS unapplied,
       SUM(o.open_amount) AS balance
  FROM (SELECT e.toplevelparent AS customer__id,
               t.id AS document__id,
               t.tranid AS document,
               BUILTIN.DF(t.type) AS type,
               t.trandate AS tran_date,
               NVL(t.duedate, t.trandate) AS due_date,
               BUILTIN.DF(t.currency) AS currency,
               t.foreignamountunpaid AS open_fx,
               t.foreignamountunpaid * t.exchangerate AS open_amount
          FROM transaction t
          JOIN entity e ON e.id = t.entity
         WHERE t.type = 'CustInvc'
           AND t.posting = 'T'
           AND t.foreignamountunpaid > 0
        UNION ALL
        SELECT e.toplevelparent,
               t.id,
               t.tranid,
               BUILTIN.DF(t.type),
               t.trandate,
               t.trandate,
               BUILTIN.DF(t.currency),
               -t.foreignpaymentamountunused,
               -t.foreignpaymentamountunused * t.exchangerate
          FROM transaction t
          JOIN entity e ON e.id = t.entity
         WHERE t.type IN ('CustCred', 'CustPymt')
           AND t.foreignpaymentamountunused > 0) o
  JOIN entity c ON c.id = o.customer__id
 GROUP BY o.customer__id, NVL(c.altname, c.entityid)
 ORDER BY balance DESC

DSO trend

SELECT m.period,
       m.ar_balance,
       m.revenue,
       CASE WHEN m.revenue > 0 THEN ROUND(m.ar_balance / m.revenue * m.days_in_month, 1) END AS dso
  FROM (SELECT g.period,
               SUM(g.ar_change) OVER (ORDER BY g.period) AS ar_balance,
               g.revenue,
               TO_NUMBER(TO_CHAR(LAST_DAY(TO_DATE(g.period || '-01', 'YYYY-MM-DD')), 'DD')) AS days_in_month
          FROM (SELECT TO_CHAR(t.trandate, 'YYYY-MM') AS period,
                       SUM(CASE WHEN a.accttype = 'AcctRec' THEN tal.amount ELSE 0 END) AS ar_change,
                       SUM(CASE WHEN a.accttype = 'Income' THEN -tal.amount ELSE 0 END) AS revenue
                  FROM transactionaccountingline tal
                  JOIN accountingbook ab ON ab.id = tal.accountingbook AND ab.isprimary = 'T'
                  JOIN transaction t ON t.id = tal.transaction
                  JOIN account a ON a.id = tal.account
                 WHERE tal.posting = 'T'
                   AND a.accttype IN ('AcctRec', 'Income')
                 GROUP BY TO_CHAR(t.trandate, 'YYYY-MM')) g) m
 WHERE m.period >= TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -11), 'YYYY-MM')
   AND m.period <= TO_CHAR(SYSDATE, 'YYYY-MM')
 ORDER BY m.period

Customer payments by month

SELECT TO_CHAR(t.trandate, 'YYYY-MM') AS period,
       COUNT(*) AS payments,
       COUNT(DISTINCT e.toplevelparent) AS customers,
       SUM(t.foreigntotal * t.exchangerate) AS amount
  FROM transaction t
  JOIN entity e ON e.id = t.entity
 WHERE t.type = 'CustPymt'
   AND t.trandate >= ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -23)
 GROUP BY TO_CHAR(t.trandate, 'YYYY-MM')
 ORDER BY period

Overdue invoices

SELECT t.id AS invoice__id,
       t.tranid AS invoice,
       NVL(e.altname, e.entityid) AS customer,
       TO_CHAR(t.trandate, 'YYYY-MM-DD') AS invoice_date,
       TO_CHAR(t.duedate, 'YYYY-MM-DD') AS due_date,
       TRUNC(SYSDATE) - TRUNC(t.duedate) AS days_overdue,
       BUILTIN.DF(t.terms) AS terms,
       BUILTIN.DF(t.currency) AS currency,
       t.foreignamountunpaid AS open_fx,
       t.foreignamountunpaid * t.exchangerate AS open_amount
  FROM transaction t
  JOIN entity e ON e.id = t.entity
 WHERE t.type = 'CustInvc'
   AND t.posting = 'T'
   AND t.foreignamountunpaid > 0
   AND t.duedate < TRUNC(SYSDATE)
 ORDER BY t.duedate