Reporting on top parent rows only, not all rows with children
Hi all,
I have my projects set up as workstreams rather than phases, with a helper column for phases.
I want to add a dashboard widget that lists out each workstream and its % done .
In my hunt through the questions I found that I can add a helper column to indicate if a task has children like this:
=IF(COUNT(CHILDREN()) > 0, 1, 0)
BUT, that includes anything with children. What I really want is everything that shows up when I choose "collapse all" - the top parents.
Am I missing a trick here? I must admit I had this set up as a metric widget on a past project, but my widget just leads to the project sheet (not the summary), and I left no clues :D
Answers
-
@Nat you could use the ancestors formula, that's what I do to grab the specific parent rows I want.
=COUNT(ANCESTORS([Column Name]@row))
That's assuming all your top parent rows have the same number of ancestors (probably 0 or 1 depending on how you have it set up), that way you can filter for just them.
-
Will this work?
Top Level
=IF(COUNT(ANCESTORS()) = 0, 1, 0)
or Top Level and Parent, ie, Top Parent
=IF(AND(COUNT(ANCESTORS([Task Name]@row)) = 0, COUNT(CHILDREN()) > 0), 1, 0)
-
Thanks @ericncarr and @jmyzk_cloudsmart_jp
Both worked, but I went with the second one because it correctly tagged the rows that were a workstream header but don't yet have child tasks. That makes it easier to set up the dashboard before the planning is complete!
Much appreciated,
Natalia
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 210 Industry Talk
- 441 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 300 Events
- 33 Webinars
- 7.3K Forum Archives