1 2 Previous Next 19 Replies Latest reply on May 21, 2014 12:26 AM by erolst

    Summarizing Calculation fields in Summary Parts

    BarCab78

      I have a report with SubSummary parts that have Summary fields and some Calculation fields using GetSummary. That works fine on the SubSummary parts based on sort but I need to be able to Total the Calculation fields down in the Trailing Grand Summary.

       

      Looks like you can only use Summary fields & Calculation fields using GetSummary in SubSummary parts and Trailing Grand Summaries to subtotal the values from each grouping of records. I can't Summarize the Calculation fields using GetSummary from the above SubSummary parts.

       

      See attached example PDF. I need to Summarize the Oval fields in the SubSummary parts, down in the Trailing Grand Summary. I hope this makes sense.

        • 1. Re: Summarizing Calculation fields in Summary Parts
          erolst

          You can summarize calculation fields allright, but the problem is that you only need one result per group.

           

          You could do that by making the calc fields that use GetSummary conditional on position; see attached PDF and the calculation on the layout.

          • 2. Re: Summarizing Calculation fields in Summary Parts
            keywords

            The key with the GetSummary calc is the break field. See if the attached sample file is any help.

            • 3. Re: Summarizing Calculation fields in Summary Parts
              BarCab78

              I didn't quite understand your diagram. Can you provide a clearer picture?

              • 4. Re: Summarizing Calculation fields in Summary Parts
                erolst

                Even though you were responding to yourself, I assume you meant my post.

                BarCab78 wrote:

                I didn't quite understand your diagram. Can you provide a clearer picture?

                You mean: in a higher resolution? I found it quite legible …

                 

                Anyway, you need to add a calculation field that calculates a value for one record only per sorted group.

                 

                If you had a found set of line items sorted by the break field theDate (e.g. all time card entries for a member), you would define a calc field as

                 

                Case (

                  theDate ≠ GetNthRecord ( theDate ; Get ( RecordNumber ) + 1 ) ;

                  Max ( GetSummary ( sQty ; thedate ) - 8 ; 0 ) * 100 )

                )

                 

                In English: if the date of a given record is not equal to the date of the next record, (then) calculate the count of hours for that date by using GetSummary(), subtract the (arbitrarily chosen) limit of 8, take the maximum value of the remainder and zero, and multiply that by 100.

                 

                In a found set sorted by day there will be only be a single record within every group where the date is not equal to the following one, so this calculation will only be performed once for a group.

                 

                Summarize this field, and you have the sum of all overtime hours costs in the found set. (Or calculate the overtime hours using the same method, summarize that field, and multiply the result by your overtime rate; or … you get the picture now, I take it.)

                • 5. Re: Summarizing Calculation fields in Summary Parts
                  BarCab78

                  Thanks for the GetSummaryDEMO file but do not currently have FileMaker 12 installed so wasn't able to open it. Would you happen to have it in FileMaker 11 or earlier format. I'm still trying to summarize summary fields in subtotal parts down in the Trailing Grand Summary part.

                  • 6. Re: Summarizing Calculation fields in Summary Parts
                    erolst

                    BarCab78 wrote:

                    I'm still trying to summarize summary fields in subtotal parts down in the Trailing Grand Summary part.

                     

                    Instead of trying, why don't you implement the solution outlined in the above explanation (which you asked for)?

                    • 7. Re: Summarizing Calculation fields in Summary Parts
                      keywords

                      OK, here is an fp7 version of the demo file, but DO take note of erolst's posts. My demo shows you that a summary field gives a different result depending on its context, and can give the same result as a GetSummmary calc; this might help you grasp the basics, but erolst's logic will take you further though.

                      • 8. Re: Summarizing Calculation fields in Summary Parts
                        BarCab78

                        Thanks very much for your help but I think my problem with Summarizing Summary fields in my Trailing Grand Summary are complicated by the fact they are referring to Calc fields in the above Summary parts the are using the GetSummary function. I'd like to forward a couple of PDF's that could illustrate better than I'm explaining.

                        • 9. Re: Summarizing Calculation fields in Summary Parts
                          Stephen Huston

                          Try using a simple Summary field rather than calcs. Summary fields return the correct subsummaries and grand summaries in the same field based on the context of the layout part. You don't need the calc-subsummary fields at all for a subsummary report, only if you need the calc result in a non-subsum location.

                          • 10. Re: Summarizing Calculation fields in Summary Parts
                            BarCab78

                            I updated my comments in the FM Dev Forum regarding my problem with Summary

                            fields in the Trailing Grand Summary. I also uploaded 3 PDF's that may

                            illustrate my problem better than words.

                             

                            Thanks for all your help.

                            • 11. Re: Summarizing Calculation fields in Summary Parts
                              BarCab78

                              I would love to have used only Summary fields in the Summary parts but some

                              Summary parts are requiring a Calc field using GetSummary values to

                              determine Reg Time hrs, O.T. hrs, Reg Time pay rates, O.T. pay rates and

                              various other values utilizing the values in the Summary fields already in

                              the same Summary parts.

                               

                              Many of these fields require the results in the Summary fields. The only

                              way is to use the GetSummary function. Maybe there's a better way.

                               

                              Thanks

                              • 12. Re: Summarizing Calculation fields in Summary Parts
                                Stephen Huston

                                How about a calc which simply gathers those values for the specific record, and then a simple summary field of that calc?

                                 

                                If that is still duplicating some values, consider scripting the collection/summation of the data the section levels into global variables, which can be added in a script and placed on the report's grand summary in another global variable.

                                • 13. Re: Summarizing Calculation fields in Summary Parts
                                  erolst

                                  BarCab78 wrote:

                                  I also uploaded 3 PDF's that may illustrate my problem better than words.

                                   

                                  Indeed; your fields very clearly indicate that you have a structural problem (field names ending on 1,2, 3 etc are always a dead giveaway!). This is why you weren't able to translate my suggested approach into your system.

                                   

                                  Consider instead

                                   

                                  Plan/Project --< Work (fk_projectID, fk_staffID, fk_categoryID, date, hrs; sHrs, cOvertimeForStaff, cOvertimeForWorkType (?), sOvertimeForStaff, sOvertimeForWorkType (?)) >-- Staff

                                   

                                  A selfjoin-relationship for Work where

                                   

                                  Work::fk_staffID = Work_self::fk_staffID AND Work:date = Work_self::date

                                   

                                  lets you define cOvertimeForStaff = Max ( Sum ( Work_self::hrs ) - 8 ; 0 ) * yourOvertimeRate

                                   

                                  By the same token, a selfjoin-relationship for workType where

                                   

                                  Work::fk_categoryID = Work_self::fk_categoryID AND Work:date = Work_self::date

                                   

                                  lets you do the same for each worktype – in one field!

                                   

                                  What you will/want to see now simply depends on your find requests and the placement of your summary fields.

                                  • 14. Re: Summarizing Calculation fields in Summary Parts
                                    BarCab78

                                    I really appreciate your help in trying to understand my Summary problem. This might help clarify my problem.

                                     

                                    An Installation Work Order ( 1 record ) has 3 potential installers ( 1 set of fields for each ). I need to evaluate each installer's Reg Time & OT on a Daily Basis (anything over 8 hrs in 1 day becomes OT).

                                     

                                    A typical day may have Installer 1, Installer 2 and/or Installer 3, working on 1 or more projects (in a single day). That's why I need to evaluate Reg Time & OT for each installer for each day worked.

                                     

                                    Even if it's only 1 installer, doing 3 separate installations in 1 day, I need to summarize the hours worked, by Appt. Date in the Summary Part. There are a few calculations needing the GetSummary function to calculate the Total Hrs for one day. It's these Calc fields using the GetSummary funtion that I can't summaize in the Trailing Grand Summary. Here's the fields involved:

                                     

                                    c_Task Duration 1 = (Task Stop 1 - Task Start 1)/3600

                                    s_Duration 1 Summary = Total of: Task Duration 1

                                     

                                    c_Reg Time 1 = If (GetSummary ( Duration 1 Summary ; Appt. Date) ≥ 0 ; Min ( GetSummary ( Duration 1 Summary ; Appt. Date) ; 8) ;0)

                                     

                                    c_Installer 1 Lunch = If (GetSummary ( Duration 1 Summary ; Appt. Date ) ≥ 6 ; -.5 ; "" )

                                    c_Installer 1 Wrk Hrs = GetSummary ( Duration 1 Summary ; Appt. Date ) + Installer 1 Wrk Lunch

                                    ??s_Installer 1 Wrk Hrs Summary =  Total of: Installer 1 Wrk Hrs

                                     

                                    c_Overtime 1 =If ( Installer 1 Wrk Hrs ≥ 8 ; Max (Installer 1 Wrk Hrs) - 8 ; "" )

                                     

                                    c_Installer 1 RT Rate = Round (( GetSummary ( Installer 1 PcWrk Wage Summary ; Appt. Date ) + ( GetSummary ( CS 1 Wage Summary ; Appt. Date )))/ Installer 1 Wrk Hrs ; 2 )

                                    c_Installer 1 RT Wage = Round ( Reg Time 1 * Installer 1 RT Rate ; 2 )

                                    ??s_Installer 1 RT Wage Summary = Total of: Installer 1 RT Wage

                                     

                                    c_Installer 1 OT Rate = If ( Overtime 1 > 0 ; Round ((GetSummary ( Installer 1 PcWrk Wage Summary ; Appt. Date ) + ( GetSummary ( CS 1 Wage Summary ; Appt. Date )))/ Installer 1 Wrk Hrs * 1.5 ; 2 ) ; "" )

                                    c_Installer 1 OT Wage = If ( Overtime 1 > 0 ; Round ( Overtime 1 * Installer 1 OT Rate ; 2 ) ; "" )

                                    ??s_Installer 1 OT Wage Summary = Total of: Installer 1 OT Wage

                                     

                                    n_Installer 1 Vac Hrs = a Number field

                                    c_Installer 1 Vac Wage = If ( Installer 1 Vac Hrs > 0 ; Round(Installer 1 Vac Hrs * Min Wage 1 Rate;2) ; "" )

                                    s_Installer 1 Vac Wage Summary = Total of: Installer 1 Vac Wage

                                     

                                    c_Installer 1 Gross Wage = Installer 1 RT Wage + Installer 1 OT Wage  + GetSummary ( Installer 1 Vac Wage Summary ; Appt. Date )

                                    ??s_Installer 1 Gross Wage Summary = Total of: Installer 1 Gross Wage

                                     

                                    The Summary Fields beginning with "??c_" are the ones I can't summarize correctly due to reliance on GetSummary calcs in the SubSummary parts.

                                     

                                    Any ideas or work around to summarize these summary fields dependant on GetSummary Calcs above?

                                    1 2 Previous Next