3 Replies Latest reply on Feb 8, 2013 9:08 AM by philmodjunk

    Can't get proper totals in sub-summary

    mikemccabe29

      Title

      Can't get proper totals in sub-summary

      Post

            

           I'm using FMP 11 Advanced:
            
           I have a a report using a list layout in which a sub-summary part displays donations for each individual.  Donations fall into different giving categories (High, Medium, Low -- for easy discussion). 
           If there are 50 overall gifts for this year, I'd like to tally how many "High," how many "Medium," and how many "Low," gifts there are.  
            
           I am setting a High, Medium or Low flag for each gift.  
           If ( GIFT = "1-99" ; 1; "0" )
            
           NOTE: I can NOT store or index this field because it references a summary field / unstored / calc field…
           (The calculation “GIFT_TIER_LOW” cannot be stored or indexed because it references a related field, a summary field, an unstored calculation field, or a field with global storage.)
            
           Now I want to tally up those Flags, but they unstirred, so I'm not sure how to do that in the report / "on the fly." 
            
            
           I made a self-join relationship which I had hoped would "find" all the records which have that flag (High, Medium, Low) and then I could create  a field to tally the number of records in the category by using get(found count).  It doesn't work. 
            
           Any ideas? Thanks 

      RawLayout.png

        • 1. Re: Can't get proper totals in sub-summary
          philmodjunk

               How is the lower sub summary layout part set up? What is the "sorted by" field specified for it?

               If your report had this format:

               High
                 Donor 1
                 Donor 2
                 Etc.

               Medium
                 Donor 1
                 Donor 2
                 etc.

               Low
                  Donor 1
                  Etc.

               or this format:

               Donor 1
                  High donations for Donor 1
                  Medium donations for Donor 1
                  Low Donations for Donor 1

               Donor 2
                  etc

               You would not need any self join relationship nor any special calculation fields, just One Calculation field to identify the donation category and a summary field.

          • 2. Re: Can't get proper totals in sub-summary
            mikemccabe29

                 I like your approach.  Thanks.   I hadn't thought of it, but I couldn't get that to work either because of one major goals I failed to mention is that I need a chart or two (pie / bar) at the end to give a graphic display of all the "Giving Tiers".  For example, if 10 people gave I want a chart shoing the three tiers (Hi, Med, Lo).  I did get the numbers (totals for each year group) to tally up properly, so I do have summary fields showing accurate Hi, Med, Lo values now, but all my charts seem to want to show something for each gift (all 10) rather than the three groups. 

            • 3. Re: Can't get proper totals in sub-summary
              philmodjunk

                   If your chart is intended to chart all donations, you can set up its data source settings to be "found set", "groups of records when sorted" and then the bars are your chart can match the sub summary layout part subtotals.