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.