Reporting on which Milestone a project is currently in

Hi Narayan!

Going a bit back in time here...but can I use this text method on a Request-based report? Maybe there is something wrong with ability to use listmethod across objects?

displayname=Active Milestone

listdelimiter=‍

listmethod=nested(tasks).lists

textmode=true

type=iterate

valueexpression=TRIM(IF(ISBLANK({resolveProject}.{milestoneID})=0,IF(ISBLANK({actualCompletionDate}),IF({canStart}=1,CONCAT({resolveProject}.{milestone}.{name},"; "),""))))

valueformat=HTML

Thanks Melinda and tell Marty I said HI! Thank you to everyone who contributed.

For fun I tried both and there were small differences. However, I think it would be a big difference if the phrases in the list were shorter. My milestone have multiple words. Here is a side by side of the 2 methods. &zwj; vs. <p>.

Narayan Raum just discussed a new trick <p> instead of <br> creates one line break even if a bunch are in a row. So puts the entry on a new line but not a hundred new lines.‚

Actually, I think the reason for mine being blank is much simpler. User error! I used Notepad to compile most of my formula (minus listdelimiter), waiting until I popped it in the report to look up the proper delimiter on my cheat sheet. Unfortunately, I copied and pasted from the incomplete one when I posted to the group (doh!).

Sorry for the confusion! And thank you, Doug, for providing the proper info!

Doug,

Thank you. The article was so helpful and its good to know to be mindful when cut & pasting code into/from the forums.

So when I updated the listdelimiter=<BR>to enter a line break, it gave me way too much space. (See screen shot attached) So thought maybe I would use TRIM. But couldn't figure it out so I tried &zwj; which worked really well.

See the Article Doug mentioned previous in the thread, which states under listdelimiter: the &zwj; = zero-width joiner. The values of the collection have no separation between them.

Here is the Code. Also attached as a screen shot.

displayname=Active Milestone

listdelimiter=&zwj;

listmethod=nested(tasks).lists

textmode=true

type=iterate

valueexpression=TRIM(IF(ISBLANK({milestoneID})=0,IF(ISBLANK({actualCompletionDate}),IF({canStart}=1,CONCAT({milestone}.{name},"; "),""))))

valueformat=HTML

This is great for now but I'm going to continue to work on this because it seems to be in a random order or at least not the one I want and it list all the milestone that are not complete. I'm looking to try and get only the current milestone....so stay tuned.

Hi Tracey,

When Kathy pasted in her text mode, I suspect (as often happens to me) the community forum converted her <br> listdelimeter along the way; and if you then copied and pasted it verbatim, your version would have left the listdelimeter empty, which Workfront then treats as if it was a comma...hence your commas.

The definitive list (excluding Narian's -- I'm sure there's a story) is at https://one.workfront.com/s/article/Referencing-Collections-in-a-Report-779518987 and the juicy part is below, for quick reference.

Regards,

Doug

-------------------------------------------------

delimiterlist=<br>

This is the delimiter which will be used to separate the values in your list.

We recommend to use <br> which adds a line break between the values.

You can also use the following:

&zwj; = zero-width joiner. The values of the collection have no separation between them.

, =comma separator. The values of the collection are separated by a comma followed by no space.

/ = slash separator. The values of the collection are separated by a slash.

- = dash separator. The values of the collection are separated by a dash.

Leaving this line empty will add a comma followed by a space between the values of the collection, by default.

Looks awesome! Will you share your source?

Try using this as your listdelimiter:

listdelimiter=&#8203;

Thank you for your help. I started with Kathy's formula and it came out like this (see attachment) any suggestions how to refine? Its a bit hard to read and milestone order is off.

Thanks again for your time and help on this.

What if you put a calculated field (DE:Current Milestone?) on each milestone task with a function that looks something like the following:

If milestone ID is not blank, (is this a milestone task?)

If actual start date is not blank, (Has this task started yet?)

If actual completion date is blank, (Is this task still active?)

"Yes" (tag this task as being the current milestone task)

Then, using Kathy's idea, you can use the collections report to look for a task where "DE:Current Milestone" = Yes. I haven't had any time to test this, but don't see why it wouldn't work out. If you do get the calculation and collection column sorted out, will you post the code back to this thread?

There are ways to do this, but the result will depend rather substantially on how your projects are structured. You may end up with more than one active milestone based on your dependencies and where your milestones are applied.

If you want to show one line per project, create a project report and add the following text mode field:

displayname=Active Milestone

listdelimiter=

listmethod=nested(tasks).lists

textmode=true

type=iterate

valueexpression=IF(ISBLANK({milestoneID})=0,IF(ISBLANK({actualCompletionDate}),IF({canStart}=1,CONCAT({milestone}.{name},"; "),"")))

valueformat=HTML

This shows you any milestone name that has not been completed and is able to start. Based on dependencies, if you will never have more than one milestone active at a given time, you could remove the "CONCAT" function from the above, as it serves only to give you a separator between milestone names.

If you want to create this as a task report instead, you would use a filter similar to what is shown in the formula above (milestoneID is not blank, actualCompletionDate is blank, and canStart is true).

Kathy