Tasks on At Risk and Milestones Reports

MichB

Hello! When using reports such as At Risk and Milestones, the appropriate Tasks surface however not their corresponding Parent Phase/Category. So for the projects I work on, it is not helpful to see Tasks without that Parent. Does anyone have a method or workaround to make the Parent surface on the report with the Tasks? Thank you!

Paul Newcome

I use a checkbox column that has a formula that mimics the report criteria to check the child row boxes and then in the parent rows the box is checked if there is at least one child row checked.

Then I pull my report based off of the checkbox column.

MichB

@Paul Newcome Amazing! That is so helpful. To make sure I follow, you have the checkbox column on the tracker sheet? Do you mind sharing how the formula reads so I can replicate it? Or if there is an article that explains this that you can point me to that would be great as well. I'm still a formula novice. :-) Many thanks.

Paul Newcome

It would end up being an IF statement with possibly some OR/AND functions tucked in. The structure of the formula is going to depend on your exact needs.

Basically what you can do is spell out your report criteria and then write an IF statement to capture all of that. If you want to spell it out here, I'd be happy to help you put something together.

MichB

Hi Paul,

I spent some time this week reading up on formula documentation and watching a few videos. I am still challenged to set up the formula based on my specific use case here. I assembled the attached slide to visualize the problem I'm trying to solve. Let me know if this is enough information for you to guide me further on the formula. A million thanks!

Michele

Screen Shot 2021-04-14 at 1.56.00 PM.png

Paul Newcome

What is your actual report criteria?

MichB

The only criteria I have is At Risk column = checked. I basically took the At Risk report template that Smartsheet provides and just adjusted it to feed in from my sheet/tracker rather than the one in the template set. Thank you!

Paul Newcome

Ok. Are you using a formula to flag "At Risk" rows or is that a manual process?

MichB

Hi Paul, sorry I went quiet, got caught up in other priorities the last two weeks! The At Risk flag is manual. I am open to changing my approach to use a formula to drive that flag if it will help accomplish what I'm trying to do. Thank you.

Paul Newcome

You could automate the entire column, but at the very least you are going to want to automate the parent rows. We would use an IF statement to say that if the count of child rows that are flagged is greater than zero, then flag the parent row.

=IF(COUNTIFS(CHILDREN(), 1) > 0, 1)

Now that the parent rows are automatically flagged as soon as one of their child rows is flagged, it should pull into your report.