I have a table and report where we calculate each employee's individual capacity percentage by adding up the hours they have placed into a table and dividing it by 40. I now need to take those employees and show the same type of percentage but based off each manager. So if Team A has 4 people, Team B has 5 and Team C has 3, how can I show the team's percentage in a report?
Qrew LegendI did a test and I think I got it to work.
One Manager has many Employees who have Many Time records. Look up Manager name down to Employee and from there down to Time records.
Make a Summary report on the grandchild Time table.
Summarize Hours
Group by Manager and then by Employee.
Create two Summary Variables.
1. Employee Name based on Distinct Count
2. Hours by Regular totals
Then make a Step 2 Summary Formula called Employee Utilization, type is numeric percent
Formula is
[Hours (tot)]/([Employee name (distinct count)]*40)
//those will be the only two fields you can select from the field helper drop down in the formula box
Then go back up to the Summarize what section above and add in the Summary Variable which may still be called Summary Formula 1 or if you first save the report and edit again it will get renamed to Employee Utilization and add that to the summarize what, after Hours.
This is actually the first time I got to use distinct count in a summary report so that was pretty cool.
So what is actually, happening here is the distinct count is counting the distinct unique different employees. Then the summary variable multiplies them by 40 which is their theoretical standard maximum work week per distinct employee. And then if we take the total hours divided by that, that is their individual productivity %.
In my quick test with just a limited amount of data the totals actually came out correct as well.
Feel free to post back because this was actually quite an interesting question. Let me know if you get it working OK, or not.
