Edit Report:Sales Reps Monthly Sales (1)
Menu Anchors
'customers', 'orders', 'sales', 'payables', 'warehouse', 'dispatching', 'receivables', 'payables-dash', 'inventory', 'purchases', 'generallendger', 'users',-- Sales Reps Monthly Sales (1)
-- Select Date Range.
-- ^Monthly Reports
-- VARIABLE: {
-- name: "range",
-- display: "Report Range",
-- type: "daterange",
-- default: { start: "first day of last month", end: "yesterday" }
-- }
-- ROLLUP: {
-- columns: {
-- "DSC Warehouse Total": "Total: {{sum}}",
-- "DSC Store Total": "Total: {{sum}}",
-- "DSC Total Sales": "Total: {{sum}}",
-- "DSC Redken Sales Warehouse Only": "Total: {{sum}}",
-- "DSC Redken Sales Stores Only": "Total: {{sum}}",
-- "DSC Redken Sales Total": "Total: {{sum}}",
-- }
-- }
-- FILTER: {
-- filter: "twig",
-- column: "DSC Warehouse Total",
-- params: {
-- template: '{{ value | number_format(2, '.', ',') }}'
-- }
-- }
--
SELECT
salesperson,
salesmanname as 'Sales Rep',
CAST(
SUM( CASE WHEN loccode = '00' THEN ( discountedlinetotal * - 1 ) ELSE 0 END ) AS DECIMAL ( 10, 2 )) AS 'DSC Warehouse Total',
CAST(
SUM( CASE WHEN loccode <> '00' AND salesperson IN ( SELECT salesmancode FROM salesman WHERE commissionrate1 = 1 ) THEN ( discountedlinetotal * - 1 ) ELSE 0 END ) AS DECIMAL ( 10, 2 )) AS 'DSC Store Total',
CAST(
SUM( CASE WHEN loccode >= '00' THEN ( discountedlinetotal * - 1 ) ELSE 0 END ) AS DECIMAL ( 10, 2 )) AS 'DSC Total Sales',
CAST(
SUM( CASE WHEN loccode = '00' AND supplierno = 'RK' THEN ( discountedlinetotal * - 1 ) ELSE 0 END ) AS DECIMAL ( 10, 2 )) AS 'DSC Redken Sales Warehouse Only',
CAST(
SUM( CASE WHEN loccode <> '00' AND supplierno = 'RK' AND salesperson IN ( SELECT salesmancode FROM salesman WHERE commissionrate1 = 1 )THEN ( discountedlinetotal * - 1 ) ELSE 0 END ) AS DECIMAL ( 10, 2 )) AS 'DSC Redken Sales Stores Only',
CAST(
SUM( CASE WHEN loccode >= '00' AND supplierno = 'RK' THEN ( discountedlinetotal * - 1 ) ELSE 0 END ) AS DECIMAL ( 10, 2 )) AS 'DSC Redken Sales Total'
FROM
stocktrans
WHERE
trandate BETWEEN '{{ range.start }}' AND '{{ range.end }}'
AND salesperson IN ('00','01','02','03','04','05','06','07','08','09','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24','25','26',
'27','28','29','31','32','33','34','35','36','37','38','88','91','99')
group by
salesperson
order by
salesperson asc