Task Report pulling ONLY the next Milestone to be completed

[Laramie_Newborn]

I am trying to create a task report that ONLY pulls in the next milestone to be completed on a project. I have this report very close to where I need it but, it is currently pulling in every task with a milestone associated with it that's not complete - I only need the next milestone associate task that is incomplete. 

The milestone view does not work for the desired finished report, a milestone report doesn't work due to filtering constraints, and our milestones are not all associated with the same task names across our projects. 

Ultimately, what we're trying to achieve is a report that shows how many projects we have in each program that are currently in each milestone stage (ie, 3 projects currently in milestone 2, 6 projects currently in milestone 3, etc). 

[skyehansen]

I think the way I prefer the MOST to configure this type of thing is to have the milestones linked to each other (or to other tasks) with predecessors. This way, I know that if I'm interested in "which milestone is 'active'" what I really mean in "Workfront language" is, "which milestone is ready to start and not complete"

You don't mention much about how your project timelines are configured with respect to milestones, so I'm not sure if this kind of thing would work for you or not.

[RowvillBh1]

@Laramie_Newborn If your tasks have predecessors as rightly mentioned by @skyehansen it will make your job a lot easier. Because Workfront will know what tasks can get started and what tasks cannot. It stores this value in a field named 'Can Start'. Once you have your predecessors in place you can create a project report and add a text mode column (see code below) that references the collection of tasks but filters out only those that have a milestone, are not completed and Can Start.

displayname=Active Milestone
listdelimiter=<p>
listmethod=nested(tasks).lists
type=iterate
valueexpression=IF({status}!="CPL"&&!ISBLANK({milestoneID})&&{canStart}=true,{milestone}.{name},"")
valueformat=HTML

[Laramie_Newborn]

Unfortunately, there's little consistency among where the milestones are placed among our projects as users often manually enter their milestones based on what makes sense for the specific project. Because of this, the predecessors are also inconsistent (the milestone task that is the next up is often not "can start" because that task still has pending predecessors, even though the previous milestone is complete). 

Alternatively, maybe there is a better option to pull the last completed milestone related task? That would be an acceptable workaround since it would inherently indicate what milestone is next.  

[RowvillBh1]

I see. You could probably adjust this formula to take completed tasks instead of incomplete ones - In the code change this {status}!="CPL" to this {status}="CPL". However, this will show all the milestones completed till now. Will that work out for you?

Apart than that I can only think of a Fusion automation that puts the active milestone on a project custom form.

[skyehansen]

this (and reporting in general) is really about figuring out WHAT the consistency is to build the report out. A large part of a system admin's job is spotting the consistency, as well as governing to ensure more consistency, or configuring for consistency on a global scale.

For example in my previous suggestion, you can interpret what I was asking as -- "what is the "next" milestone; is the consistency that the next milestone is the one that Can Start?".

When you say, "no, but how about if we pull in the "last completed" milestone task?", the same consistency question needs to be asked. If there's not a way to consistently identify what a last completed task is, then you're left with your original result, because the only consistent things you can point to are "it has a milestone id, and it's complete" or "milestone ID and not complete". 

With that in mind, are there any other consistencies you can share with us, or any way you could govern or configure to get more consistency? e.g. In your last response, it kind of feels like you are hinting that the milestones are always in order. So is your consistency that "Milestone1 is always followed by Milestone2 which is then followed by Milestone3," as long as we have those 3?