Skip to main content

Lesson 9: Time Differences – Time to Approve a Report

In this report we will first examine the data before completing the final formatting.

Create a New Report

  1. Right-click on the Expense Analysis (Approval / Routing) object and select NEW > REPORT.
  2. Add the following fields to the report as dimensions (BY):
  3. Header (Segment) - LineItem (Segment) – LineItemStep (Segment) - Approver (Segment) – LASTNAME (Field)
  4. Header (Segment) - Report Name (Field)
  5. Header (Segment) - LineItem (Segment) – LineItemStep (Segment) - ID (Field)
  6. Header (Segment) - LineItem (Segment) – LineItemStep (Segment) - CREATEDATE (Field)
  7. Header (Segment) - LineItem (Segment) – LineItemStep (Segment) - UPDATEDATE (Field)
  8. Header (Segment) - LineItem (Segment) – LineItemStep (Segment) - STATUSAPPROVED (Field)

Create a Filter on the STATUSAPPROVED

The report is now showing all of the steps in the approval process for each approver.

  1. Right-click on the STATUSAPPROVED field and select Filter Values.
  2. Click on VALUE and be sure the Type is CONSTANT.
  3. Click GET VALUES and select ALL. Then double-click on APP to add it to the list.
  4. Click OK on the drop-down and OK in the Create a Filtering Condition window.

Create a Detail Field for Time to Approve

  1. In the Data tab, in the Calculation group, select Detail (Define).
  2. Name the field HoursDiff.
  3. Click on the FUNCTIONS (fx) button in the top right corner.
  4. In the Date and Time folder, select the HDIFF function. This function will calculate the difference between two time fields.

  5. Click the Tree View button.
  6. Replace the DTfield1 and DTfield2 within the HDIFF function.
  • Replace DTfield1 with Header (Segment) - LineItem (Segment) – LineItemStep (Segment) - UPDATEDATE (Field)
  • Replace DTfield2 with Header (Segment) - LineItem (Segment) – LineItemStep (Segment) - CREATEDATE (Field)

  • The 'component' should be changed to HOUR.
  • The 'output_format' should be changed to D12.2.

  1. The completed function should be: HDIFF ( HEADERLINEROUTE.LineItemStep.UPDATEDATE , HEADERLINEROUTE.LineItemStep.CREATEDATE , 'HOUR', 'D12.2')
  2. Click OK.

Add HoursDiff to the Report as a Dimension

  1. Under Header (Segment) - LineItem (Segment) – LineItemStep (Segment), grab the HoursDiff field and add it to the report as a dimension.
  2. Verify that the hours are calculating correctly for each step.

Calculate Average Time to Approve

  1. Drag the HoursDiff field to the Sum field container.
  2. Right-click the HoursDiff field.
  3. Select More > Aggregation Functions > Average.

Create the Summary Report

  1. Drag the Report Name field and ID field to the Sum field container.
  2. Delete the STATUSAPPROVED, CREATEDATE and UPDATEDATE fields from the BY field container. You can do this by right-clicking on the column name in the resource panel, or by right-clicking on the column on the report.

Calculate the Number of Reports Approved by Each Person

  1. Right-click on Report Name field in the Sum field container.
  2. Select More > Aggregation Functions > Count Distinct.
  3. The underlying data has one row per approval step. This will calculate the number of reports per person.

Calculate the Total Number of Approved Lines for Each Person

Let us also show how many line items each person approved.

  1. Right-click on the ID field.
  2. Change the aggregation function to Count.

Change the Column Titles

Right-click on each column and select Change Title.

  1. Change LASTNAME to Approver.
  2. Change COUNT DISTINCT Report NAME to Number of Approved Reports. This will show the number of individual reports that were approved. For our purposes we don't want to know how many lines were approved; we want the number of reports that were approved.
  3. In the Field – CNT.DST.Report Name tab, in the Style group, click the Justify Center button.
  4. Change ID Count to Number of Approved Lines and center the column.
  5. Change Ave HoursDiff to Average Hours to Approve and center the column.

Refinement: Exclude Reports Where the Approver Is the Expense Owner

You may want to exclude reports where the approver is the expense owner (usually these were created by a delegate).

  1. In the Data tab, click FILTER.
  2. Click NEW FILTER on the Create a Filtering Condition window.
  3. Click on FIELD and use the Hierarchical Structure to select Header (segment) – OwnerID (field).
  4. Change the condition to Not equal to.
  5. For the < Value >, change the Type to FIELD. Select Header (segment) – LineItem (segment) – LineItemStep (segment) – PERSONID (field).
  6. Click OK to close the Create a filtering condition window.

Save and Run the Report

  1. In the Quick Access Toolbar, click the Save button. Save the report in this location: Content > [Your Company Name] > My Content.
  2. Name the report “Lesson 9 - Time Differences - Time to Approve a Report".
  3. In the Quick Access Toolbar, click the Run button.

Was this article helpful?