Task report splitting out planned hours by assignee

[NickVa1]

Hi WF Community,

Does anyone know if it's possible to split out a task report by user and planned hours, or would an assignment or work item report be the only options for that?

Example:

- 20 planned hours on Task A

- Pardeep is assigned 5 planned hours

- Keleel is assigned 15 planned hours

We would like to see the task report grouped by task, and then see the breakout of planned hours by assignee under the task.

I know this can be handled in an assignment report, but curious if a task report can do this as well?
Thanks,
Nick

[skyehansen · Accepted solution]

hi Nick, I'm not sure if you're familiar with collections in reporting, but if you are, your question is an example of where they would be used. 

https://experienceleague.adobe.com/docs/workfront/using/reporting/reports/text-mode/reference-collec...

You're specifically looking for the assignments collection and then Planned Hours is known as workRequired in the system. A quick search focusing on collections, assignments and workrequired would definitely net you with some good samples to start experimenting with.

https://experienceleaguecommunities.adobe.com/t5/custom/page/page-id/GlobalSearch#q=collections%20re...

Here's a very specific answer with that in mind: https://experienceleaguecommunities.adobe.com/t5/workfront-questions/text-code-for-assignement-break...

[NickVa1]

Thanks for nudging me in that direction, Skye.  A collection could work, I'll see if it suffices based on requirements.  I was able to list assignees and their planned hours in a custom column.

[MSuleiman]

Hi @NickVa1 were you able to find a solution? Keen to know as I'm trying to build a report to compare planned hours vs actual hours by job role/user. So far it seems I need to use an assignment report but this comes with some issues so was wondering if I could use a task report as well. 

Thanks

Mo

[NickVa1]

Hi Mo,
We're still determining which option works best for us, based on data points (fields) we want to include in the report that live on various objects.  There are limitations with each type of report (tasks, assignments, hours), depending on what data you want to see, and how you want it displayed.

• An assignments report is nice if you just want to see assignment related data broken out by assignee (assuming you have multiple assignees assigned to a task).  Planned hours is easy to see by assignee.
• If you want to see planned hours broken out by assignee on a task report, I had to create a custom calculated column, with the following text mode:
  • displayname=Planned HRS by Assignee
    listdelimiter=<p>
    listmethod=nested(assignments).lists
    textmode=true
    type=iterate
    valueexpression=CONCAT({assignedTo}.{name}," (",ROUND({workRequired}/60,2)," HRS",") ")
    valueformat=HTML
  • This is what the end result looks like:
    • 
• An hours report shows too much duplicated data points if you're not aggregating/grouping data.

Hope that helps.

[MSuleiman]

Hi Nick,

Thanks for sharing that view and text mode. Being able to break down planned hours by assignee on a task report it's definitely useful. Could you please also share the code for 'actual/planned hours' and 'remaining planned hours' column assuming it was both done using text mode?

Have you also looked into showing planned vs actual hours by job role? I have noticed with a task report, to get the variance or percentage of burn by job role accurately, you will need to have separate tasks for each user/job role (due to primary task owner). Not sure if there is a way to show this accurately on a report without separating task for each job role for tasks that currently have multiple assignments. 

Thanks

Mo

[NickVa1]

Hey Mo!

Here's the text mode for the "Actual/ Planned Hours" calculated custom column:

displayname=Actual/ Planned Hours
linkedname=direct
namekey=actualworkrequired
querysort=actualWork
textmode=true
valueexpression=CONCAT({actualWorkRequired}/60,' / ',{workRequired}/60)
valuefield=actualWorkRequired
valueformat=compound
viewalias=actualworkrequired

Here's the text mode for the "Remaining Planned Hours" calculated custom column:

aggregator.valueexpression=SUB({workRequired}, {actualWork})
aggregator.valueformat=compound
displayname=Remaining Planned Hours
linkedname=direct
textmode=true
valueexpression=SUB({workRequired}, {actualWork})/60
valueformat=customNumberAsString

Unfortunately, grouping by job role, on a task report, only allows you to do this by the primary assignee.  This means that if you have many assignees on one task, it will only look at the primary assignee.

[MSuleiman]

This is great. Thanks a lot Nick!

[JamesM-MERGE]

Hi Nick, I used the below code on a Task report which provides this display. Do you know what the code would be to pull this same display on an Assignments report?

displayname=Assignee Planned Hrs
listdelimiter=<p>
listmethod=nested(assignments).lists
textmode=true
type=iterate
valueexpression=CONCAT({assignedTo}.{name}," (",ROUND({workRequired}/60,2)," Hrs",") ")
valueformat=HTML

[NickVa1]

Hi jamesmiller-zjd,

If you want to see the planned hours by assignee on an assignment report, you won't need text mode.  
If you group by task name (or better, task ID since that's more unique), it doesn't matter if you have one or many assignees on the task, it will break out their planned hours for each assignee on separate rows.  Getting accurate actual hours per assignee is the tough part I was unable to accomplish using an assignment report.

For tasks that have multiple assignees, to get both planned and actual hours per assignee on separate rows in the report, I used a Work Item report.

• Here's the assignee planned hours text mode:
  • aggregator.displayformat=minutesAsHoursString
    aggregator.function=SUM
    aggregator.namekey=view.relatedcolumn
    aggregator.namekeyargkey.0=assignment
    aggregator.namekeyargkey.1=workRequired
    aggregator.valuefield=assignment:workRequired
    aggregator.valueformat=compound
    displayname=Assignee Planned Hrs
    linkedname=assignment
    namekey=view.relatedcolumn
    namekeyargkey.0=assignment
    namekeyargkey.1=workRequired
    querysort=assignment:workRequired
    textmode=false
    valuefield=assignment:workRequired
    valueformat=compound
    viewalias=assignment:workrequired
• Here's the assignee actual hours text mode:
  • aggregator.displayformat=minutesAsHoursString
    aggregator.function=SUM
    aggregator.namekey=view.relatedcolumn
    aggregator.namekeyargkey.0=assignment
    aggregator.namekeyargkey.1=actualWorkCompleted
    aggregator.valuefield=assignment:actualWorkCompleted
    aggregator.valueformat=compound
    displayname=Assignee Actual Hrs
    textmode=true
    valueexpression={assignment}.{actualWorkCompleted}/60
    valueformat=doubleAsDouble
• And, if you want the variance between the two, here's the text mode for that:
  • aggregator.function=SUM
    aggregator.valueexpression=({assignment}.{workRequired}-{assignment}.{actualWorkCompleted})/60
    aggregator.valueformat=compound
    displayname=Remaining Hrs
    textmode=true
    valueexpression=({assignment}.{workRequired}-{assignment}.{actualWorkCompleted})/60
    valueformat=doubleAsDouble
• You'll want to group by something (e.g. task, planned completion date, etc.)

Hope that helps!
Nick