Creating an on-account purchase report

While it does not exist as a standard report, you can create a custom report that shows you what products your customers are buying on account. This report will show you all the items your customers are buying "on-account" ranked by dollar value.

1. In the Data Entry main window click Reporting and then select General Reports to open the Reporting window.
2. Click the Custom Reports tab.
3. Click the Add SQL button.
4. Enter a title and the following SQL query:

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