Edit Report:MISC Item Sales by date range
Menu Anchors
'customers', 'orders', 'sales', 'payables', 'warehouse', 'dispatching', 'receivables', 'payables-dash', 'inventory', 'purchases', 'generallendger', 'users',-- MISC Item Sales by date range
-- PLACE HOLDER REPORT DESCRIPTION
-- PLACE HOLDER REPORT FILTERS AND DRILL DOWN
-- VARIABLE: {
-- name: "range",
-- display: "Report Range",
-- type: "daterange",
-- default: { start: "yesterday", end: "yesterday" }
-- }
-- ROLLUP: {
-- columns: {
-- "Sales Amt": "{{ sum }}",
-- "Cost": "{{ sum }}",
-- "Qty Sld": "{{ sum }}",
-- "Profit": "{{ sum }}",
-- "stockid": "{{ count }}"
-- }
--}
select
s.loccode as `Location`,
s.debtorno as `CustNo`,
s.branchcode as Branch,
s.trandate,
s.stockid,
sm.description,
CAST(sum(s.qty*-1) as DECIMAL(10,2)) as `Qty Sld`,
CAST(sum(s.discountedlinetotal*-1) as DECIMAL(10,2)) as `Sales Amt`,
CAST(sum(s.costtotal*-1) as DECIMAL(10,2)) as `Cost`,
CAST(sum( (s.discountedlinetotal-s.costtotal) * - 1 ) as DECIMAL(10,2)) AS Profit,
CAST(((sum(s.discountedlinetotal-s.costtotal)/sum( s.discountedlinetotal)) * 100 ) as DECIMAL(10,2))AS `Profit%`
from stocktrans s
left join stockmaster sm on s.stockid = sm.stockid
where trandate between "{{ range.start }}" AND "{{ range.end }}"
and s.stockid like "%MISC%"
group by s.loccode, s.debtorno, s.branchcode, s.stockid
order by s.loccode, s.debtorno, s.branchcode, s.stockid