Show parent OR task at same level as parent in report
My past due report was showing the naked tasks without context, so I wanted to add the parent.
I added a helper column with '=(PARENT(Tasks@row)) and grouped the report by that column.
That works for some of the items, however here's the issue, some level 3 items have sub-tasks and others don't, so some tasks are at level 3, and some at level 4.
This means on my report, I'm seeing:
Level 3 item
- late level 4 task
- late level 4 task
Level 2 workstream (that is also the parent of the level 3 item above)
- Late level 3 task
That "late level 3 task" now looks like it's in a whole separate stream, even though it's right below the "Level 3 Item" whose subtasks are late. It's too confusing to share and will be a trigger in meetings.
I tried putting "ancestors" instead of "parent" but it gives the same result.
Is there a way to show the complete hierarchy back 2 levels like when you filter the sheet itself?
Answers
-
You could use a JOIN/ANCESTORS combo.
=JOIN(ANCESTORS(Tasks@row), " - ")
-
Thanks so much @Paul Newcome, this is going to help a lot!
(Now to have a couple of team discussions around the length of task names, as the report grouping doesn't wrap the text!)
-
You can also take a look into a WBS solution. There are a number of WBS solutions posted throughout the Community forum.
-
Hi Paul, That's a really good point - I'm assuming team members have an understanding of WBS, but for a variety of reasons they may not be working that way. I'm going to include not assuming this in how I discuss the solution when I bring people on. Thanks again!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 466 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!