Hours Report Grouped by Milestone

[NickVa1]

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

[KIMBERLYREA]

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. 

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

[NickVa1]

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

[KIMBERLYREA]

Gotcha. Yeah that's hard. I wish you luck. 

[KellieGWAB]

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. 

[NickVa1]

Hi Kellie - can you share your calculated field formula?

[Doug_Den_Hoed__AtAppStore]

Hi Nick,

I suspect @KellieGWAB 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

[NickVa1]

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.

[Doug_Den_Hoed__AtAppStore · Accepted solution]

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? 

[KellieGWAB]

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})

[NickVa1]

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!