Need help to prepare a report which shows the In Progress/On Hold tasks, along with the Parent task

Sonnie

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.

SoS | Dan Palenchar

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

SoS | Dan Palenchar

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!

SoS | Dan Palenchar

Sonnie

https://community.smartsheet.com/discussion/comment/444465#Comment_444465

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.

SoS | Dan Palenchar

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

Sonnie

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.