Report filter for multiple levels of organization based on recursive manager structure

[SuzyPa]

We have approx 700 users and an org structure that is 6 levels deep at its max - I was expecting that I could i could create a 'Reports to Me" filter that would give me everything (say all Priority 1 Risks) that are assigned to anyone in my org tree - so if I'm a VP, I get my P1 risks plus P1 risks for all of my Program Directors, their Application Managers, and the teams underneath them. Yesterday my configuration consultant created a custom field to track this and generated the report I was looking for - But now I need to maintain that field and it's duplicate information to what should be already in the system. Any thoughts? Is there a better way? Suzy Park Partners Healthcare System, Inc.

[Doug_Den_Hoed__AtAppStore]

Hi Suzy, I invite you to consider a few of or options, which sound similar to what you are asking for, Our Executive Reports solution provides that type of org chart reporting (which you can then tailor yourself). "http://store.atappstore.com/product/executive-dashboards/">http://store.atappstore.com/product/executive-dashboards/ To make it work, we simply attach the custom form to each user (one time) and then recalculate custom data periodically to keep it current, either manually in Workfront, or using one of our Recalc solutions. "http://store.atappstore.com/product/force-custom-data-double-recalc/">http://store.atappstore.com/product/force-custom-data-double-recalc/ "http://store.atappstore.com/product/recalc-helper/">http://store.atappstore.com/product/recalc-helper/ Regards, Doug

[melindal3825541]

I again use a custom field on a custom form. Mine is a calculated field called Manager Hierarchy. This just uses the Manager's Name so requires no extra maintenance if that is maintained. Here's the calculation: CONCAT(IF(ISBLANK(Manager.Manager.Manager.Manager.Manager Hierarchy),"",CONCAT(Manager.Manager.Manager.Manager.Manager Hierarchy," - ")),IF(ISBLANK(Manager.Manager.Manager ID),"",CONCAT(Manager.Manager.Manager.Name," - ")),IF(ISBLANK(Manager.Manager ID),"",CONCAT(Manager.Manager.Name," - ")),IF(ISBLANK(Manager ID),Name,CONCAT(Manager.Name," - ",Name))) Basically this list out in name form Abe - Bob - Cindy - Diane - Ed - Frank - George Assuming that Abe is Bob's Boss, Bob is Cindy's .... and this is George's User Form But on Abe's form it's only Abe And on Bob's form it's Abe - Bob Etc. No extra punctuation (That's why I check ISBLANK so much) and arbitrary depth (but you may need to do multiple recalculations for every 4 levels of hierarchy when you are first setting up or are changing upper management). So if you have more than 4 levels of manager's, use that person's Manager Hierarchy Field, then put each manager and end with yourself. If you have no manager it will just list yourself. This lets me create manager reports where owner's manager Hierarchy contains $$USER.name and it will show only my work for individual contributor's and all of my reports and their reports if I'm a manager of any arbitrary depth. If you have significant depth in your organization reflected in Workfront, you may need to do several recalculations to get everyone to appear all the way up. -- Melinda Layten, Senior Consultant Work Management Improvement CapabilitySource Phone: (484) 505-6855 site: www.capabilitysource.com email: melinda.layten@capabilitysource.com - we simplify your work so you can run your business -

[SuzyPa]

Thank you! I will try this! Suzy Park The information in this e-mail is intended only for the person to whom it is addressed. If you believe this e-mail was sent to you in error and the e-mail contains patient information, please contact the Partners Compliance HelpLine at http://www.partners.org/complianceline . If the e-mail was sent to you in error but does not contain patient information, please contact the sender and properly dispose of the e-mail.

[MoniqueEvans]

Thanks for posting this Melinda. I copied your calculation and also named the field Manager Hierarchy but when I pasted the code in, I get an error message "Custom Expression Invalid: This is an invalid custom expression, please try again." I tried looking through to make sure there wasn't an extra comma or parentheses or anything but don't see what could be causing the error. Could you please assist? Monique Evans Stanley Black & Decker, Inc.

[William--]

Just taking a stab in the dark here, because I know Melinda's code works for us: Have you tried creating a Manager Hierarchy field with a blank calculation; saving the user form; then editing/pasting the calculation afterwards? The expression is getting validated in real time - so if the Manager Hierarchy field doesn't yet exist in Workfront, the validation could be failing. William English

[MoniqueEvans]

William you are a genius! I would have NEVER thought of that. I was racking my brain, never once occurred to me that the calculation refers to itself Monique Evans Stanley Black & Decker, Inc.

[Christina_Jarosz]

Just finding this thread and it's making my head spin....egash. Will this work for timesheet reporting as well? Looking to build out time reporting dashboard, where $$UserID will be always open up to their own direct reports, or direct-direct reports respectively. Christina Jarosz Ascensus

[MelindaLa1]

Yep this should work for timesheets as well. Or even hours. Since the form is on the user you can use it anywhere that is less then 3 hops from a user. Melinda Layten Technical Project Manager - API and Integration Workfront

[Christina_Jarosz]

Unfortunately it doesn't seem to want to populate with the wildcard - am I missing something? The resulting field will only show up as a text field from the calculation - it won't let me pull a wildcard result. Christina Jarosz Ascensus

[MelindaLa1]

Manager Hierarchy Contains $$USER.name -- "https://www.workfront.com/" style="text-decoration-line:none;box-sizing:border-box;color:rgb(0,161,208)">

---

Melinda Layten Technical Product Manager: API, Fusion and Enhanced Authentication M: (484) 505 6855 "https://www.workfront.com/" style="color:rgb(248,130,47);text-decoration-line:none;box-sizing:border-box;font-size:12px">workfront.com

[Christina_Jarosz]

What am I doing wrong? It doesn't seem like it want's to take the $$USER.name for any instance. Christina Jarosz Ascensus

[MelindaLa1]

You want contains not equal. Equal switches to the ID, contains is looking for a substring.

[AlyshaSa]

HI William, I attempted this as well when setting up the field for myself (custom calculated field on custom form), but even after saving the form with the calculation blank and then going back in to add the calculation, I still get the same error - "Custom Expression Invalid." Any ideas on what could be causing this? Anything I might have overlooked? Melinda or Monique, if you have any input, it would be incredibly welcome as well. Alysha Sarrade Jackson Family Wines Inc

[MelindaLa1]

The two most common problems: your field isn't named Manager Hierarchy with the same spelling and capitalization (or edit as needed) Losing a parenthesis. I like to edit complex formulas like this in sublime or another text editor with parenthesis matching. It is so easy to accidentally add or delete one. Finally work inside out. Start with the contents of the inner most parenthesis and build the formula back up. If none of these help, post your calculation here. Melinda Layten Technical Project Manager - API and Integration Workfront

[AlyshaSa]

Sometimes it's the simple things (as I facepalm). My field was named differently. Thank you so much for your help! Alysha Sarrade Jackson Family Wines Inc

[BrandonNW]

Thanks guys! I have a twist on this though...... Is there a solution if I wanted to group this in a chart? If I want to see all of a Level 3 manager's reports and the level 2 manager's reports and the level 1 manager's reports in a chart view, could this be done? If I group by "manager hierarchy" it pulls in every user combination of manager path as a different data set. I just want to see all the hours the level 3 manager is accountable for... Brandon Pritchard System Administrator iNHouse Marketing - Nationwide pritcb1@nationwide.com