Edit Report:samples/221024103101.sql
Menu Anchors
'customers', 'orders', 'sales', 'payables', 'warehouse', 'dispatching', 'receivables', 'payables-dash', 'inventory', 'purchases', 'generallendger', 'users',-- Customer Sales for a Product Line
-- PLACE HOLDER REPORT DESCRIPTION
-- PLACE HOLDER REPORT FILTERS AND DRILL DOWN
-- VARIABLE: {
-- name: "range",
-- display: "Report Range",
-- type: "daterange",
-- default: { start: "first day of this month", end: "last day of this month" }
-- }
-- VARIABLE: {
-- name: "brand",
-- display: "Brand",
-- type: "text",
-- }
-- VARIABLE: {
-- name: "salesman",
-- display: "Salesman",
-- type: "select",
-- database_options: { table: "salesman", display: "salesmanname", column: "salesmancode", where: " current = 1 " , order:{ column: "salesmanname" , order: "ASC"} },
-- multiple:true,
-- }
-- ROLLUP: {
-- columns: {
-- "TOTAL": "Total: {{sum}}"
-- }
SELECT
s.debtorno AS CUSTOMERNO,
name AS CUSTOMER,
brand as BRAND,
CAST(sum( discountedlinetotal * - 1 ) as DECIMAL(10,2)) AS TOTAL
FROM
stocktrans s
Left Join debtorsmaster d ON s.debtorno = d.debtorno
where trandate between "{{ range.start }}" AND "{{ range.end }}"
and brand like '{{ brand }}%'
{% if salesman|length > 0 %}
and salesperson in (
{% for person in salesman %}
'{{ person }}'
{{ not loop.last ? ',' }}
{% endfor %})
{% endif %}
group by s.debtorno, brand
order by TOTAL desc, CUSTOMER asc