Edit Report:Supplier Breakdown By Sales Rep
Menu Anchors
'customers', 'orders', 'sales', 'payables', 'warehouse', 'dispatching', 'receivables', 'payables-dash', 'inventory', 'purchases', 'generallendger', 'users',-- Supplier Breakdown By Sales Rep
-- Select Date Range. Enter Supplier Code.
-- ^Monthly Reports
-- VARIABLE: {
-- name: "range",
-- display: "Report Range",
-- type: "daterange",
-- default: { start: "first day of last month", end: "yesterday" }
-- }
-- VARIABLE: {
-- name: "supplierno",
-- display: "Supplier Code",
-- type: "text",
-- }
-- VARIABLE: {
-- name: "salesperson",
-- display: "Sales Rep No",
-- type: "text",
-- }
SELECT
loccode AS 'Location',
salesperson as 'Sales Rep No',
salesmanname AS 'Sales Rep',
supplierno as 'Supplier',
categorydescription AS 'Category/Product Line',
sum( discountedlinetotal * - 1 ) AS 'Total Sales'
FROM
stocktrans
INNER JOIN stockcategory ON stocktrans.categoryid = stockcategory.categoryid
WHERE
trandate BETWEEN '{{ range.start }}' AND '{{ range.end }}'
AND supplierno like '{{ supplierno }}%'
GROUP BY
stocktrans.categoryid,
salesmanname
UNION
SELECT
loccode AS 'Location',
salesperson as 'Sales Rep No',
salesmanname AS 'Sales Rep',
supplierno as 'Supplier',
line_description AS 'Category/Product Line',
sum( discountedlinetotal * - 1 ) AS 'Total Sales'
FROM
stocktrans
INNER JOIN productlines ON stocktrans.lineid = productlines.line_field
WHERE
trandate BETWEEN '{{ range.start }}' AND '{{ range.end }}'
AND supplierno like '{{ supplierno }}%'
GROUP BY
stocktrans.lineid,
salesmanname