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.
Thanks!
Answers
-
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.
