Edit Report:spa sales for erin
Menu Anchors
'customers', 'orders', 'sales', 'payables', 'warehouse', 'dispatching', 'receivables', 'payables-dash', 'inventory', 'purchases', 'generallendger', 'users',-- spa sales for erin
-- Enter supplier code.
-- ^Monthly Reports
-- VARIABLE: {
-- name: "range",
-- display: "Report Range",
-- type: "daterange",
-- default: { start: "first day of last month", end: "yesterday" }
-- }
-- VARIABLE: {
-- name: "groupby",
-- display: "Group By",
-- type: "select",
-- multiple: false,
-- options: [{display: 'fromstkloc', value:'fromstkloc'},{display: 'Salesperson', value:'salesperson'}],
-- }
-- ROLLUP: {
-- columns: {
-- "TOTALSALES": "Totalsales: {{sum}}"
-- }
-- }
SELECT
supplierno,
suppliername,
fromstkloc,
salesperson,
salesmanname,
IF
( 'fromstkloc' = 'salesperson', salesmanname, locations.locationname ) AS 'fromstkloc',
cast( sum( discountedlinetotal * - 1 ) AS DECIMAL ( 10, 2 ) ) AS TOTALSALES
FROM
stocktrans s
INNER JOIN locations ON locations.loccode = fromstkloc
inner join debtorsmaster d on s.debtorno = d.debtorno
WHERE
trandate BETWEEN '{{ range.start }}' AND '{{ range.end }}'
and supplierno IN ('QE','RO','AB','KV','AI','CB','RL','SR','BR','AR','MB','MC','YB','LL','US','EA','BB')
and d.typeid NOT IN ('6', '8','10','21')
{% if groupby == 'fromstkloc' %}
GROUP BY fromstkloc, supplierno
{% endif %}
{% if groupby == 'salesperson' %}
GROUP BY salesperson, supplierno
{% endif %}
HAVING TOTALSALES > 0
{% if groupby == 'fromstkloc' %}
ORDER BY fromstkloc
{% endif %}
{% if groupby == 'salesperson' %}
ORDER BY salesperson
{% endif %}