How to include parent rows on a report only if their child tasks are in progress?
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.
Answers
-
A way to determine if a parent has an overdue child task that is In Progress would be a formula like this:
=IF(COUNTIF(CHILDREN(Finish@row), <TODAY(30)) * COUNTIF(CHILDREN(Status@row), OR(@cell = "In Progress", @cell = "Not Started")) > 0, 1, 0)
I multiply the following two in the formula to get the AND result.
- Number of Children overdue or coming due in 30 days
- Number of Children In Progress or Not Started
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 210 Industry Talk
- 441 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 300 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!