Edit Report:0$ test
Menu Anchors
'customers', 'orders', 'sales', 'payables', 'warehouse', 'dispatching', 'receivables', 'payables-dash', 'inventory', 'purchases', 'generallendger', 'users',-- 0$ test
-- Credits and invoices with drilldowns that have 0$ totals
-- ^receivables
-- VARIABLE: {
-- name: "range",
-- display: "Report Range",
-- type: "daterange",
-- default: { start: "first day of last month", end: "last day of last month" }
-- }
-- ROLLUP: {
-- columns: {
-- "SalesAmt": "{{ sum }}",
-- }
--}
-- FILTER: {
-- filter: "drilldown",
-- column: "Order",
-- params: {
-- report: "drilldown/order-details.sql",
-- macros: {
-- "orderno": {
-- "column": "Order"
-- }
-- }
-- }
--}
select * from(select
dt.debtorno as `CustNo`,
d.name as `Cust Name`,
dt.trandate as`Date`,
dt.type as `Type`,
dt.order_ as `Order`,
dt.transno as `Invoice`,
sum((dt.ovamount+dt.ovgst+dt.ovfreight+dt.ovfee+dt.ovdiscount)*-1) as `SalesAmt`
from debtortrans dt
left join debtorsmaster d on dt.debtorno = d.debtorno
where dt.trandate between "{{ range.start }}" AND "{{ range.end }}"
and dt.type IN(10,11)
group by dt.transno
order by dt.debtorno, dt.order_, dt.transno) A
where A.SalesAmt = 0