5 Replies Latest reply on Jan 31, 2011 12:30 PM by philmodjunk

    More complicated charts

    DonBragger

      Title

      More complicated charts

      Post

      Hi all,

      I was wondering if someone could help me with some charts. What I have is a simple database:

      customer --< projects --<lineitems >--< invoice

      What I would like to do is:

      1) Report on the total amount invoiced for all customers within a date range. This I can do.I set up a start and end date in the customer field and related those with the customer_ID to the invoice table and summed the totals.

      2) This is the bit I need some help with. How can I allow the user to select a company and show the total invoiced for particular projects for a given date range. I'm not sure which table it should be viewed from or how to quite set it up.

      Thanks in advance.

      Don

        • 1. Re: More complicated charts
          philmodjunk

          Is company a field in Customer? Is there more than one customer from the same company?

          I'd base the report layout on LineItems, given the structure you have there. (Many would link projects to invoice rather than lineitems, but then may be one invoice can bill on more than one project?)

          You can perform a find on this layout for all line items falling in a given date range and which have a related customer record with the specified company name.

          Summary fields can then compute the total. (And there are ways to get just this total if you don't want to see the individual items.)

          • 2. Re: More complicated charts
            DonBragger

            I've made projects link to lineitems because it is possible, like you say, to invoice for more than one project.

            If the report is based in lineitems, how can I graph by project? I mean, how can I group all the projects together on the x-axis?

            Thanks so much for you help.

            Don

            • 3. Re: More complicated charts
              philmodjunk

              Every line item links to one and only one project. Thus, if you sort by ProjectID (or a project name field in the related projects table if you want this to be alphabetical by a name.) Then you can use the grouped data for your graph. You will need summary fields to compute the totals and you may find that you need a calculation field that uses Get Summary to compute the project sub totals for your chart.

              It's tempting to try this in Projects, but then finding records by a range of dates becomes problematic.

              • 4. Re: More complicated charts
                DonBragger

                Thanks Phil. I've got the charts to work, now I want to do a report and struggling. For the charts I've used the calculation of sum(inv_total) to display total costs. How can I make a report like:

                Company

                    Project and costs

                             subtotal costs for company

                                       Total costs for date period

                Thanks!

                Don

                • 5. Re: More complicated charts
                  philmodjunk

                  Perhaps this tutorial on summary reports can give you some ideas: 

                  Creating Filemaker Pro summary reports--Tutorial