10 Replies Latest reply on Jun 2, 2014 9:09 AM by AlishaDenicola

    Create Grand Total Field which Adds up Multiple Summary Fields

    AlishaDenicola

      Title

      Create Grand Total Field which Adds up Multiple Summary Fields

      Post

           Hello all, this is for a personnel database I'm building for my employer. In the database are 48 employees whose details include portals for time off, training, performance reviews, and salaries. Each portal is displayed on a tab in the "details" layout. 

           What I need to do now is build a total report that shows the time off for all the employees. I need to be able to filter all 48 time off records by type or date, and I also need to display a summary of that filtered data. 

           Currently the time off portals are linked to summary fields that show how many time off occurrences each employee has and how many days they've taken off. My problem is that I need to summarize these summaries. 

           My totals report shows each employee in list view, and shows their time off portal. I need to take the total of each employee: Joe 22 days off, Bob 10 days off, Sue 12 days off, and add those summary fields up to create a company-wide time off: Company 44 days off. 

           How can I do that? I've tried GetSummary, and it doesn't seem to add more than one employee's records. I can't create a summary of a summary, so I'm a bit stuck. 

           thanks, 

           Alisha 

        • 1. Re: Create Grand Total Field which Adds up Multiple Summary Fields
          TKnTexas

               When building reports, the data is in the child-table.  Portals are great for displaying child-table data from the parent record.  However, reports should be built from the child-table.  The fields that your filter the portals on would be the break fields for subtotaling the data in the report.  

          • 2. Re: Create Grand Total Field which Adds up Multiple Summary Fields
            AlishaDenicola

                 Right, the problem is this though: 

                 The portal is the child table, correct, the details is the parent table. All of that works fine. However, I need to summarize data that occurs in the portal as well as summarize those summaries. 

                 Example: 
                 For Joe: 

                 Portal shows Time Off Occurrences on new lines like such: 
                 Vacation March 2 - March 4 Total 2 days
                 Sick Leave March 4- March 6 Total 2 days
                 Lieu Time March 6 - March 10 Total 4 days. 

                 So i have a summary field which summarizes the data in the total field in the portal: Total Days Off = 8 

                 But I have 48 employees. I need to be able to total up all of the Total Days Off fields so that if I have: 

                 Joe: Total Days Off = 8
                 Sue: Total Days Off = 20
                 Bob Total Days Off = 12

                 I can display a field like Grand Total Days Off = 40 that adds up all the days off everyone has had. 

                 Any ideas?

            • 3. Re: Create Grand Total Field which Adds up Multiple Summary Fields
              TKnTexas

                   I can envision several possibilities.   All 48 employees with amount of time taken by type, or a total of the three types subtotaled.  Without seeing your data structure it is a little harder.

                   Sort on Person on Type.

              • 4. Re: Create Grand Total Field which Adds up Multiple Summary Fields
                philmodjunk

                     Even with the followup information provided, this still sounds like a report that can be built on a layout based on the child table. You can perform a find to find all records in the specified date range and all or just some employees.

                     But it is also possible to set up other approaches that work from your current layout. But we'd need a more detailed understanding of how you record this data in your fields and tables.

                     It will also be helpful to know whether or not you are using FileMaker 12 or newer as FileMaker 12 introduces a new function:: ExecuteSQL that can be used to compute such a total more simply than some alternative options we might use with older versions.

                • 5. Re: Create Grand Total Field which Adds up Multiple Summary Fields
                  AlishaDenicola

                       TKnTexas, that is exactly what I want to do. I want to be able to see all 48 employees with potentially a filter to see each type of time off they've taken, and a subtotal that adds up all the time of taken company-wide. 

                       However I can't seem to do that. I also can't seem to upload an image so you all can see my data structure. It's fairly straightforward though: PersonnelDetails is the parent table, and it is linked to TimeOff by field "pk_PersonnelID" 

                       I have all 48 employees showing up in a "Reports" layout that is based in the PersonnelDetails table and shows the portal to the TimeOff table. I have this layout viewing in list view so you can see all 48 employees' time off. The problem is that I don't know how to get a total of all the days off company wide. 

                       Phil, I'm using Filemaker Pro 13. 

                  • 6. Re: Create Grand Total Field which Adds up Multiple Summary Fields
                    philmodjunk

                         Do you use one field for all types of time off with a "type" or "category" field in TimeOff identifying the type of time off taken?

                         Or do you have separate fields for each kind of time off?

                         You should be able to upload an image to this thread by capturing a jpg, png or gif format image of manage | database | relationships and then using the upload an image controls found below Post a new Answer to upload it. If you are using windows, you can press Alt - Print Scrn to copy the current window to the clipboard, then you open Windows Paint, paste into Windows paint, crop your image as needed and then save the file as one of the three file formats supported by the upload am image feature of this forum.

                    • 7. Re: Create Grand Total Field which Adds up Multiple Summary Fields
                      AlishaDenicola

                           I'm on a Mac. But thanks for the info! 

                           There is one field for all types of time off. It's a drop down with the five types of time off there to choose from. 

                      • 8. Re: Create Grand Total Field which Adds up Multiple Summary Fields
                        SteveMartino

                             Some of the suggestions from this post might help:

                        Reporting troubles......probably easy.

                              

                             @Phil (Hi again Phil), regarding your suggestion to the original poster about SQL, when you get a sec...how might that query look?

                             Thanks

                             Steve

                        • 9. Re: Create Grand Total Field which Adds up Multiple Summary Fields
                          philmodjunk

                               To get a grand total, you can:

                               a) define a summary field in the TimeOff table that computes the total time off. If you pull up a summary report for this on a layout based on the TimeOff table, Sub Summary layout parts can include this summary field to show sub totals for each employee, each type of time off and with different sorting orders those sub totals can be arranged in a number of different ways. Then the same summary field, when placed in the header, footer or a grand summary layout part, will show the grand total. You can get different results on such a layout be performing a find for different groups of records and by sorting the records by different sort orders to get different groups for the sub summary part controlled sub totals.

                               See this tutorial on summary reports: Creating Filemaker Pro summary reports--Tutorial

                               or

                               b) Take that same summary field from a) and put it on a layout based on a different table. Then the relationship between the two table occurrences will control what total you see in that field. If you set up a relationship using the X operator to match to all time off records, a summary field selected from that table occurrence will show a total based on all records in the table. If you use match fields that match to all records in a given date range, you get a total based on that date range.

                               c) put the same summary field in a one row filtered portal. Then the summary field displays a total based on what related records also pass the portal.

                               For some ideas on how to use ExecuteSQL, see this thread: FMP 12 Tip: Summary Recaps (Portal Subtotals)

                          • 10. Re: Create Grand Total Field which Adds up Multiple Summary Fields
                            AlishaDenicola

                                 Option A worked perfectly. Thank you again for all your help!