Edit Report:Sales Reps Monthly Sales: Stores (2)
Menu Anchors
'customers', 'orders', 'sales', 'payables', 'warehouse', 'dispatching', 'receivables', 'payables-dash', 'inventory', 'purchases', 'generallendger', 'users',-- Sales Reps Monthly Sales: Stores (2)
-- Select Date Range.
-- ^Monthly Reports
-- VARIABLE: {
-- name: "range",
-- display: "Report Range",
-- type: "daterange",
-- default: { start: "first day of last month", end: "yesterday" }
-- }
-- ROLLUP: {
-- columns: {
-- "Store Sales": "Total: {{sum}}",
-- "DSC Store Sales": "Total: {{sum}}",
-- "Store Total Sales": "Total: {{sum}}",
-- "Store Redken Sales": "Total: {{sum}}",
-- "Store DSC Redken Sales": "Total: {{sum}}",
-- "Store Redken Total Sales": "Total: {{sum}}",
-- }
-- }
--
SELECT
loccode as 'Location',
CAST(
SUM( CASE WHEN loccode > '00' AND salesperson NOT IN ( SELECT salesmancode FROM salesman WHERE commissionrate1 = 1 ) THEN ( discountedlinetotal * - 1 ) ELSE 0 END ) AS DECIMAL ( 10, 2 )) AS 'Store Sales',
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 Sales',
CAST(
SUM( CASE WHEN loccode > '00' THEN ( discountedlinetotal * - 1 ) ELSE 0 END ) AS DECIMAL ( 10, 2 )) AS 'Store Total Sales',
CAST(
SUM( CASE WHEN loccode > '00' AND supplierno = 'RK' AND salesperson NOT IN ( SELECT salesmancode FROM salesman WHERE commissionrate1 = 1 ) THEN ( discountedlinetotal * - 1 ) ELSE 0 END ) AS DECIMAL ( 10, 2 )) AS 'Store Redken Sales',
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 'Store DSC Redken Sales',
CAST(
SUM( CASE WHEN loccode > '00' AND supplierno = 'RK' THEN ( discountedlinetotal * - 1 ) ELSE 0 END ) AS DECIMAL ( 10, 2 )) AS 'Store Redken Total Sales'
FROM
stocktrans
WHERE
trandate BETWEEN '{{ range.start }}' AND '{{ range.end }}'
and loccode IN ('01', '02', '03', '04', '05', '06', '07', '08')
group by
loccode
order by
loccode