Report filter on Parent Data, but also return the Children for each returned Parent row

Dennis M Wierzbicki
Dennis M Wierzbicki ✭✭✭✭✭
edited 05/24/22 in Smartsheet Basics

I have a series of 100+ project plans. In each project plan, there is a column titled "Assigned To". For the highest Parent in the project, which for us is "WBS Level 1", the "Assigned To" person is the project manager. For the children rows (lower hierarchy WBS levels), the Assigned To could be anyone on the team.

There is no Project Manager field/column in any of the project plans (if only there were a way to embed this in a sheet's Metadata).

I have assembled a report for the 100+ project plans the contains a filter that allows me to return the first row of all projects for a given Project Manager. Easy to do with two entries in a filter, "WBS Level"=1, and "Assigned To" is one of a pick list of entries. So I can now see all projects where John Smith is the Project Manager.

Where I'm going off the rails is trying to also return ALL Children rows of the projects returned above, i.e., child rows for each of John Smith's projects that result from "WBS Level" =1 and "Assigned To" = John Smith.

I can return the above items, and ALL children, of ALL projects, but not just the children of the list generated by the first part of the filter.

There has to be a way to do this, no?