Portfolio % complete report of 10 Workstreams, ~60 projects, hundreds of tasks

philcvk

Hello community!

I have a programme with the following structure:

• 1 Programme with about 10 Workstreams
• Each Workstream has about 6 Projects, each workstream is a Sheet and has a column called 'Project' ... the Primary Column is the Task Name, a mix of Tasks, Sub Tasks (using indents) and Milestones. There's a % Complete column that the project leads keep updated.

We have just moved to Smartsheets so this is the structure inherited.

I want to analyse the % complete at the portfolio and project level. I set up a Report that pulls all the tasks and columns in from the 10 Workstream sheets.

The step I don't know how to do is to filter out the 'sub-tasks' , then I will be left with the Tasks % complete in the report view.

Am very keen to hear of different approaches and solutions here. We can't purchase Control Centre unfortunately. I can add calculated fields or move the data around, I have some flexibility to get to this analysis and view.

Thanks!

jmyzk_cloudsmart_jp

Hi @philcvk

You can use helper columns with formulas like these to find out the hierarchical position of the row.

• Is Parent (checkbox) =IF(COUNT(CHILDREN([Task Name]@row))>0,1,0) : to determine if the row is parent
• ANC (number) = COUNT(ANCESTORS()) : to determine the level of hierarchy 0: top, 1: second, etc.

Then, use those as filters in your reports.

https://app.smartsheet.com/b/publish?EQBCT=45502f9158294e76bd9eec792d902b5b