I need to create a report that displays Sales by Customer (i.e. Sales Invoices less Credit Notes), by month.
Ideally, my report would have the Customers listed down the left side, and then 13 columns, listed Jan, Feb, … Dec and Total-Year.
Here is the question - in which tables do I find this data? (Or perhaps, is there already a basic JetReport that someone may have put together, that I can borrow from and expand?)
Samuel
Date
Votes
1 comment
-
Jet Reports Historic Posts I think there are many techniques for getting this information.
Developing this yourself from scratch would be a great learning exercise.
Here is how I would start. I'd be interested to see what ideas others have.
1) Start by getting a list of Customers. Use something like =nl("Rows","Customer",) to get started. Add some NFs to being in their No. and Name
2) Then, Write a formula to bring in total sales for a year. We use the Sales Invoice Header/Line tables, but depending on your system usage thiis might not work for you as sometimes that data can be deleted. i.e. =nl("Sum","Sales Invoice Header","Amount","Posting Date","010108..123108","Sell-to Customer No,",C3)
3) Once you've got that, do the same for credits in the next cell
4) Once that is working, combine the forumlas together into a single sell (Subract Credts from Invoices)
5) The move the date reference to above the formula, and then use absolute cell references
6) There are two ways to go from here. I'll explain the simpler technically method although it requires more maintenance work.
7) Put the required dates across the sheet, replacing the date in (5) with January, moving through the other twelve months
8) Copy your invoices/credits formula across for each month - but be careful to check the references to the No. for Customer and Month above.
I myself would use formulas in place of (7) that use nl("Columns") to automatically go across the sheet, calculating the dates as the Jet compiles.
If you can master that, you'll have learned many Jet techniques in a short space of time.
Good luck!
Please sign in to leave a comment.