Report to show a users actual hours vs. planned hours for tasks their assigned to

[NickVa1]

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

[skyehansen]

The only thing I can think of at this time is an hours report.

[sljonas]

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

[DustinMa · Accepted 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!

[KendraRo]

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?

[KendraRo]

@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?

[DustinMa]

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:

Understanding text mode

API Explorer

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!

[SherylF]

Thank you! this code worked for me

Best, 

Sheryl 

[NickVa1]

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.

[DustinMa]

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!

[NickVa1]

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?

[DustinMa]

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,

[NickVa1]

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.

[DustinMa]

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!

[kynabaker16]

Hey Dustin! We don't have a separate group for Reporting anymore, so Nick is posting in the right place.

[DustinMa]

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! =)