Reporting on approvals | Community
Skip to main content
M
MeiganRe
Level 2
October 19, 2020
Solved

Reporting on approvals

  • October 19, 2020
  • 4 replies
  • 2756 views

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.

    This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
    Best answer by Richard_Le_

    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.

    4 replies

    R
    Community Advisor
    Richard_Le_Community AdvisorAccepted solution
    Community Advisor
    October 19, 2020

    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.

      M
      MeiganReAuthor
      Level 2
      October 22, 2020

      Hey Rich -

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

      R
      Community Advisor
      Richard_Le_Community Advisor
      Community Advisor
      October 22, 2020

      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

        A
        amandak6593542
        Level 2
        October 21, 2020

        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?

        D
        DarylMa1
        Level 2
        October 22, 2020

        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.

        B
        BenFr2
        Level 2
        October 23, 2020

        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...
        D
        DarylMa1
        Level 2
        October 23, 2020

        Hey Ben, that is a task report.

        T
        TealeMcCleaf
        Level 6
        November 24, 2020

        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

        Terms & ConditionsAccessibility statement

        Loading footer...