3 Replies Latest reply on Jan 30, 2013 2:28 PM by user21605

    How to display Invoice Totals per month in one Report/Screen

    user21605

      Title

      How to display Invoice Totals per month in one Report/Screen

      Post

      Hi All,

      I have been reading through the forums but haven't found a solid answer to my issue.

      Basically I'm trying to find a way of displaying Invoices Total Amounts for each month on one screen/report.  That can update as each month passes.  Also show every month for each year and summarize each year as well.  So you can see what you have turned over for each month on one screen.

      I currently have an Invoice Table which reads from and Quotation Table that in turn read of a Line Items Table.  The line Items Table pulls products and clients together for the Quotation Table.

      I would appreciate your thoughts.

      Thanks
           Brandan

        • 1. Re: How to display Invoice Totals per month in one Report/Screen
          philmodjunk

               Presumably, you have a date field in your Invoice table. I'll name it InvoiceDate.

               If you do not already have them, define two fields of type calculation as follows:

               cInvoiceYear:   Year ( InvoiceDate ) //select "number" as the return type

               cInvoiceMonth: InvoiceDate - Day ( Invoicedate ) + 1 //Select "date" as the return type

               This last calculation, btw, returns the date for the first day of the same month. So all records dated in the month of January, 2013 will return a date of 1/1/2013.

               Now you can set up a list view layout based on Invoices.

               To this layout, use Part Setup.. from the Layouts menu to add two sub summary layout parts:

               Make the first sub summary layout part "sorted by" cInvoiceYear.
               Make the second sub summary layout part "sorted by" cInvoiceMonth

               I'm guessing that you do not want to list the individual invoice records as separate items in this report. If that is correct, delete the body layout part from your layout.

               In both of these sub summary layout parts, place the same summary field defined in Invoices. Define this summary field to compute the total of your Invoice total field.

               You can put cYear in the first sub summary layout part to identify the year. You can put cMonth In the second sub summary layout part to show the month and year. you can use data formatting on the inspector's data tab to choose a date format that only displays the month and year.

               Put this same sub summary layout part in a trailing grand summary layout part to show a grand total.

               In order for this report to be visible, you must sort your records by cInvoiceYear and cInvoiceMonth. The resulting report can be based on all of the invoice records or just a selected group of invoice records pulled into your found set as the result of performing a find or via Go to Related Records, but after such an operation, make sure to sort the records as both produce an unsorted found set of records.

          • 2. Re: How to display Invoice Totals per month in one Report/Screen
            user21605

                 Hi Phil,

                  

                 Thanks for you reply.  I think I have followed your instructions but I'm not getting the correct results/display.  Pic attached.

                  

                 Thanks
                 Brandan

            • 3. Re: How to display Invoice Totals per month in one Report/Screen
              user21605

                   Apologies, I have now sorted them by cInvoiceYear and cInvoiceMonth which now shows each month results and in what year..

                   Fantastic !  Thank you so much for your help !

                   Brandan