Parent/Child Hierarchy when Building Reports

Chris Hallo

@Paul Newcome, thank you for that formula. I used it in my scenario:

=IF(COUNT(CHILDREN([Milestones/Sub-Milestones/Tactics]@row)) > 0, COUNT(ANCESTORS([Milestones/Sub-Milestones/Tactics]@row)))

...and it works but seems to be skipping rows even after I converted it to a column formula. Am I missing something? My plan was to use the Tactic field in a report filtered based on the value but the highlighted cells are not applying the formula.

Ultimately, I want to include the milestone/sub-milestone/tactic as a count if it is the third level of indention or greater. Any insight would be greatly appreciated!

Paul Newcome

@Chris Hallo This formula:

=IF(COUNT(CHILDREN([Milestones/Sub-Milestones/Tactics]@row)) > 0, COUNT(ANCESTORS([Milestones/Sub-Milestones/Tactics]@row)))

Is written to be applied to only parent rows (Note the bold portion saying if the count of children is greater than zero). If you want it on every single row, you will need to remove that portion which will leave you with...

=COUNT(ANCESTORS([Milestones/Sub-Milestones/Tactics]@row))

David_Lewis

This seems to be a question best solved with helper columns!

On your sheet, create two helper columns. Column 1 will contain your indentation characters, scaled for however many levels of children. Column 2 will have the Combined indentation characters and Original Task name.

"Column 1" formula: =IF(COUNT(ANCESTORS([Primary Column]@row)) = 0, "", "__" + PARENT([Column 1]@row))

"Column 2" formula: =Indents@row + [Primary Column]@row

Pull "Column 2" into your report and hide your Primary Column. Voila! Indents on your report.

Feel free to substitute your own column names or preferred indentation characters as necessary.

NOTE: Report indents can lead to confusion if your report filters exclude any parent rows!

Nasir@EBC

Thanks guys for the support!

However, the issue that I am facing is a bit different: In the report view, the child rows are not even appearing right under their parent row! So in this case I cannot even figure out which child row belongs to which parent row!

Any suggestions please?

Paul Newcome

@Nasir@EBC You would need to use a similar helper column but instead of pulling in the ancestor count you would pull in the parent data. From there you can sort your report based on this helper column.