Monthly sales report - How to show empty months?
In FMPA 11, I have 3 tables: customers, invoices, line invoices.
I've managed to make a layout with monthly total invoices as follow:
customer 1 - total invoice jan2012
customer 2 - total invoice jan2012
customer 1 - total invoice feb2012
..and so on
This is helpful but limited. I need the report to show also the customers with zero invoices (who did not buy every month).
As in the previous example, it should appear like this:
customer 1 - total invoice jan2012 (eg. 100$)
customer 2 - total invoice jan2012 (eg. 150$)
customer 1 - total invoice feb2012 (eg.120$)
customer 2 - total invoice feb2012 (eg, 0$)
customer 1 - total invoice mar2012 (eg.0$)
customer 2 - total invoice mar2012 (eg, 0$)
As far as I've got, I've made a new table with all the months (last 2 years, 22 records - autocreate new record (month) every month
DATE=(current month ; "01" ; current year)).
In this table there is also a global field related to Customer ID - The DATE field is related to Date Printed from Invoices.
In Customer layout there is a script trigger "on record load" to set the customer ID inside the global field in Months Table.
With this system I can place a portal in the customer page (from Months) and list all months in one field and in the second field the Total invoices from a self join relation with invoice. (See picture).
If the customer doesn't have any invoice for one of the months, the total will show nothing.
How can I list all the months in a layout with all the customers and all the months? Showing me also who did not buy every month.
I've been scratching my head for a week trying to find this solution..
Any suggestion is more than wellcome!
(I use FMPA 11 running on FMS11 - Macbook pro)