Edit Report:TOPS
Menu Anchors
'customers', 'orders', 'sales', 'payables', 'warehouse', 'dispatching', 'receivables', 'payables-dash', 'inventory', 'purchases', 'generallendger', 'users',-- TOPS
-- Top 20 customers by productline
-- VARIABLE: {
-- name: "range",
-- display: "Date Range",
-- type: "daterange",
-- default: { start: "first day of last month", end: "last day of last month" }
-- }
-- FILTER: {
-- filter: "class",
-- column: "Sales",
-- params: {
-- class: "right"
-- }
-- }
-- FILTER: {
-- filter: "class",
-- column: "Quantity",
-- params: {
-- class: "center"
-- }
-- }
-- VARIABLE: {
-- name: "pl",
-- display: "Product Line",
-- type: "select",
-- database_options: { table: "productlines", display: "line_description", column: "line_field", order:{ column: "line_description" , order: "ASC"} },
-- multiple:false,
-- empty:false,
-- }
-- FILTER: {
-- column: "DEBTORNO",
-- filter: "drilldown",
-- params: {
-- macros: { "DEBTORNO": { column: "DEBTORNO" }, "salesperson": { column: "SALESCODE" }, "range.start": { column: "TRANDATE" },"range.end": { column: "TRANDATE" } },
-- report: "samples/230714115215.sql"
-- }
-- }
SELECT
(select line_description from productlines where line_field = sm.lineid) as `Product Line`,
s.buyername as Customer,
s.debtorno as `Customer #`,
sum(quantity) as Quantity,
cast(sum(quantity * unitprice) as decimal (10,2)) as Sales from salesorderdetails sd
left join salesorders s on s.orderno = sd.orderno
left join stockmaster sm on sd.stkcode = sm.stockid
where sm.lineid = '{{pl}}' and s.orddate between '{{range.start}}' AND '{{range.end}}'
group by s.debtorno
order by Sales DESC
limit 20;