AnsweredAssumed Answered

Monthly sales report - How to show empty months?

Question asked by paolobkk on Oct 11, 2012
Latest reply on Oct 11, 2012 by paolobkk


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)