How to create a Matrix report for milestones

[MarkSa18]

I currently have a report that lists Milestones under each corresponding Project. I would like to recreate this report only in a horizontal layout (matrix?) where column heading is the milestone name and each row is a different project name.

Any suggestions?

[skyehansen]

You could do this a number of ways. Based exactly on your question, you would set up a task report, filter on milestoneID is not blank, and switch over to a matrix grouping where the project name is to the left and the milestone name is above.

I prefer and recommend a project report using the milestone view.

You can also set up milestone columns based on text mode like the below:

https://experienceleaguecommunities.adobe.com/t5/workfront-questions/displaying-a-specific-milestone...

[KellieGardner]

The only way to do this is to run a project report and use text mode to get the milestones of tasks.

Example:

Text mode:

displayname=*Name of your Column*
listdelimiter=<div>
listmethod=nested(tasks).lists
type=iterate
valueexpression=IF({milestone}.{ID}="**ID of your milestone**",CONCAT({plannedCompletionDate},": ",{percentComplete},"%"))
valueformat=HTML

(mine pulls in the planned completion date and % complete but you could pull in any task data you wanted)

[MarkSa18]

Thank you @KellieGardner I am testing that next. I appreciate your help.

Best,

Mark

[MarkSa18]

Hello @KellieGardner 

Thank you...I see your output is for one milestone....I would require multiple milestones {plannedcompletiondate] (columns) tied to Project #A (row1). And then the next row (row 2) would be Project #B with same milestones [plannedcompletiondate} etc...

This layout is way matrix is recommened but I have not had any success with displaying multiple projects

Any help feedback is appreciated

[MarkSa18]

NM...I got it to work Thank You.

I am just not sure if it makes sense to build this for 63 milestone (columns)

But this solution works at least if it was only for a few milestones

Again, thank you

[KellieGardner]

Oof, that's a lot of milestones. Have you tried using the milestone view of a project report to see if it can get you what you need natively?

[Erika_Antkowiak]

Hi @MarkSa18! We created the exact report you are describing and I'm happy to share how we built it. Matrix reports can be a game changer so you'll have to let me know how this goes for you. 

1. Create a new task level matrix report.
2. Select your columns. We used the following:
   • Project Name,
   • Task Name,
   • Task Assignments,
   • Duration,
   • Planned Hours,
   • Actual Hours, 
   • Planned Start Date,
   • Planned Completion Date,
   • % Complete
3. Select your groupings. In matrix reports, you have row grouping options and column grouping options. We used the following:
   • Row
     • Assigned to > Name (You don't have to include this on your report if it isn't important to you)
     • Project > Name
   • Column
     • Milestone > Name
4. Add your filters. We used the following: 
   • Task > Assigned to ID = Is Not Blank
   • Task > Milestone ID = [Include individual Milestones]
5. Save the matrix report to see the projects displayed on the left (top to bottom) and the milestones across the top (left to right).

Selections can be adjusted based on what you want to see. Ours is used to compare planned and actual hours per project milestone. Give it a try and let me know how it goes. I'm happy to answer any questions you have. I hope to hear from you shortly! Good luck!

Best,

E

[MarkSa18]

Thank you @Erika_Antkowiak !

I am trying to display the planned completion date of the task under the column heading of the associated Milestone Name.

I set up a rough version of your report only when I add second project it does not provide a value on the field.

Any suggestions>

Thank you

Mark

[ljorr16]

Hi! Did you have any luck? I'm trying to do the exact same thing you are. I'd love to see project name, task name, assignee, milestone name, planned completion and actual completion. Seems we are limited on the number of columns we can have?

[MarkSa18 · Accepted solution]

Hi! I am still working on a report that displays milestone data in a linear way...I haven't pushed the column max yet although I may since I would like to see upwards 60 milestones. As you know it's tedious coding each column with milestone name etc...I plan to keep chipping away at solutions until I find something -the matrix view of Milestones is where I would like to land only you can't export in that view.

[ljorr16]

Thank you!

[Erika_Antkowiak]

Hi @MarkSa18! I apologize for the delay. I read your message above. Are you setting it up so the project is listed first on the grouping and then the planned completion date of the task? Below, I've provided a screenshot on how it can be done. Let me know if this helps or if you are looking for something additional.

[ljorr16]

Hi! Above is a sample of how my attempt turns out. Would love to get the planned completion date under the milestone name not a random count.....thoughts on where I may have gone wrong?

[Erika_Antkowiak]

Hi @ljorr16! I believe I get what you want. Instead of 0, 2, etc. under your milestones you want the planned completion date for each. I'm looking into this now. I'll loop back with you when I have an update.

[ljorr16]

Yes, exactly!! Thank you for your help! I'm trying to get everyone into WF and OUT of Excel spreadsheets, but that's hard if I can't report in a way the team needs to see it.