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

Screenshot of the Sales 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 net sales this year and this month, orders entered this month and the value of the open backlog. Below them are net sales by month, the ten largest customers over the last twelve months, net sales by sales rep this year, and the open sales orders table.

The Customer filter applies to both net sales tiles, the backlog tile, the trend, the top-customer chart and the open orders table. Click a report name in a widget title to open the report behind it. The trend's report drills to the sales transaction lines.

What it carries

The dashboard reads 6 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
Net sales, this year	stat	Sales by customer by month
Net sales, this month	stat	Sales by customer by month
Orders entered, this month	stat	Sales orders by month
Open order backlog	stat	Open sales orders backlog
Net sales by month	line	Sales by customer by month
Top 10 customers, last 12 months	hbar	Top customers by revenue (12 months)
Net sales by sales rep, this year	bar	Sales by sales rep by month
Open sales orders	table	Open sales orders backlog

The SuiteQL

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

Sales by customer by month

SELECT TO_CHAR(t.trandate, 'YYYY-MM') AS period,
       e.toplevelparent AS customer__id,
       NVL(c.altname, c.entityid) AS customer,
       COUNT(DISTINCT t.id) AS transactions,
       SUM(-tl.foreignamount * t.exchangerate) AS sales
  FROM transaction t
  JOIN transactionline tl ON tl.transaction = t.id
  JOIN entity e ON e.id = t.entity
  JOIN entity c ON c.id = e.toplevelparent
 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 >= ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -23)
 GROUP BY TO_CHAR(t.trandate, 'YYYY-MM'), e.toplevelparent, NVL(c.altname, c.entityid)
 ORDER BY period DESC, sales DESC

Sales transaction lines

SELECT TO_CHAR(t.trandate, 'YYYY-MM') AS period,
       TO_CHAR(t.trandate, 'YYYY-MM-DD') AS tran_date,
       BUILTIN.DF(t.type) AS type,
       t.tranid AS document,
       NVL(c.altname, c.entityid) AS customer,
       NVL(e.altname, e.entityid) AS billed_to,
       BUILTIN.DF(tl.item) AS item,
       tl.memo AS memo,
       -tl.quantity AS quantity,
       -tl.foreignamount * t.exchangerate AS sales,
       BUILTIN.DF(t.employee) AS sales_rep
  FROM transaction t
  JOIN transactionline tl ON tl.transaction = t.id
  JOIN entity e ON e.id = t.entity
  JOIN entity c ON c.id = e.toplevelparent
 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 >= ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -23)
 ORDER BY t.trandate DESC, t.tranid

Sales orders by month

SELECT TO_CHAR(t.trandate, 'YYYY-MM') AS period,
       COUNT(DISTINCT t.id) AS orders,
       SUM(-tl.foreignamount * t.exchangerate) AS amount,
       SUM(-tl.foreignamount * t.exchangerate) / COUNT(DISTINCT t.id) AS avg_order
  FROM transaction t
  JOIN transactionline tl ON tl.transaction = t.id
 WHERE t.type = 'SalesOrd'
   AND t.status NOT IN ('SalesOrd:C', 'C')
   AND tl.mainline = 'F'
   AND tl.taxline = 'F'
   AND tl.itemtype NOT IN ('TaxGroup', 'TaxItem', 'Subtotal', 'Description', 'EndGroup')
   AND t.trandate >= ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -23)
 GROUP BY TO_CHAR(t.trandate, 'YYYY-MM')
 ORDER BY period

Open sales orders backlog

SELECT t.id AS order__id,
       t.tranid AS order_no,
       TO_CHAR(t.trandate, 'YYYY-MM-DD') AS order_date,
       NVL(e.altname, e.entityid) AS customer,
       BUILTIN.DF(t.status) AS status,
       TO_CHAR(t.shipdate, 'YYYY-MM-DD') AS ship_date,
       TRUNC(SYSDATE) - TRUNC(t.trandate) AS days_open,
       BUILTIN.DF(t.currency) AS currency,
       t.foreigntotal AS total_fx,
       t.foreigntotal * t.exchangerate AS total
  FROM transaction t
  JOIN entity e ON e.id = t.entity
 WHERE t.type = 'SalesOrd'
   AND t.status IN ('SalesOrd:B', 'SalesOrd:D', 'SalesOrd:E', 'SalesOrd:F', 'B', 'D', 'E', 'F')
 ORDER BY t.trandate

Top customers by revenue (12 months)

SELECT x.customer__id,
       NVL(c.altname, c.entityid) AS customer,
       x.transactions,
       x.sales,
       x.sales / NULLIF(SUM(x.sales) OVER (), 0) AS share,
       TO_CHAR(x.last_sale, 'YYYY-MM-DD') AS last_sale
  FROM (SELECT e.toplevelparent AS customer__id,
               COUNT(DISTINCT t.id) AS transactions,
               SUM(-tl.foreignamount * t.exchangerate) AS sales,
               MAX(t.trandate) AS last_sale
          FROM transaction t
          JOIN transactionline tl ON tl.transaction = t.id
          JOIN entity e ON e.id = t.entity
         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 e.toplevelparent) x
  JOIN entity c ON c.id = x.customer__id
 ORDER BY x.sales DESC

Sales by sales rep by month

SELECT TO_CHAR(t.trandate, 'YYYY-MM') AS period,
       NVL(BUILTIN.DF(t.employee), '(no sales rep)') AS sales_rep,
       COUNT(DISTINCT t.id) AS transactions,
       SUM(-tl.foreignamount * t.exchangerate) AS sales
  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 >= ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -23)
 GROUP BY TO_CHAR(t.trandate, 'YYYY-MM'), NVL(BUILTIN.DF(t.employee), '(no sales rep)')
 ORDER BY period DESC, sales DESC