How to include parent rows on a report only if their child tasks are in progress?

jordangallant · 12/21/23

I created a report to look across 4 project plans to show tasks that are overdue and tasks coming due within the next 30 days. It was requested to add parent rows to the report to be able to show the tasks in context. Since there isn't an out of the box way to add parents tasks to the report, I use a helper column identifying a task as a parent (=IF(COUNT(CHILDREN([Task Name]@row)) > 0, ".", "") and then use report filtering to show parent tasks in addition to those overdue and coming due in 30 days.

The problem is a lot of parent tasks have different timelines than individual tasks and I only want the parent tasks to show if their child task is overdue or coming due in 30 days. Any ideas on how to make this work? Ideally I was thinking it would be a column indicating if it had child tasks with a finish date in the next 30 days or overdue and then include that column in the second filtering group of the report.