Project Reports vs. Hour Reports - what am I missing? Losing my mind!

[Christina_Jarosz]

When we began WF, our consultant setup project reports that showed us the total count of projects per xyz. Likewise, there was a similar project report that showed total hours per project per xyz.

What we didn't anticipate that YOY data where we want to isolate ONLY 2019 or ONLY 2018 projects would not let us filter that way. For example, we wanted to see the hours logged during 2018 for a project - not all hours on the project YTD.

I'm stuck at a crossroads. By doing a project report (by count) we get an accurate project count of 1265 projects in 2019. It tells me that we logged 352.5 hours for project 36489 in 2019. (inaccurate)

By doing an hour report, we get an inaccurate project count of 547 projects in 2019. (as it is only displaying projects that have hours tracked to them). This report tells me we logged 1.75 hours for project 36489 in 2019. (accurate). It also shows time entries vs. projects as the data detail.

In speaking with WF support, they said:

I was able to meet with my team and the handling of this report is outside the capabilities of our our objects interact. The best option you have is to make the hour reports for your objects and then export to excel and find the unique values that way. We have other services that may be able to help with what you are looking to do, but with Workfront - we have reached an end point to the reporting capabilities for this type of data.

So I ask our very knowledgeable community this:

• Is there a way to display a report that shows a total of project count and hours specific to that criteria in the same report? Something atune to, in 2019 we had a project count of 1265 projects. For these projects, here is the total hours logged during 2019 only.

This is so easily done exporting the hourly report time entries into excel, tossing into a quick pivot and there's the data. I'm not understanding why this is so hard to do in WF. Our executive team is not happy right now.

[Doug_Den_Hoed__AtAppStore]

Hi Christina,

I can offer a hack that might make your executive team happier (although you might be grumpier).

The root cause is that each Project needs to have at least one hour against which to operate, from a "Count" perspective. So, either manually (eww), or with a kickstart, or with our Excel Updater solution, provided your environment allows time to be tracked at the project level (at least temporarily), add an immaterial amount of time (e.g. 0.01 hours) to every project with the same date as its Planned Start Date.

Doing so will then allow you (in an Hours Report) to simultaneously Count the projects "accurately" by YYYY, and sum the hours "reasonably" (ignoring the minor amounts for such placeholders), either by filtering with an OR (e.g. "Entry Date between 01-01-2018 and 12-31-2018 OR Hour = 0.01), or by bringing back everything (no filter) in a matrix grouping Project on the rows and Hour Entry Entry Date (to the YYYY) on the columns.

Regards,

Doug

[Christina_Jarosz]

Bumping this up - can I run a project report and use an EXISTS filter to ONLY pull in a specific year's hours?

[ManuelBa]

Unfortunately not. We have tried to tackle similar management requirements but Workfront's object-based data model prevents a solution.

The workaround shown above is clever, even though a bit sad that implementing dummy hours is necessary.

We also considered connecting a BI tool like Tableau via Fusion or API, but decided that it is not worth the effort for only a few reports.

@Doug Den Hoed‚ : Do you think there is a chance pulling both, projects and hours, with your Magic Reports and then merge the two dimensions into a chart?

[Doug_Den_Hoed__AtAppStore]

Hi Manuel (and Christina),

Yes, our Magic Reports solution could certainly bridge the gap and present these combined annual Project Counts and Total Hours together, either as a numeric grid, and or graphically (e.g. trending over years).

Another option, come to think of it, could be to use our UberCalc solution to (on a button click) "push" the total hours into custom data on each Project (Total Hours 2018, Total Hours 2019, etc) so you could then easily report on the information (e.g. count the Projects, total the hours) using standard Workfront reporting, then roll your own grids and charts as you see fit, using Workfront (which -- longer topic -- could even include trending such information over time).

Regards,

Doug

[ManuelBa]

Thinking about the 2nd option, a Fusion Workfront to Workfront connection with some logic could eventually get close to that functionality? Something like "when logging an hour to a project, update the custom fields with hours per year"?

Disadvantage is for sure the associated costs (Fusion is about four times more expensive) and, in general, that you need a custom field for every year (2018, 2019, …, 2025, …, 2030, …).

[Doug_Den_Hoed__AtAppStore]

Yes, exactly, Manuel,

Fusion could certainly also do the second option (as could some low level API brute-force).

Regards,

Doug

[AdinaPi1]

I've been trying a few things, and I think I'm getting close. Here is what I did.

Hour Report

Required View columns:

• Date
• Hours (summarized by Sum)
• Project: Reference Number: Summarize by Count, then go into text mode and add D in front of CNT (aggregator.function=DCNT)

Groupings - Matrix :

• Left side: Project Name
• Right side: Hour > Entry Date (group dates by Year)

Filter: I filtered by portfolio, but you can filter by whatever. Anything under general hours will have no project grouping.

Run report, and scroll to the bottom of the matrix to the Total row. It will show # of projects for each year, and the total number of hours for each year.

Let me know if I am missing any variables, or if you need screenshots.

[ManuelBa]

thanks for posting your solution!

What's the role of the 'D' in DCNT?

[ChristinaJay]

Bumping this up - any idea if you can make this a calculated field at a project level?

[AdinaPi1]

D stands for Distinct. So in the grouping, if there are 8 hour entries it will show 1 (project) instead of 8. I use the project reference field since that is unique to each project.

[ManuelBa]

Thanks so much, we have a couple of reports where ‚D‘ opens up new possibilities.

So far, we have exported to Excel for distinct count.

[Doug_Den_Hoed__AtAppStore]

Nice job Adina,

One caveat though (for Christina, and as per my earlier hack note, above) is that technique will exclude Projects that have zero hours in a given year...which might be fine, but might also be worth noting.

Regards,

Doug

[ChristinaJay]

Bumping up an oldie (but goodie) thread...we already do this via hour report export into PowerBI, but noodling at getting to it via Fuson.

The logic I have is searching for the hour record type with the entry date greater than 01/01/24 and the end date less than 12/31/24, then summed by project. 

I'm getting a weird API message though:

The operation failed with an error. [422] APIModel V18_0 does not support field (Hour)