Free Custom Report - "What are people buying on-account?"

You can add this report to your custom report list by going to Data Entry -> View -> General Reporting -> Custom Reports and then click ADD and paste in this SQL.

This report will show you all the items your customers are buying "on-account" ranked by dollar value.

--------- SQL BELOW - do not include this line -------------

SELECT
pr.DESCRIPTION,
sum(ii.QUANTITY) AS Qty,
cast(
sum(ii.PRICE) AS DECIMAL (10, 2)
) TotalSales
FROM
invoices i
LEFT OUTER JOIN payments p ON i.payment_id = p.payment_id
JOIN customers c ON i.chargeto_customer_id = c.customer_id
JOIN invoice_items ii ON i.invoice_id = ii.invoice_id
JOIN products pr ON ii.product_id = pr.product_id
WHERE
i.invtype = 'POS'
AND i.postdate >= '{STARTDATE}'
AND i.postdate <= '{ENDDATE}'
AND ii.IS_TAX_ITEM = 0
AND (
i.payment_id <= 0
OR (
(
unix_timestamp(p.postdate) - unix_timestamp(i.postdate)
) > 9
)
)
AND voidedinvoice = 0
GROUP BY
pr.product_id
HAVING
qty > 0
AND totalsales >= 0
ORDER BY
totalsales DESC