New to the forum but I've been unable to create the report that I am looking for. I'll keep searching to see if someone was looking for a similar solution.
I am attempting to gather the cost that each manager and all of their direct reports generate each month. I have been able to enter a manual filter and find all of the lines that include a user (user d is located on 4 lines) but even if i do the sum I need to loop and run the sum/count for each line (thousands of lines, hundreds of maangers)
Users are A,B,C,D,E,F,G (E,F & G report to D, D reports to B, B reports to A, A is the top of the company
This data is in 2 data sources
User | manager |
|
| $ per month | Minutes per month |
a |
|
| a | 101 | 100 |
b | a |
| b | 150 | 200 |
c | ab |
| c | 123 | 300 |
d | abc |
| d | 187 | 400 |
e | abcd |
| e | 55 | 250 |
f | abcd |
| f | 19 | 399 |
g | abcd |
| g | 63 | 182 |
The results based on the above would be that all usage would rollup to A, B would include all users except A etc,....... EFG would only include themself.
User | total $ | Total Time |
a | 698 | 1831 |
b | 597 | 1731 |
c | 447 | 1531 |
d | 324 | 1231 |
e | 55 | 250 |
f | 19 | 399 |
g | 63 | 182 |
Hi @bscully1 , I have attached a batch macro that should work for you. You should combine the data sources you are using to create this report to feed into the macro, and then take a unique list of the managers/users you are looking for in your report as the control parameter. Let me know if you have questions. Thanks.