Creating a program report with project in each phase

[jordanbray]

Hello

I'm trying to run a report that has the program and then the projects listed with the start and end date in each milestone phase.  For a visual cue:

 Is this report even possible or is it possible to create one like it?

Any help would be appreciated.

[Madalyn_Destafney]

Hi, my first thought is a matrix report, but I don't think you'll be able to get it to display exactly as you need. You can start with a project report filtered by the program(s) you need, and go to groupings to create the matrix tab. Where I think you'll get stuck is having the start and end side by side like this. Check out more on matrix reports here: https://experienceleague.adobe.com/docs/workfront/using/reporting/reports/create-manage-reports/crea... 
When you say 'milestone' are you using actual WF milestones? If so, have you considered milestone or milestone path reports?

[NicholeVargas · Accepted solution]

@jordanbray You can - it would just require a handful of custom text mode collection columns on a project report. If you add the following text mode column to your project report, this will show you all the names of the milestones within that project (basically the details of the milestone path):

displayname=Milestone Names
listdelimiter=<p>
listmethod=nested(milestonePath.milestones).lists
textmode=true
type=iterate
valuefield=name
valueformat=HTML

The reason you want to add this column in first, is that you'll need the names of all the milestones to create custom columns for the start and end dates.

To get the planned start date of Milestone 1 (you'll want to swap the text in bold for the name of your actual milestone), use the following text mode. If you prefer actual start date, be sure to swap that as well. 

displayname=Milestone 1
listdelimiter=<p>
listmethod=nested(tasks).lists
textmode=true
type=iterate
valueexpression=IF({milestone}.{name}="Milestone 1",{plannedStartDate})
valueformat=HTML

To get the planned completion date for Milestone 1, here is the text mode for that column: 

displayname=Milestone 1
listdelimiter=<p>
listmethod=nested(tasks).lists
textmode=true
type=iterate
valueexpression=IF({milestone}.{name}="Milestone 1",{plannedCompletionDate})
valueformat=HTML

You will need to create 2 columns for each milestone, so this can be a little time consuming, but should pull in the relevant data you need. Let me know if you have any questions! 

It would look something like this at the end:

 

[jordanbray]

Thank you for your help!  Now that I've played around with the system more and understand it better, I'm impressed you knew what I was talking about given how vague I was!  Thank you again.

[NicholeVargas]

Happy to help! The screenshot gave me a great visual of what you were hoping to accomplish! 

[ljorr16]

Hi Nicole! What if I wanted to do the same as above, BUT show planned completion date if not completed, then actual completion date when completed? 

Does that make sense?

I'm stuck here:

IF(ISBLANK({milestoneID})=0,IF(ISBLANK({actualCompletionDate}),IF({status}="INP",{plannedCompletionDate},{actualCompletionDate})))

Bonus points if we can conditionally format this to show up as red if projected and/or actual completion date is greater than planned completion date (i.e. it's late)

thank you text mode guru!

[NicholeVargas]

@ljorr16 it would look something like this, although you would want to replace Milestone 1 with the name of your milestone in the valueexpression line. 

This will show either the planned completion date if the task is not complete or the actual completion date if it's done. If you can't tell the difference between the dates, you could always add text before/after the date so it's clear. 

displayname=Milestone 1
listdelimiter=<p>
listmethod=nested(tasks).lists
type=iterate
valueexpression=IF({milestone}.{name}="Milestone 1",IF(ISBLANK({actualCompletionDate}),{plannedCompletionDate},{actualCompletionDate}))
valueformat=HTML

I'm not great with text mode conditional formatting (red highlighting), so I can try and play around with it - it might just take me longer so figured I'd share this first! 

[ljorr16]

Nichole, You are just the best!! Thank you so much for this. I truly appreciate you!

Glad I'm not the only one who finds text mode conditional formatting a challenge, I'm in good company I see!