Need help to prepare a report which shows the In Progress/On Hold tasks, along with the Parent task
![Sonnie](https://us.v-cdn.net/6031209/uploads/defaultavatar/nWRMFRX6I99I6.jpg)
I am trying to create reports which will show tasks in various status - Not Started, In Progress, On Hold, Complete.
But when I run the report, it is fetching the actual tasks only, but I need to capture the parent task also.
I find no issue with In Progress tasks, because when I filter for the In Progress tasks in the report, it will include the Parent task also which will also be in In Progress status.
I have managed to capture the parent task name by adding a Parent column in the project sheet and then grouping by the Parent name in the report. This seems ok, but for parent task, it is not capturing start/end date and other details. Also, Gantt chart is not showing up for the parent task.
Could any please suggest a solution though which I can capture all the details of the parent task along with the child task in the report?
Below is the project tracking sheet I have prepared. (test data)
The below shows where I have managed to capture the Parent task name in the report for On Hold tasks. but it is not capturing the Start date/end date or gantt chart for the parent task.
Below is the filter in the report.
Best Answer
-
Hey @Sonnie,
To include the parent rows where at least one child row is In Progress you want to add a filter condition as:
In Progress // is greater than // 0
This would be in a separate filter group from your existing criteria (but you build these together). What you currently have is a single filter group with two filter conditions.
So you would go here, click + Add a Condition and add this
In Progress // is greater than // 0
You can then drag it into a separate group and use an OR (the operator on the right hand side with the brackets) around these two groups.
It would end up looking more or less like this but with your columns and conditions
School of Sheets (Smartsheet Partner)
If my answer helped please accept and react w/💡Insightful, ⬆️ Vote Up, ❤️Awesome!
Answers
-
Hello @Sonnie!
If you add a condition group to your filter criteria for [Children] [is greater than] [0] than it will force the parents to appear. This should have an OR relationship with your existing criteria (below).
Keep in mind, this will show Parent rows even if none of the child rows are in progress. So, if you only want to show parent rows where there is 1+ child row in progress you would need to add a column to your Sheet and use a formula like:
IF(Children@row > 0, COUNTIF(CHILDREN(Status@row), "In Progress")
Then reference this in your report builder for any value greater than 0.
Hope this helps!
School of Sheets (Smartsheet Partner)
If my answer helped please accept and react w/💡Insightful, ⬆️ Vote Up, ❤️Awesome!
-
School of Sheets (Smartsheet Partner)
If my answer helped please accept and react w/💡Insightful, ⬆️ Vote Up, ❤️Awesome!
-
Thanks Dan. This looks great.
I have added new columns with the formula you suggested. (screen shot below)
But not sure how to apply this to the report - Sorry my logic brain is not working that much. 😀
Can you please advise what filters to apply in the report? (Of course, I will include the new column >0)
Do we need filter grouping?
Thanks in advance.
-
Hey @Sonnie,
To include the parent rows where at least one child row is In Progress you want to add a filter condition as:
In Progress // is greater than // 0
This would be in a separate filter group from your existing criteria (but you build these together). What you currently have is a single filter group with two filter conditions.
So you would go here, click + Add a Condition and add this
In Progress // is greater than // 0
You can then drag it into a separate group and use an OR (the operator on the right hand side with the brackets) around these two groups.
It would end up looking more or less like this but with your columns and conditions
School of Sheets (Smartsheet Partner)
If my answer helped please accept and react w/💡Insightful, ⬆️ Vote Up, ❤️Awesome!
-
Wow! it worked. - I pulled for the On Hold
Thanks, Dan, for the detailed explanations.
I had seen the filter grouping option, but never tried it / didn't know how to use it
Thanks again, Dan.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!