Is there a text mode solution for filtering projects when the actual duration is greater than the planned duration?

[JaredMauch]

I'm trying to filter completed projects in a report when the actual duration is greater than the planned duration but I'm not having success in text mode. I've tried:

actualDuration=FIELD:duration

actualDuration_Mod=gt

actualDuration=FIELD:plannedDuration

actualDuration_Mod=gt

Applying either of those makes the report show no data, but it'll show data when either of those are not applied. Can someone tell me what I'm doing wrong and point me in the right direction? Thanks!

[AnthonyIm · Accepted solution]

Hi Jared, looking at the API Explorer (which is a treasure when doing text mode https://one.workfront.com/s/api-explorer) I'm not seeing actualDuration, duration, or plannedDuration as field that exist on a project object type.

I would try...

actualDurationMinutes=FIELD:durationMinutes

actualDurationMinutes_Mod=gt

See if that works for you :)

[JaredMauch]

Hi Anthony,

That worked for my report. Thanks for the suggestion and the link to the API Explorer!

[andreadmw]

Hi! this just helped me so thank you! I also wanted to ask a follow-up. If I wanted to refine further to show outliers. So lets say Projects where the actual duration was over the planned duration by 2 weeks or more.

[Madalyn_Destafney]

Hi there, were you able to figure this out? I'm trying to do the same thing, I want to only pull when the difference in durations was anything over 1 week into the report...

Also want same thing in a different report for when planned vs actual hours is difference of X... @AnthonyIm do you know what I could use for this?

[imgrund]

Hi Madalyn - 

For your instance, I would do a calculated field on tasks that does the calculation of how many days difference you have, and then do the filter on that. 

The calculation I tend to use is 

IF(!ISBLANK({actualCompletionDate}),PROD(WORKMINUTESDIFF(CLEARTIME({actualCompletionDate}),CLEARTIME({plannedCompletionDate})),IF(CLEARTIME({actualCompletionDate}) < CLEARTIME({plannedCompletionDate}), -1, 1))/480,"")

Not sure how your setup is for your instance, but this would require that all tasks have a custom form attached to them. 

Then for your report, you will run a task report where the Duration Slippage (or whatever you called the calculated field) is greater than 5. That will then show you all the tasks overdue more than a week. 

It is also nice to have this number on the form because then you can also do the average duration slippage by various components to help identify issues that might need to be addressed or templates that need to be updated.