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

Find more posts tagged with

Reports

Dashboards

Metric Widget

Accepted answers

All comments

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

@Nat

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

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.