Edit Report:Supplier Monthly Intro Sales with Invoice Numbers
Menu Anchors
'customers', 'orders', 'sales', 'payables', 'warehouse', 'dispatching', 'receivables', 'payables-dash', 'inventory', 'purchases', 'generallendger', 'users',-- Supplier Monthly Intro Sales with Invoice Numbers
-- Enter Date Range. Enter Supplier Number. Pull invoices from accounts + include with Evo Monthly Sales Out Report.
-- ^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",
-- }
-- ROLLUP: {
-- columns: {
-- "Total Sales": "Total: {{sum}}"
-- }
-- }
SELECT
tranyear as 'Year',
tranmnth as'Month',
suppliername as 'Supplier',
debtorno as 'Debtor No',
branchcode as 'Branch',
brname as 'Salon Name',
transno as 'Invoice Number',
categorydescription as 'Category',
description as 'Description',
salesmanname as 'Sales Rep',
( SUM( discountedlinetotal )* - 1 ) AS 'Total Sales' ,
sum(qty*-1) as 'Qty Sold'
FROM
stocktrans
INNER JOIN productlines ON stocktrans.lineid = productlines.line_field
INNER JOIN stockcategory ON stocktrans.categoryid = stockcategory.categoryid
WHERE
1
AND trandate BETWEEN ( '{{ range.start }}' ) AND ( '{{ range.end }}' )
-- AND loccode ='00'
AND supplierno like '%{{ supplierno }}%'
AND stocktrans.categoryid IN ( 'IN','INC','99C', '99')
GROUP BY
orderno,
stocktrans.stockid
ORDER BY
description