Transposing Tasks in a Report (summarizing common tasks)

[Michael Gwin]

I have a program plan where I list multiple projects that all have the same
subtasks (milestones for those projects). I want to be able to list the
individual tasks out in a report, per project, instead of projects, per task.

For Example...

Program
Project1
Milestone1
Milestone2
Milestone3
Project2
Milestone1
Milestone2
Milestone3
etc

Id like to see a report showing the following:

Milestone1
Project1
Project2
Milestone2
Project1
Project2

If its not easy, Im thinking I will redo the plan but I have several hours
into it already and would rather not. I know I can filter by the Milestone
but then the output looks like:

Project1
Milestone1
Project 2
Milestone1
Project 3
Milestone1

Not ideal but I suppose functional if thats the way I have to do it. Im
trying to get a summary vew that I can put on a scorecard for my leadership.
Id like it to be as crisp as possible.

Thanks for the help.
Mike

 

[John]

I have a program plan where I list multiple projects that all have the same
subtasks (milestones for those projects). I want to be able to list the
individual tasks out in a report, per project, instead of projects, per task.

For Example...

Program
Project1
Milestone1
Milestone2
Milestone3
Project2
Milestone1
Milestone2
Milestone3
etc

Id like to see a report showing the following:

Milestone1
Project1
Project2
Milestone2
Project1
Project2

If its not easy, Im thinking I will redo the plan but I have several hours
into it already and would rather not. I know I can filter by the Milestone
but then the output looks like:

Project1
Milestone1
Project 2
Milestone1
Project 3
Milestone1

Not ideal but I suppose functional if thats the way I have to do it. Im
trying to get a summary vew that I can put on a scorecard for my leadership.
Id like it to be as crisp as possible.

Thanks for the help.
Mike

Mike,
This is about as close as I can come to the format you want.
1. Use a spare text field (e.g. Text1) and fill it with the summary line
name for the summary line itself and all subtasks under it
2. Customize a second spare text field (e.g. Text2) with the trivial
formula Text2=[Name]. Note: do NOT extend the formula to summary lines
3. Go to Project/Group By/Customize Group and set the criteria to first
group by Text2 and then Text1.
4. Hit "OK"
5. Finally, collapse each subgroup so only the Text2 value appears.

If you really want it crisp, export the data to Excel and format it
there. A VBA macro can be used to automate this process.

Hope this helps.
John
Project MVP