01
About this portlet
A comparison chart built on Sales by customer by month. It plots this year's net sales month by month against last year's, with the totals to date for both. Months still ahead are left blank.
The report it reads drills to Sales transaction lines.
After import, give the report an audience. Then, on a NetSuite dashboard, choose Personalize and add a Custom Portlet. On the new portlet, choose Set Up and pick Advanced Report - Net sales, this year against last year. The portlet shows only to people who can open the report.
02
What it carries
The portlet reads 2 reports. They travel in the same file, so the import creates them first.
03
Sample preview
Fictional figures in the shape you will see. Yours come from your account.
| Month | This year | Last year |
|---|---|---|
| Jun | 588,200 | 512,400 |
| Jul | 552,900 | 530,100 |
| Aug | 600,750 | 541,800 |
| Sep | 571,300 | 498,250 |
04
The SuiteQL
This is the query each report runs. Read it, copy it, or change it after import.
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 DESCSELECT 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