4 Replies Latest reply on Jan 31, 2011 10:22 AM by philmodjunk

    Help with report



      Help with report


      Hi all,

      I'm having some trouble trying to make a report. I have customers --< projects ----< lineitems >----< invoices and would like to make a report on this along the lines of:



          Project and costs

                   subtotal costs for company

                             Total costs for date period


      Any ideas on which table to start from and what calculations needed greatly appreciated.



        • 1. Re: Help with report

          Start the report from the table that contains the records at the most granular level you need to split, sort, etc.  From your relationship diagram that looks like Invoices.  Unless you are prepared to have, say, only a total line in the report for all invoices for that line item, in which case the most granular level you would need to go down to is Line Items.

          It looks like you want to show each Project as having costs in many periods, so it looks like you need to run it from Invoices, as you need the component invoices for any one project to be split across potentially several periods.

          • 2. Re: Help with report

            OK, I got it (I think):
            LineItems>--Projects >--<Invoices >--Customers
            The report is in LineItems and has the following:
            sub_summary_by_Customers - Customer Name
            sub_summary_by_Projects - Projects Name GrandTotal*
            sub_summary_by_Customers - GrandTotal*

            * GrandTotal is a summary field in LineItems for the total amount charged.

            I have two questions from here in:
            1) How can I select a date range?
            2) How can I display this on a pie chart?

            Thanks all!

            • 3. Re: Help with report

              A small point: you confused me, and I think you'll confuse yourself later, by calling the field 'GrandTotal'.  It is a Summary Field, and becomes a sub-summary or Grand Summary depending on where in the layout you place it.  A field GrandTotal in a sub-summary part will not display a traditionally-though-of 'Grand Total'.  (Okay, as long as you know what it means, I suppose...)

              Select the date range by doing a Find on another layout, along with any other criteria, then sort and change to your summary layout.

              • 4. Re: Help with report

                A date range can be entered as find criteria in this format 1/31/2011...2/5/2011

                This can be done manually or in a script.