Reporting on approvals

[MeiganRe]

Our WF instance is set up so when a worker moves a task to "Complete" it sends an approval to the project owner using the status "Complete - Pending approval."

We want to build a report that shows how long a task is in the Pending Approval status to track how long our requesters take to approve a task.

We've reached out to our WF consultant and are unable to find a solution, I thought someone in the community may have experienced the same issue.

[Richard_Le_ · Accepted solution]

Hi Meigan,

The way to achieve this is through the use of calculated custom fields on a custom form attached to the task that you want to measure approval duration on. You would need to create 3 calculated fields. The first two fields would generate a timestamp for each status change. i.e. record the date and time that the task was put into 'Complete - Pending Approval' status / record the date and time that the approval was provided and put into 'Complete' Status. The text mode code would look something like this:

Calculated field to generate time stamp when task is put into 'Complete' status

IF(Status='CPL',IF(ISBLANK(FIELDNAME),$$NOW,FIELDNAME),FIELDNAME)

Calculated field to generate time stamp when task is put into 'Complete - Pending Approval' status

IF(Status='CPL:A',IF(ISBLANK(FIELDNAME),$$NOW,FIELDNAME),FIELDNAME)

For the third field, you would calculate the time difference between the two status changes which would give you the ability track how long it was taking people to approve tasks. The text mode code would look like this:

Calculate the difference in weekdays between the two date / timestamps recorded in the previously created fields

WEEKDAYDIFF(FIELDNAME1,FIELDNAME2)

Hope this helps!

Best Regards,

Rich.

[MeiganRe]

Hey Rich -

Thanks so much for the reply! I'm not great at the text fields... What should I put in for the FIELDNAME?

[Richard_Le_]

Hi Meigan,

The fieldname would be the name of the field that you create on the custom form. It can be named as whatever is relevant to you - maybe 'Complete Status Date' for the first field, 'Approved Date' for the second and 'Approval time' for the third?

For example:

First create a new calculated field on the custom form and give it a name, let's use 'Complete Status Date' as per my suggestion. Then save the form (to create the field and register in the Workfront data base), then go back into the form and enter the following code into the calculation section of the 'Complete Status Date' field:

IF(Status='CPL',IF(ISBLANK(Complete Status Date),$$NOW,Complete Status Date),Complete Status Date)

Then repeat these steps for the remaining fields using the code i provided in my previous post and using your own field names.

Does that make sense?

Best Regards,

Rich

[MeiganRe]

Rich - Thanks so much for your help on this.

I continue to get an error for an Invalid expression. This is what I've entered.

IF(Status='CPL',IF(ISBLANK(Complete Status Date),$$NOW,Complete Status Date),Complete Status Date)

Any suggestions?

Thanks!

Meigan

[Richard_Le_]

Hi Meigan,

Did you create the field first (before entering the code into the calculation section)? You need to create the field first by giving it a name, setting the format to date/time and clicking save + close - but with no code in the calculation field.

Then go back into the custom form and add the code and click save + close.

If you follow the steps in this sequence, you shouldn't get an invalid expression warning.

Because this calculated field is referencing itself, you need to add the field to the data base before entering the calculation, this is why it's important to create the field before adding the calculation to it.

Does that make sense? Let me know how you get on.

Best Regards,

Rich.

[MeiganRe]

Nope. That's what I was doing wrong. Thank you!

[amandak6593542]

Following, in need of the same type of reporting. Is it possible to build a report just on all the tasks that a specific user would have waiting in the Complete - Pending Approval status?

[DarylMa]

Sounds like you are struggling with getting approvals. What has worked for us is the creation of a 'user' dashboard that we have set for all 'landing pages' using layout templates. One of the reports on the dashboard is 'User Approvals Needed'. Its a report that uses the following as a filter:

awaitingApprovalStepApproversMM:userID=$$USER.ID

awaitingApprovalStepApproversMM:userID_Mod=in

For the columns, we use Name, Description, Status, Approvers and Current Approvers and Status.

Since the approver is presented with this every time they log into Workfront its hard for them to ignore.

Hope this helps.

[BenFr1]

This may work for me too! Thank you for sharing.

1. Can you please confirm the report type? I tried creating a new "User" report and it didn't work with the default columns. Furthermore, those options listed above are not available for the "User" report...

[DarylMa]

Hey Ben, that is a task report.

[TealeMcCleaf]

Hi Meigan,

Using the API, I was able to pull the fields that show when the approval began and when it was approved for specific tasks like this:

This example is pulling the approval start and completion for a task with "Budget Review" in the name.

column.19.displayname=Review Completion Dates

column.19.sharecol=true

column.19.textmode=true

column.19.value=<strong>Budget Review Start: </strong>

column.19.valueformat=HTML

column.19.width=100

column.20.displayname=Budget Review Approval Path Initiation Date

column.20.listdelimiter=</>

column.20.listmethod=nested(tasks).lists

column.20.sharecol=true

column.20.textmode=true

column.20.type=iterate

column.20.usewidth=true

column.20.valueexpression=IF(CONTAINS("Budget Review",{name}), {approvalStartDate})

column.20.valueformat=HTML

column.20.width=100

column.21.sharecol=true

column.21.textmode=true

column.21.value=<hr><strong>Budget Review Complete: </strong>

column.21.valueformat=HTML

column.21.width=100

column.22.displayname=Budget Review Approval Path Completion Date

column.22.listdelimiter=</>

column.22.listmethod=nested(tasks).lists

column.22.sharecol=true

column.22.textmode=true

column.22.type=iterate

column.22.usewidth=true

column.22.valueexpression=IF(CONTAINS("Budget Review",{name}), {approvalCompletionDate})

column.22.valueformat=HTML

column.22.width=100

You could take it a step further and perform a datediff calculation on the field in a Task report to show the # of days it was pending.

valueexpression=CONCAT(ROUND(DATEDIFF({approvalCompletionDate}, {approvalStartDate}),2)," Days")

valueformat=HTML

linkedname=direct

namekey=id

aggregator.displayformat=HTML

aggregator.valueexpression=CONCAT(ROUND(DATEDIFF({approvalCompletionDate}, {approvalStartDate}),2)," Days")

aggregator.function=SUM

aggregator.valueformat=HTML

aggregator.namekey=id

textmode=true

displayname=Start to Completion of Task Approval

Hope this helps,

Teale