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?


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!