As I produce my annual financial statements, I am trying to use the Custom Reports feature to produce a couple of reports I’ve had to do every year by hand: Sales by Customer, and Receipts by Customer. Each is an itemized list of all sales (or receipts) grouped by customer. The first one is similar to the relatively new Sales Invoice Totals by Customer, except that it lists each invoice as a separate line instead of just totals by customer, and because it includes a column for end-of-year Receivables against each invoice because I’m on cash-basis accounting so it matters whether invoices are paid or not.
Here are mock-ups of what my manually produced reports (i.e. export data to Excel and do a whole lot of filtering, rearranging, and formatting) look like:
Is there any way I can come close to this using Custom Reports? I’ve tried, but without JOINs and other SQL magic, I can’t find a way to accomplish this.
I don’t think you will quite get to where you want, because the records in General Ledger Transactions are at the line item level, while the invoice or receipt information basically contains “header” information, without the financials. I think the best you will be able to do is explore the Sales Invoices listing and the Receipts & Payments listing and play with the information in a spreadsheet. Then export the Aged Receivables report and add relevant information.
