Working with the TL Reconciliation Report

Body

Overview

On the System Manager Reporting Page in OneUSG Connect, you can run the TL Reconciliation Report (180_TL_RECONCILIATION) to see hours in Time and Labor that have been 'Taken by Payroll.' This is a query that only shows the hours that are set to be paid for the payroll. The query will only show data after the first Payroll Load, typically on Monday afternoon.

Keep in mind that the hourly rate listed is the hourly rate on job data, and special Time and Labor Rules do not apply. For example, overtime hours will show the employee's base hourly rate, and not the time and a half rate.

For more information on this process, see the Manager Self Service for Supervisors and Time & Absence Approvers courses in the OneSource Training Library.

In this article, you will learn the steps to run the Time and Labor Reconciliation query and use a PivotTable to view your data.

1. Step 1

First, log into OneUSG Connect

From the Manager Self Service page, click the System Manager Reporting tile.

2. Step 2

The System Manager Reporting page is displayed.

Under the Queries menu, click the Time and Labor menu item.

3. Step 3

The available queries under Time and Labor are displayed.

Click the TL Reconciliation Report link.

4. Step 4

The search criteria for the query 180_TL_RECONCILIATION is displayed

Click the Calendar icon next to the Period Begin DT field to select a date.

Note: You can search for a specific employee if you have the Employee ID available. You can also leave the % sign in the Employee ID and/or Dept ID fields to return all possible results.

5. Step 5

Click the appropriate date for the Period Begin DT field .

Example shown here: March 24 link.

6. Step 6

Click the Calendar icon next to the Period End DT field to select a date.

7. Step 7

Click the appropriate date for the Period End DT field.

Example shown here: April 6 link.

8. Step 8

Click the View Results button.

9. Step 9

Click the Excel SpreadSheet link.

10. Step 10

Click the Open button.

11. Step 11

The Excel file opens in a protected view.

Click the Enable Editing button to work with the data.

12. Step 12

Click any cell prior to inserting the Pivot chart in the document.

Example shown here: Cell 4 H100

13. Step 13

Click the Insert tab at the top of the screen.

14. Step 14

Click the PivotChart button

15. Step 15

The Create PivotChart dialog box is displayed with default settings. You can change the settings for the PivotChart according to your needs.

Example shown here: OK button.

16. Step 16

The PivotChart is displayed. You can use the PivotChart Fields section to the right of the page to create the chart.

To include the Employee ID field in the PivotChart, you can drag the Employee ID field to the Axis (Categories) quadrant.

17. Step 17

The Employee ID field is now shown under the Row Labels on the left side of the screen.

Next, you can drag the Last Name field to the Axis (Categories) quadrant.

18. Step 18

Now you can see that the last names of the employees are displayed under their associated Employee ID in Column A.

In the PivotChart Fields area, scroll down to drag the Sum Quantity field to the Values quadrant.

19. Step 19

The total reported hours for the pay period for all your employees are displayed

20. Step 20

Click the Data tab at the top of the screen.

21. Step 21

Click the Sum of Sum Quantity cell in B1.

22. Step 22

Click the Sort Largest to Smallest icon button to sort the total reported hours in an increasing or decreasing order.

23. Step 23

The reported hours are sorted to display hours reported from most to least.

Drag the Pay Group field to the Filters quadrant to filter the table by Pay Groups.

24. Step 24

The Pay Group filter is displayed in the top row.

Click the Pay Group (All) drop-down menu to view reported hours for a particular pay group.

25. Step 25

Click the Select Multiple Items option.

26. Step 26

Deselect any pay group by clicking the checkbox next to the pay group.

27. Step 27

Once you have deselected all the Pay Groups that you don't want to view, click the OK button.

28. Step 28

Employees in the selected Pay Group(s) are displayed with their Last Names, Employee ID, and the total hours reported for the specific pay period.

29. Step 29

Click the Sort Smallest to Largest icon button to sort the column from the least to the most number of hours reported.

30. Step 30

The hours reported are displayed from lest to most.