2 Replies Latest reply on Sep 2, 2009 1:48 PM by etripoli

    Summary of Sub-Summary Summaries

    jregalia

      Title

      Summary of Sub-Summary Summaries

      Post

      First, I'd like to introduce myself.  My name is Jeff and I'm a programmer/analyst at a community college.  I'm an experienced programmer but have very little experience with FileMaker.  I will try to be as thorough as possible without getting into unnecessary information.  My problem:

       

      Human Resources has asked me to create a report of payroll data for the staff.  The main report is a detailed report of each employee, their itemized pay (by paycode), their total pay and at the very end a grand summary of all payroll.  For the purpose of disability insurance, there is a ceiling of $11,994 on any one employee (ie. allowable total = min (total, 11994)).  Calculating this value is easy for each subsumary but since each employee has multiple records (one per pay code) the grand summary is grossly inflated because it is including each total multiple times for each employee.  To make matters worse, some employees even have multiple records of the same pay code because of funding sources.  I can get a total based on records but this does not take into account the per employee ceiling.

       

      My current value fields are:

      Amount: Number (input from file)

      Total Amount: Summary; =Total of Amount

      Total Amount Allowed: Calculation; Unstored, =Min(GetSummary(Total Amount; Employee Sort Key); Max Amount Allowed)

      Total Allowed: Summary; =Total of Total Amount Allowed

      Max Amount Allowed:  Number; Global, Auto-enter Data (11994)

       

      My report is structured as:

      1. Header
      2. Leading Sub-Summary sorted-by Employee Sort Key (employee info)
        1. Name
        2. Employee ID
      3. Sub-Summary sorted-by Pay Code (pay code list)
        1. Pay Code
        2. Pay Code Description
        3. Total Amount
      4. Trailing Sub-Summary sorted-by Employee Sort Key (employee summary)
        1. Total Amount (Actual total of all pay codes for employee)
        2. Total Amount Allowed (Total after price ceiling adjustment)
      5. Trailing Grand Summary
        1. Total Amount (Total of amount for all records)
        2. Total Allowed (BROKEN; Supposed to be total of all adjusted employee totals but actually turns out as adjusted total of employee total once for every record)
      6. I have very basic knowledge of FileMaker but I'm a fast learner.  If anyone has any tips to point me in the right direction, I'd greatly appreciate it.  I am using FileMaker Pro 9 so anything available in 9 I have at my disposal.  Let me know if you need more information and I'll gladly provide whatever is needed.  Thanks for looking.

         

        Jeff

        • 1. Re: Summary of Sub-Summary Summaries
          comment_1
             I believe you need to divide the allowed amount by the count of the records in the group, before totaling it up again for the grand summary.
          • 2. Re: Summary of Sub-Summary Summaries
            etripoli
              

            I have found that in some cases, instead of using an actual summary field, it's better to use calculation fields based on the data in related (or self-related) tables.  This gives you the ability to do very complex summaries, and retain the ability to do 'overall' or grand totals.  In your case, you could relate the table that has the payroll data back to itself by employee & pay range, and create a calculated field that adds up the total pay, and uses that total or 11994, whichever is less.

             

            Just remember one big difference between Summary fields and calculation fields based on related tables - Summary fields only do their calculations based on the found set of records, whereas the calculated fields work on all related records.