Expand my Community achievements bar.

The next phase for Workfront Community ideas is coming soon. Learn all about it in our blog!
SOLVED

Hours Report Grouped by Milestone

Avatar

Level 10

Hi WF Community,

 

Does anyone know if it's possible to have an hours report that is grouped by task milestone?


The idea would be that users can see the actual hour entries (as rows) and an aggregate/sum of those hours rolled up to the grouping level (which is the task Milestone Name).

 

Thanks,

Nick

1 Accepted Solution

Avatar

Correct answer by
Level 10

 

Gotcha @NickVa1,

 

In that case, provided you have a custom form on every such parent and child task (heck, you use Templates and have Fusion, so just do them all) with the following formula, as shown below.

 

IF(!ISBLANK({milestoneID}),{milestone}.{name},IF(!ISBLANK({parentID}),IF(!ISBLANK({parent}.{DE:Milestone Branch}),{parent}.{DE:Milestone Branch}),""))

 

Regards,

Doug

 

P.S. Excellent challenge! Shall we celebrate over an extra large Americano with cream at Vendome sometime and call it square? 

 

Doug_Den_Hoed__AtAppStore_0-1710366581731.png

Doug_Den_Hoed__AtAppStore_1-1710366838866.png

 

View solution in original post

10 Replies

Avatar

Level 9

I have Milestone reports. It's a task report and filtered by milestones. Those are parent tasks in our timing plans so they don't have children tasks associated to them.  These are my filters: 

 

Task -> Milestone ID = (list out the milestones you want included)

Project -> Milestone Path = 

Project -> Group ID = 

Project -> Status = 

Task -> status = Complete 

Project -> Planned start date = (date range)

I then grouped by:
Project custom data field

Task planned start date

Milestone Name

This is my view along with some custom data fields. 

KIMBERLYREA_0-1710344370156.png

We look more at average duration of the milestones rather than the hours but this is our chart based off it. 

KIMBERLYREA_1-1710344579751.png

 

 

Avatar

Level 10

Thanks for sharing, Kimberlyrea!
Unfortunately, this won't work as I need to see hour entries and aggregate them by hour entry date (month).

Avatar

Community Advisor

Hi Nick - I have a reporting custom form on our tasks that has a calculated field on it to capture what milestone that task is tied to so that I can report on this from an hour report. I would suggest using a calculated field. 




Avatar

Level 10

Hi Kellie - can you share your calculated field formula?

 

Hi Nick,

 

I suspect @KellieGardner is suggesting a simple calc on the Task that points to its {milestone}.{name} so that it is the "close enough" for the Hours report to then get at it, without having too many hops (although I'd've thought {task}.{milestone}.{name} would also have worked), along the same principal as described in this How's My Portfolio post.

 

Regards,

Doug

Thanks, Doug.
I thought the same, but the only place this new field populates the Milestone Name is on the Milestone Task.  It doesn't populate the Milestone Name in this new custom field for all child tasks below it, which is what I need, which is what I need in order to group hour entries (on an hours report) by Milestone Name.

Avatar

Correct answer by
Level 10

 

Gotcha @NickVa1,

 

In that case, provided you have a custom form on every such parent and child task (heck, you use Templates and have Fusion, so just do them all) with the following formula, as shown below.

 

IF(!ISBLANK({milestoneID}),{milestone}.{name},IF(!ISBLANK({parentID}),IF(!ISBLANK({parent}.{DE:Milestone Branch}),{parent}.{DE:Milestone Branch}),""))

 

Regards,

Doug

 

P.S. Excellent challenge! Shall we celebrate over an extra large Americano with cream at Vendome sometime and call it square? 

 

Doug_Den_Hoed__AtAppStore_0-1710366581731.png

Doug_Den_Hoed__AtAppStore_1-1710366838866.png

 

Avatar

Community Advisor

Since the milestones don't get inherited down to sub tasks, my code basically goes and looks at the task and any parent tasks to find the milestone it's tied to. 

 

Might not be the cleanest code, I haven't looked at it since I've learned more advanced text mode. but here ya go

 

CONCAT({milestone}," ",
{parent}.{milestone}," ",
{parent}.{parent}.{milestone}," ",
{parent}.{parent}.{parent}.{milestone}," ",
{parent}.{parent}.{parent}.{parent}.{milestone}," ",
{parent}.{parent}.{parent}.{parent}.{parent}.{milestone}," ",
{parent}.{parent}.{parent}.{parent}.{parent}.{parent}.{milestone}," ",
{parent}.{parent}.{parent}.{parent}.{parent}.{parent}.{parent}.{milestone}," ",
{parent}.{parent}.{parent}.{parent}.{parent}.{parent}.{parent}.{parent}.{milestone})



Avatar

Level 10

Thanks Kellie and Doug!  Both of those work.

Appreciate it.

Doug, yes, let's grab an Americano at Vendom!  That's the one in Sunnyside, yes?  I'll message you privately with my number.  Cheers!