Reporting on top parent rows only, not all rows with children

Nat ✭✭✭✭
edited 08/09/23 in Smartsheet Basics

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


  • ericncarr
    ericncarr ✭✭✭✭✭

    @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.

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭


    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)

  • Nat
    Nat ✭✭✭✭

    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,