Edit Report:Sales By Customer for a Product Line (PCSLS)
Menu Anchors
'customers', 'orders', 'sales', 'payables', 'warehouse', 'dispatching', 'receivables', 'payables-dash', 'inventory', 'purchases', 'generallendger', 'users',-- Sales By Customer for a Product Line (PCSLS)
-- PLACE HOLDER REPORT DESCRIPTION
-- PLACE HOLDER REPORT FILTERS AND DRILL DOWN
-- ^orders
--
-- VARIABLE: {
-- name: "range",
-- display: "Report Range",
-- type: "daterange",
-- default: { start: "first day of this month", end: "last day of this month" }
-- }
-- VARIABLE: {
-- name: "lineid",
-- display: "Product Line ID",
-- type: "text",
-- }
-- ROLLUP: {
-- columns: {
-- "TOTAL": "Total: {{sum}}",
-- "QTY": "Total: {{sum}}"
-- }
-- }
-- CHART: {
-- "columns": ["CUSTOMER","TOTAL"],
-- "type": "PieChart",
-- "title": "Customer Sales For Product Line ",
-- }
SELECT
p.line_description as `PRODUCTLINE`,
s.lineid as PRDLNID,
s.debtorno AS CUSTOMERNO,
name AS CUSTOMER,
CAST(sum( discountedlinetotal * - 1 ) as DECIMAL(10,2)) AS TOTAL,
CAST(sum( qty * - 1 ) as DECIMAL(10,0)) AS QTY
FROM
stocktrans s
Left Join debtorsmaster d ON s.debtorno = d.debtorno
Left Join productlines p ON p.line_field = s.lineid
where trandate between "{{ range.start }}" AND "{{ range.end }}"
and s.lineid like '{{ lineid }}%'
group by s.debtorno, s.lineid
having TOTAL > 0
order by TOTAL desc, CUSTOMER asc