7 Replies Latest reply on Jun 4, 2015 7:38 AM by jdevans

    Need help with calculation/summary for report

    jdevans

      Title

      Need help with calculation/summary for report

      Post

      I have a table that collects hours worked by each employee. Each record contains, date worked, hours worked, account worked and employee.

      hours_worked is a number field, and it is what the employee enters in manually as they charge their time to the system.

      total_hours is a summary field that is a sum of hours_worked, and is a cumulative total of all hours put into the system by all employees.

      I'm putting together a sub-summary report and part of it works great, but one piece I'm having trouble extracting is % of an employee's own monthly total worked on each account.

      For example, if I worked 100 hours for the month, but 25 of those hours were to an XYZ account, then 25% of MY time was spent on XYZ account, I need to report that.

      Right now what I'm getting is my percentage of the total of everyone's time.

      I can run a find against that data, and pull out everyone's time for a given month, but in that report, I also need to see each individual's ratio breakdown as well.

       

        • 1. Re: Need help with calculation/summary for report
          philmodjunk

          total_hours is a summary field that is a sum of hours_worked, and is a cumulative total of all hours put into the system by all employees.

          Actually, there are ways to use this field to get many different subtotals depending on how you use/reference this field.

          Sounds like you need to use the GetSummary function. If you refer to a summary field in a calculation, the value returned is a "grand total" based on all the records in your found set. (Unless field is from a related table). GetSummary enables you to access the type of subtotal that you see when you put the summary field in a sub summary layout part, but now you have a the subtotal in your calculation and you can use it to compute your percentages.

          GetSummary ( SummaryField ; BreakField ). The "break field" is the same field that you specify as the "when sorted by" field in a sub summary part. Just like a sub summary part, it does not return a value unless the found set is sorted in a sort order that includes the break field.

          UNLIKE a sub summary part, the break field used must be defined in the same table as both the summary field and your calculation field that uses GetSummary. There are times when this forces you to add a calculation field that simply copies the related field value over so that you can sort on it and use it as the break field.

          • 2. Re: Need help with calculation/summary for report
            jdevans

            OK, my break field seems to be the bugger. I think I need to be able to break simultaneously on employee_id AND the month selected for the report. Any idea how I could do that? I keep getting the same total that I've been getting. I'm obviously doing something really wrong.

            What I have now is a new calculated field- employee_hours. It's calculation is = GetSummary(total_hours; fk_employee_id).

            I suppose this is correct. I can't figure out where to put it and how to sort it to give me an employee's total hours for the month (regardless of account).

            • 3. Re: Need help with calculation/summary for report
              jdevans

              my db is set up like so:

              Employees---<Hours>---Accounts>---Projects with fk_ids on the child tables related = to the pk_ids on the parents, if this help. The report is doing some sorting based on parent tables.

              • 4. Re: Need help with calculation/summary for report
                philmodjunk

                Sort your records by Month, then by employee ID

                • 5. Re: Need help with calculation/summary for report
                  jdevans

                  so, should I create a new sub-summary part for this new GetSummary Field? On what should it sort? Month or employee ID?

                  Does it matter the placement of the part amongst the other parts? Based on my last few posts, do you think I have set up the GetSummary field correctly to get the reporting I seek?

                  • 6. Re: Need help with calculation/summary for report
                    philmodjunk

                    I don't really know how your layout is currently designed, but I don't really see the need for another sub summary part if your current sub summary part specifies Employee ID as it's break field.

                    • 7. Re: Need help with calculation/summary for report
                      jdevans

                      I ended up working on it into the night last night. I must've tried 5 dozen versions of the thing. I tried multiple different calculation, summary field combinations- all while occasionally searching Google for answers. I ended up finding some advice you (Phil) gave someone else while they were trying to do something similar to my case that worked.

                      First I re-ordered my sub-summary parts, making the one that sorts on employee id right underneath the header. This gave me the total_hours per employee, the part I was missing earlier.... It has project_name, employee_name, total_hours.  Keep in mind the whole report is based on a found set of hours for the month, so that part is done as the report opens.

                      Then I put in a second sub-summary part under the first one that sorts on account_id. It contains the account_number field, the account_name field, total_hours, and pct_of_empl_hrs. The total_hours here is giving the correct total per account, since it sorts on account_id. The tricky part was the pct_of_empl_hrs field.

                      I established 4 fields to make it work, and for me it was tricky:

                      1) employee_hrs: Calculation =GetSummary(total_hours;fk_employee_id)

                      2) empl_acct_hrs: Calculation =GetSummary(total_hours;fk_account_id)

                      3) pct_of_empl_hrs: = Calculation =empl_acct_hrs / employee_hrs

                      4) pct_of_total_hours: = Summary =Fraction of Total of hours_worked

                      I put pct_of_empl_hrs (#3) on the Sub-Summary part that sorts on account_id.

                      And then I put pct_of_total_hours (#4) on the Trailing Grand Summary part along with total_hours.

                       

                      Maybe there is a more elegant way to do this, but this was how I got it to give me what I needed. employee_hrs, is only serving to be included in the pct_of_emply_hrs Calculation and isn't on the layout. Same with empl_acct_hrs. SO I end up dividing one GetSummary field by another...your suggestion that I found on a Forum search to another user.

                      Thanks for all your helpful posts Phil.