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

Title

Monthly sales report - How to show empty months?

Post

     Hello!

     In FMPA 11, I have 3 tables: customers, invoices, line invoices.

     I've managed to make a layout with monthly total invoices as follow:

Jan2012

     customer 1 - total invoice jan2012

     customer 2 - total invoice jan2012

Feb2012

     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:

      

Jan2012

     customer 1 - total invoice jan2012 (eg. 100$)

     customer 2 - total invoice jan2012 (eg. 150$)

Feb2012

     customer 1 - total invoice feb2012 (eg.120$)

     customer 2 - total invoice feb2012 (eg, 0$)

Mar2012

      

     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)

      

      

      

      

Screen_Shot_2012-10-11_at_17.39.57_.png

Outcomes