8 Replies Latest reply on Aug 8, 2010 2:00 PM by jringold

    Report filtering

    jringold

      Title

      Report filtering

      Post

      Hello, I have made great progress with my donors database, and I now I just need to finish up some reports.


      Basic info.

       
      Filemaker version: 11

      Operating system: XP

      Database info.: Donors::DonorID = Donations::DonorID
      I have created a summary field in the donations table which totals the donations, and that correctly calculates the total donations for that donor.

      My level of experience: Filemaker newbie, competent with SQL and relational databases

       

      What I am trying to accomplish:

      I can create a report that shows the total donations for each donor. This report is based on the "donations" table, uses a summary field to calculate the total donations for each donor, and draws the donor name and so forth from the related "donors" table. I'd like to do a few simple things to apply criteria to the report, but I'm such a Filemaker newbie that I'm having a hard time.

      1) For instance, I'd like to show only donors who have given a total of (for instance) $1,000 or more. That's the main thing that I'd like to accomplish.

      2) It would also be nice show a grand total of donations for this set of records, but when I reference my summary field for a grand total row in the report, it appears to be using the total donations for the last record only (rather than totalling for all records.)

      I hope this is clear and thanks for any help!

       
       -Jim

        • 1. Re: Report filtering
          philmodjunk

          1) In donors, define a calculation with the sum function to compute the total donations for that donor. On your report layout perform a find that puts "> 1000" in this field.

          2) if this is a straight summary field, putting it in a footer, header or grand summary part should compute the total for the entire found set. If it doesn't, check it's position in the layout. Any chance it's a pixel or two too close to a body or sub-summary part? If even one pixel of this field touches or crosses the line separating the part, you may see it treated as a field on that layout part instead.

          • 2. Re: Report filtering
            jringold

            Thank Phil, you rock! #1 worked perfectly. There are a couple of good Filemaker tricks there.

            For number two, I tried making a big footer and putting the summary field clearly in that part, but I seem to return the toal donations for the first donor on the list, rather the total for the found set. I'm using the summary field Donations::Total_Amount, which =Total(Donations::Amount). Maybe I should have this field in the donors table instead as with #1?

            Thanks much!

            -Jim

            • 3. Re: Report filtering
              philmodjunk

              You shouldn't need to.

              Simply defining a field of type summary, chosing the "Total of" option and specifying the amount field as the field being totaled should result in the field reporting the grand total in the footer or grand summary part and a sub-total when placed in subsummary parts.

              • 4. Re: Report filtering
                jringold

                Thanks Phil. I think I've done that. What seems to be happening is that the found set is in browse mode, and whichever individual donor is selected is the total that will be displayed in the footer.

                • 5. Re: Report filtering
                  philmodjunk

                  No, I don't think so, something is going on that we are missing.

                  I just pulled up one of my summary reports , changed the trailing grand summary to Footer, returned to browse mode and clicked on different records. The summary field always returned the total of the entire found set--not the current record.

                  • 6. Re: Report filtering
                    philmodjunk

                    Here's a very simple demo file: http://www.4shared.com/file/QS79yY8G/SummaryExample.html

                    Look it over for differences. Note that placing the summary field in the footer, trailing grand summary and body all compute the total for the found set. Putting the same field in the subsummary part computes a subtotal for that group.

                    • 7. Re: Report filtering
                      jringold

                      Thanks Phil. Looking at your file, the primary difference I see is that for each record (each donor) I am pulling a group of related records (individual donations), that are correctly totaled for the summary field in the body part. So in a sense that is acting as a found set for each donor. Then when I need to a grand total, I need to sum those subtotals. So that's a little bit different than in your example (the field appears the same in the body part and the footer, same as mine works now, but I need the behavior to be different in the body part and the footer.) Does that make sense? (It's getting a bit late here)

                      • 8. Re: Report filtering
                        jringold

                        Hey, maybe I'm onto something here. My current summary field totals the "amount" field in the "donations" table. 

                        If I base a layout on the donors table, and make a new summary field in "donors" which instead totals the "amount_sum" field (the new sum function field that you suggested, it also sums the "amount" field in the donations table) ... then I put that new summary field in a trailing grand summary part I get the correct grand total!