Edit Report:Manufacturer Breakdown by Sales Rep, with salons
Menu Anchors
'customers', 'orders', 'sales', 'payables', 'warehouse', 'dispatching', 'receivables', 'payables-dash', 'inventory', 'purchases', 'generallendger', 'users',-- Manufacturer Breakdown by Sales Rep, with salons
-- All Locations.Choose sales rep
-- ^Monthly Reports
-- VARIABLE: {
-- name: "range",
-- display: "Report Range",
-- type: "daterange",
-- default: { start: "first day of last month", end: "yesterday" }
-- }
-- VARIABLE: {
-- name: "salesman",
-- display: "Salesman",
-- type: "select",
-- database_options: { table: "salesman", display: "salesmanname", column: "salesmancode", where: " current = 1 and commissionrate2 in (1,5) " , order:{ column: "salesmanname" , order: "ASC"} },
-- multiple:true,
-- }
-- VARIABLE: {
-- name: "supplierno",
-- display: "Supplier No",
-- type: "text",
-- }
-- ROLLUP: {
-- columns: {
-- "Total": "Total: {{sum}}"
-- }
-- }
-- FILTER: {
-- column: "DEBTORNO",
-- filter: "drilldown",
-- params: {
-- macros: { "debtorno": { column: "DEBTORNO" } },
-- report: "drilldown/customer-invoices.sql"
-- }
-- }
-- ignore
SELECT
suppliername AS 'Supplier Name',
salesmanname AS 'Salesman',
tranmnth AS 'Month',
d.debtorno AS 'debtorno',
s.brname AS 'salon name',
d.address4 AS 'prov',
( SUM( discountedlinetotal )* - 1 ) AS 'Total'
FROM
stocktrans s
inner join debtorsmaster d on s.debtorno = d.debtorno
WHERE
1
AND trandate BETWEEN '{{ range.start }}' AND '{{ range.end }}'
AND supplierno like '{{ supplierno }}%'
and d.typeid NOT IN ('6', '8','10','21')
{% if salesman|length > 0 %}
and salesperson in (
{% for person in salesman %}
'{{ person }}'
{{ not loop.last ? ',' }}
{% endfor %})
{% endif %}
GROUP BY
debtorno,
supplierno
ORDER BY
salesperson,
suppliername,
Total ASC