Edit Report:Maria Nila Report - Sandra
Menu Anchors
'customers', 'orders', 'sales', 'payables', 'warehouse', 'dispatching', 'receivables', 'payables-dash', 'inventory', 'purchases', 'generallendger', 'users',-- Maria Nila Report - Sandra
-- Select date range. Enter MN in 'supplierno' field to pull Maria Nila Sales.
-- ^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
s.stockid,
sm.description,
SUM(qty * -1) AS qty,
CAST(s.price AS DECIMAL(10, 2)) AS price,
CAST(SUM(s.price * (qty * -1)) AS DECIMAL(10, 2)) AS prediscounttotal,
CAST(discountpercent * 100 AS DECIMAL(5, 2)) AS discount,
CAST(purchcost AS DECIMAL(10, 2)) AS purchcost
FROM
stocktrans s
INNER JOIN stockmaster sm ON s.stockid = sm.stockid
WHERE
trandate BETWEEN '{{ range.start }}' AND '{{ range.end }}'
and supplierno like '{{ supplierno }}%'
GROUP BY
s.stockid, s.price
ORDER BY
s.stockid ASC