Parent/grand-parent rows in reports

Kristen Olson

Hello! I have tried to follow steps in other posts, but my brain cannot figure it out :) Could anyone help me identify the helper formulas for pulling parent cells into a report?

I need to pull in to a report the Goals and Problem Statements for any Projects that are identified as "Must Have". So I am trying to figure out the formulas I would need in the columns Helper 1 and Helper 2 to identify a parent that has any children with "must have" projects.

I am having trouble figuring out how to nest formulas for accomplishing this.

Thank you!

Kelly Moore

Hey @Kristen Olson

To make sure I understand your screenshot - you have child rows that are must-have projects. You want the grandparent (not the parent) of that row in a report.

In a checkbox helper column, we will identify parent rows that have Descendants with Must Haves.

=IF(AND(COUNT(CHILDREN([2021 Strategy]@row)) > 0, COUNTIFS(DESCENDANTS([Must Have]@row), 1) > 0), 1)

In a Text/Number helper column, (I often call this helper 'Level') the hierarchical level is outlined.

=COUNT(ANCESTORS([2021 Strategy]@row))

If the rest of your sheet follows the structure of your screenshot, you should be able to use the report builder to filter the data, choosing the Helper checkbox column when checked, and selecting this Helper Level column of the appropriate Level(s) (it looks like levels greater than 1 )

Will that work for you?

Kelly