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.
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).
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.
Sort your records by Month, then by employee ID
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?
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.
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.