10 Replies Latest reply on Dec 30, 2014 5:44 PM by erolst

    Trailing Grand summary not working

    planteg

      Hi,

       

      let's start with a screen capture:

      Grand summary.jpg

      Each line are summaries by week for some numbers but for the last one That works fine. But have issues with some of the Grand Totals on the last line (Trailing Grand Summary). The numbers in the yellow box are fine, but in the red box they are wrong.

       

      The numbers on the summary part (yellow box) are summary fields that are Total of respective fields. Numbers for summary part in the red box are calculation fields calling GetSummary().

       

      For the yellow box, same fields are set on the Grand Total part, they work as expected. In the red box, at first I also set the same fields as on summary part, but got the same value as on the last line. I changed that to Summary fields at Total of respective fields on summary parts. Numbers are wrong, my guess is that I get total for all fields in the database .

       

      I hope my explanations are not too confusing. So the question is: how can I get correct Grand Totals for the columns in the red box ? I thought of using  Execute SQL(), but maybe there something more à la FileMaker to achieve the result.

       

      Thanks

       

      Gilles Plante

        • 1. Re: Trailing Grand summary not working
          erolst

          planteg wrote:

          I hope my explanations are not too confusing. I hope my explanations are not too confusing. So the question is: how can I get correct Grand Totals for the columns in the red box ?

           

          Just a bit …

           

          Have you tried creating Summary fields aggregating the calc fields that are defined with GetSummary() – i.e. cTotalDeEntrée and cCumulatif – as sTotalOfTotalDeEntrée and sTotalOfCumulatif?

           

          Or just post your file.

          • 2. Re: Trailing Grand summary not working
            planteg

            erolst,

             

            actually this is how fields are defined:

             

            For each week (sub-summary part)

             

            Under Total des entrées Calculation unstored , GetSummary( SommeTotal; WeekDate ) field name is Total des entrées

            Under Cumulatif Calculation unstored, GetSummary ( SommeTotal; WeekDate) - Employés::HeuresSem field name is CumulatifEmployée

             

            For the Trailing Grand Summary

             

            Under Total des entrées Summary, Total of Total des entrées

            Under Cumulatif Summary Total of CumulatifEmployée

             

            So it's exactly as you suggested if I am right. My guess is the last two in the the Trailing Grand Summary because it totals calculation field that do not have the proper context.

            • 3. Re: Trailing Grand summary not working
              erolst

              planteg wrote:

              So it's exactly as you suggested if I am right. My guess is the last two in the the Trailing Grand Summary because it totals calculation field that do not have the proper context.

               

              Yes, you're right; but for a summary field, context means found set and the part where it is placed – and there is your problem (which I only recognized now after taking a closer look at your screenshot): you don't want a trailing grand summary, you want a trailing sub-summary by employee!

              • 4. Re: Trailing Grand summary not working
                planteg

                Trailing sub-summary . In the report you see, the week lines are not in the body, but are sub-summary parts. Then how can I implement a trailing sub-summary, which is in fact a sub-summary below the body ?

                 

                I solved part of my issue this way: Total des entrées was a summary field on a summary field . I changed it to the summary field that what summarized and it works. For the Cumulatif column, I can't have a summary field because I need a calculation. I can't see how I can fix that one.

                 

                Thanks

                • 5. Re: Trailing Grand summary not working
                  erolst

                  planteg wrote:

                   

                  Trailing sub-summary . In the report you see, the week lines are not in the body, but are sub-summary parts. Then how can I implement a trailing sub-summary, which is in fact a sub-summary below the body ?

                   

                  For this to work at the moment, you have a leading sub-summary by Employee ('Jean Amotte', 'Semaine', column headers, etc.), and sub-summaries by Week (and, yes, no body part); now you need to add a trailing sub-summary by Employee, into which you place the summary fields that summarize the “Total de entrées” and “Cumulatif” calculation fields by employee; because, contrary to what the legend on your layout says, this is not a “grand totaux”, but a “totaux du employée” (if you pardon my French … )

                  planteg wrote:

                  Total des entrées was a summary field on a summary field .

                  Nope, that's not possible; if anything, it was a summary of a calculation field that uses/used getSummary() (but that still leaves it a calc field).

                  • 6. Re: Trailing Grand summary not working
                    erolst

                    Come to think of it; I think to get your desired results, you don't need any GetSummary() calculations – just the correct (and correctly placed) layout parts.

                     

                    See attached a quick mockup of your screenshot. I had no time to add “cumulatif”, but that will work along the same lines.

                    • 7. Re: Trailing Grand summary not working
                      planteg

                      Hi erolst,

                       

                      thanks for the demo database . Tried it your way, I still have a wrong result for the last column though.

                       

                      Please go back to initial screen capture. So it's not working: I know why but can't make it work. Summary fields as defined in the table are place holders that get values from the context in the report. In my case it's either a week or the entire Find Set.

                       

                      The problem lies in the way the last column has to be calculated: it's in fact the total_for_the_records_in_a week, which is displayed under Total des entrées, less a number that's valid for the whole week. That number that get subtracted is not in any of the records of the Found Set, but in a record in a related table. That works fine per week, but in the trailing sub-summary it fails.

                       

                      I manage to succeed in the following way:

                       

                      1. The field that the user selects the period and the user are in a global table. I added a calculation field that gets me the number of weeks in the range selected.
                      2. Just for the trailing summary, I added a calculation filed computed this way

                       

                                SommeTotal - (z_Resources::z_NbSem * Employés::HeuresSem) where SommeTotal is user for Total des entrées columns - it works fine in the trailing sub-summary -, z_NbSem is the number of weeks and Employés::HeuresSem is the number of hours the employee works per week .

                       

                      But I have a last question: is the solution correct regarding best practices for FileMaker.

                       

                      Thanks

                      • 8. Re: Trailing Grand summary not working
                        erolst

                        planteg wrote:

                        but can't make it work.

                        […]

                        I manage to succeed in the following way:

                        Now what is it?

                        planteg wrote:

                        The problem lies in the way the last column has to be calculated: it's in fact the total_for_the_records_in_a week, which is displayed under Total des entrées, less a number that's valid for the whole week. That number that get subtracted is not in any of the records of the Found Set, but in a record in a related table

                        It probably should be in the line items table, because if you ever change that value for a given employee, a mere reference will falsify your older calculations (think invoice & changed article prices). So you should use a lookup / an auto-enter calculation to bring & store that value into the time sheet table.

                         

                        Your problem brings us back to using GetSummary – exactly like placing the same summary field into different parts to obtain different results, you can use GetSummary() with the same summary field, but different break fields.

                         

                        In this case: Using a calc field with GetSummary ( TimeSheet::hours ; TimeSheet::cWeek ) will give you the total for an employee per week (if week is in the sort order, of course…); use the result to perform arithmetic with the preference week value for the employee. Then create a summary field that summarizes this calc field for the employee's records within the found set (this looks like some sort of running total to me).

                         

                        Usually, all these summary fields / calc fields using summaries / summary fields using calculated values etc. are relatively straightforward by themselves – but sometimes you start losing track of what does what …

                        planteg wrote:

                        But I have a last question: is the solution correct regarding best practices for FileMaker.

                        Not to my knowledge; rather than fumble around with global tables etc., try the solution I described above; it has all its ingredients in the same table and doesn't rely on user intervention/selection.

                        • 9. Re: Trailing Grand summary not working
                          planteg

                          Hi erolst,

                           

                          It probably should be in the line items table, because if you ever change that value for a given employee, a mere reference will falsify your older calculations (think invoice & changed article prices). So you should use a lookup / an auto-enter calculation to bring & store that value into the time sheet table.

                          I understand the reasoning behind the use of a lookup field. And I also understand that it's much easier when everything is in the same table. In this case, I can't have the number_of_hours_per week in the time sheet table. Each record in that table is part of the day work on a specific project for a specific customer. That would mean repeating that number_per_week on each record, that wouldn't work. The fact that the number could change at some point in time could be an issue.

                           

                          By the way, regarding time sheet, there is only a one table, that is some kind of line items table. This table has relationships with customers, projects (in relation to customers) and employees. The global table is only containing fields needed to specify reports parameters needed to run the reports.

                           

                          Thanks

                          • 10. Re: Trailing Grand summary not working
                            erolst

                            planteg wrote:

                            I understand the reasoning behind the use of a lookup field. And I also understand that it's much easier when everything is in the same table. In this case, I can't have the number_of_hours_per week in the time sheet table. Each record in that table is part of the day work on a specific project for a specific customer. That would mean repeating that number_per_week on each record, that wouldn't work. The fact that the number could change at some point in time could be an issue.

                             

                            That could definitely become an issue, and I don't see where the problem is with storing that number in the TimeSheet table: the alternative would be to maintain a related table per employee in which to store the hoursPerWeek for a given period, and reference that via employee ID and date range; but this is more complicated.

                             

                            But then, it's your solution …   Happy New Year etc.