Edit Report:Sales Value Report
Menu Anchors
'customers', 'orders', 'sales', 'payables', 'warehouse', 'dispatching', 'receivables', 'payables-dash', 'inventory', 'purchases', 'generallendger', 'users',-- Sales Value Report
-- supplier sales for all locations
-- ^Monthly Reports
-- VARIABLE: {
-- name: "range",
-- display: "Report Range",
-- type: "daterange",
-- default: { start: "first day of last month", end: "yesterday" }
-- }
-- VARIABLE: {
-- name: "suppliername",
-- display: "Supplier",
-- type: "text",
-- }
-- ROLLUP: {
-- columns: {
-- "alltotal": "Total: {{sum}}",
-- "warehouse total": "Total: {{sum}}",
-- "calgary south total": "Total: {{sum}}",
-- "edmonton total": "Total: {{sum}}",
-- "regina total": "Total: {{sum}}",
-- "saskatoon total": "Total: {{sum}}",
-- "winnipeg total": "Total: {{sum}}",
-- "boutique total": "Total: {{sum}}",
-- "calgary ne total": "Total: {{sum}}",
-- "red deer total": "Total: {{sum}}",
-- "brandon total": "Total: {{sum}}",
-- "edmonton east total": "Total: {{sum}}",
-- "lethbridge total": "Total: {{sum}}",
-- "ecomtotal": "Total: {{sum}}",
-- }
-- }
SELECT
suppliername,
CAST(
SUM(
CASE WHEN loccode IN (
'00', '01', '02', '03', '04', '05', '06',
'07', '08', '09', '10', '11'
) THEN (discountedlinetotal * -1) ELSE 0 END
) AS DECIMAL (10, 2)
) AS 'alltotal',
CAST(
SUM(
CASE WHEN loccode = '00' THEN (discountedlinetotal * -1) ELSE 0 END
) AS DECIMAL (10, 2)
) AS 'warehouse total',
CAST(
SUM(
CASE WHEN loccode = '01' THEN (discountedlinetotal * -1) ELSE 0 END
) AS DECIMAL (10, 2)
) AS ' calgary south total',
CAST(
SUM(
CASE WHEN loccode = '02' THEN (discountedlinetotal * -1) ELSE 0 END
) AS DECIMAL (10, 2)
) AS 'edmonton total',
CAST(
SUM(
CASE WHEN loccode = '03' THEN (discountedlinetotal * -1) ELSE 0 END
) AS DECIMAL (10, 2)
) AS 'regina total',
CAST(
SUM(
CASE WHEN loccode = '04' THEN (discountedlinetotal * -1) ELSE 0 END
) AS DECIMAL (10, 2)
) AS 'saskatoon total',
CAST(
SUM(
CASE WHEN loccode = '05' THEN (discountedlinetotal * -1) ELSE 0 END
) AS DECIMAL (10, 2)
) AS 'winnipeg total',
CAST(
SUM(
CASE WHEN loccode = '06' THEN (discountedlinetotal * -1) ELSE 0 END
) AS DECIMAL (10, 2)
) AS 'boutique total',
CAST(
SUM(
CASE WHEN loccode = '07' THEN (discountedlinetotal * -1) ELSE 0 END
) AS DECIMAL (10, 2)
) AS 'calgary ne total',
CAST(
SUM(
CASE WHEN loccode = '08' THEN (discountedlinetotal * -1) ELSE 0 END
) AS DECIMAL (10, 2)
) AS 'red deer total',
CAST(
SUM(
CASE WHEN loccode = '09' THEN (discountedlinetotal * -1) ELSE 0 END
) AS DECIMAL (10, 2)
) AS 'brandon total',
CAST(
SUM(
CASE WHEN loccode = '10' THEN (discountedlinetotal * -1) ELSE 0 END
) AS DECIMAL (10, 2)
) AS 'edmonton east total',
CAST(
SUM(
CASE WHEN loccode = '11' THEN (discountedlinetotal * -1) ELSE 0 END
) AS DECIMAL (10, 2)
) AS 'lethbridge total',
CAST(
SUM(
CASE WHEN placedfrom = '04' THEN (discountedlinetotal * -1) ELSE 0 END
) AS DECIMAL (10, 2)
) AS 'ecomtotal'
FROM
stocktrans s
inner join debtorsmaster d on s.debtorno = d.debtorno
WHERE
trandate BETWEEN '{{ range.start }}' AND '{{ range.end }}'
and d.typeid NOT IN ('6', '8','10','21')
and loccode IN (
'00', '01', '02', '03', '04', '05', '06',
'07', '08', '09', '10','11'
)
GROUP BY
suppliername