Show parent OR task at same level as parent in report

Nat

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?

Paul Newcome

You could use a JOIN/ANCESTORS combo.

=JOIN(ANCESTORS(Tasks@row), " - ")

Nat

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!)

Paul Newcome

You can also take a look into a WBS solution. There are a number of WBS solutions posted throughout the Community forum.

Nat

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!

Paul Newcome

Happy to help. 👍️