Hi WF Community,
Is there an existing report, or a way to create a custom report, that will allow us to see a user's (task assignee's) actual hours logged vs. the planned hours on a task-by-task basis? We want to see this grouped by user. It would show the tasks they are on in rows under the user grouping. It seems like a simple report, but we ran into some issues around the planned hours being inflated when there is more than 1 timesheet entry against a task for a given user. Also we want to ensure the actual hours being displayed for a task is just for the user in the grouping and not any user that logged time against said task.
Thanks.
Nick
Solved! Go to Solution.
Do an assignment report, make sure to sum the planned hours column, and then add the following text mode as a new column:
aggregator.displayformat=minutesAsHoursString
aggregator.function=SUM
aggregator.namekey=Actual Hours
aggregator.valuefield=actualWorkCompleted
aggregator.valueformat=compound
displayname=Actual Hours
linkedname=direct
namekey=actualWorkCompleted
querysort=actualWorkCompleted
shortview=false
stretch=100
textmode=true
valuefield=actualWorkCompleted
valueformat=compound
viewalias=actualWorkCompleted
width=100
I just did this with a client not long ago, and they did a matrix report. Worked great!
Happy Easter weekend!
The only thing I can think of at this time is an hours report.
Views
Replies
Total Likes
This is what I use in my reports, hope this helps.
Project Report
displayname=Actual / Planned Hours
linkedname=direct
namekey=actualworkrequired
querysort=actualWork
textmode=true
valueexpression=CONCAT({actualWorkRequired}/60,' / ',{workRequired}/60)
valuefield=actualWorkRequired
valueformat=compound
viewalias=actualworkrequired
Task Report
aggregator.function=SUM
displayname=Actual / Planned Hours
linkedname=direct
namekey=actualworkrequired
querysort=actualWork
textmode=true
valueexpression=CONCAT({actualWorkRequired}/60,' / ',{workRequired}/60)
valuefield=actualWorkRequired
valueformat=compound
viewalias=actualworkrequired
Views
Replies
Total Likes
Do an assignment report, make sure to sum the planned hours column, and then add the following text mode as a new column:
aggregator.displayformat=minutesAsHoursString
aggregator.function=SUM
aggregator.namekey=Actual Hours
aggregator.valuefield=actualWorkCompleted
aggregator.valueformat=compound
displayname=Actual Hours
linkedname=direct
namekey=actualWorkCompleted
querysort=actualWorkCompleted
shortview=false
stretch=100
textmode=true
valuefield=actualWorkCompleted
valueformat=compound
viewalias=actualWorkCompleted
width=100
I just did this with a client not long ago, and they did a matrix report. Worked great!
Happy Easter weekend!
Can I run this report by months? So I can see planned hours compared to actual per month? And see planned hours per person by task?
Views
Replies
Total Likes
@Dustin Martin‚ Can I run this report by months? So I can see planned hours compared to actual per month? And see planned hours per person by task?
Views
Replies
Total Likes
Hi Kendra,
Sorry I missed your previous reply!
The short answer is kind of, you just have to figure it out.
The long answer involves text mode and filters.
Here are a few help articles we'll get started with:
Now, before we get to grouping, we need to establish your report's period of time.
If you're only looking for tasks, you can filter off task planned start or completion date.
Next you'll need to add a grouping, but there isn't a grouping for task Planned Hours.
So, you'll need to start with something similar and modify it to fit your needs.
We'll start with Task Original Planned Hours, and switch to Text Mode:
textmode=true
group.0.valuefield=task:originalWorkRequired
group.0.namekeyargkey.0=task
group.0.namekeyargkey.1=originalWorkRequired
group.0.valueformat=intAsInt
group.0.namekey=view.relatedcolumn
group.0.linkedname=task
This gives you the valuefield that the system will use to populate data from.
Since we're looking for Planned Hours, we'll just remove "original" and drop the capital W to a lowercase, since we use camelcase in our system.
It'll look like this:
textmode=true
group.0.valuefield=task:workRequired
group.0.namekeyargkey.0=task
group.0.namekeyargkey.1=workRequired
group.0.valueformat=intAsInt
group.0.namekey=view.relatedcolumn
group.0.linkedname=task
Note, I also updated the namekeyargkey (name key, argument key). This will help display the name in the grouping itself, versus sticking with "Task: Original Planned Hours".
For a better understanding of how this works, you have to learn how our objects interact with each other.
We start with the report itself. It's an Assignment report, meaning it hits the Assignment table. Open the API explorer, locate that row, and you'll get an understanding of where we're at so far.
Since we're looking to group by a different object, the Task Planned Hours, we have to reference the Task object. So then we expand Reference, and see Task on the list. Click it, it now has a link to the Task row.
You can search the list of fields here for Planned Hours, and you'll see 2 values, one for "work" and one for "workRequired". One is an integer, and one is a double format type. This means you'll see one as "2" versus "2 Hours" for example.
I hope this all makes sense!
Best of luck!
Thank you! this code worked for me
Best,
Sheryl
Views
Replies
Total Likes
Thanks, everyone. Stephen, I had tried those options, and although they are good if you're a project owner wanting to see an aggregate of everyones' time (actual) logged against a task, it doesn't allow me to just see actual hours for a given individual, per task, vs. the task's planned hours. Thanks though.
Dustin - this worked, thanks! We were hoping to display the report results in a bar chart, comparing a planned hours vs. actual hours. However, since I can't seem to group by the calculated custom column for Actual Hours - the one you provided - I can't chart that value. Is it possible to replicate the custom column you gave me and create a calculated custom field instead? I can then group by this field and chart it.
Views
Replies
Total Likes
Hey Nick,
The field itself is just a way to display Actual Hours (actualWorkCompleted) summarized to allow for it to appear in the matrix report.
You can absolutely add a custom calculated field.
The calculation would be Actual Hours/60
Why /60? Because the data is stored in minutes. :)
Good luck!
Views
Replies
Total Likes
Thanks, Dustin. I may explore that option (potentially).
One more question, if you don't mind - is there a way, via text mode, to pull in the Hour Entry Date field to this Assignment report?
Views
Replies
Total Likes
Hey Nick,
Not that I'm aware of, as the hour object cannot be directly referenced from the Assignment object.
You can confirm and compare via the API Explorer:
https://drop.workfront.com/GGukX7or
I hope this helps, though I know it wasn't the answer you were hoping for.
Thanks,
Views
Replies
Total Likes
Thanks for your time, Dustin. Yeah, I had checked out the API Explorer and saw that it wasn't directly related. I know there's a way to create filters that use EXISTS statements, and can connect non-related objects via 'linking' objects. It's a little confusing to me, but I was curious if someone smarter than me knew of a way to use this method to show data from a non-related object in a report? I can post to the larger community if you're uncertain. Cheers.
Views
Replies
Total Likes
I know you can do a view or a filter with EXISTS, but I don't believe you can use it for groupings, and I'm 99% sure it can't be used in a calculated field. I'd definitely post it in the reporting board. =)
Good luck!
Views
Replies
Total Likes
Hey Dustin! We don't have a separate group for Reporting anymore, so Nick is posting in the right place.
Views
Replies
Total Likes
Well, in that case... I'll just have him ask you directly.. or hopefully someone that's a bit better with reporting than I am can chime in! =)
Views
Replies
Total Likes
Views
Likes
Replies
Views
Likes
Replies
Views
Likes
Replies