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

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

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

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?

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

Yes, exactly, Manuel,

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

Regards,

Doug

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.

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.

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