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

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.