Working with the TL Reconciliation Report

   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

Manager Self Service page displayed with the System Manager Reporting tile highlighted

First, log into OneUSG Connect

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

2. Step 2

System Manager Reporting page displayed with the Time and Labor menu item highlighted

The System Manager Reporting page is displayed.

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

3. Step 3

Time and Labor query page displayed with the TL Reconcilation Report link highlighted

The available queries under Time and Labor are displayed.

Click the TL Reconciliation Report link.

4. Step 4

Search criteria for the query 180_TL_RECONCILIATION displayed with the Period Begin DT field highlighted

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

Search criteria for the query 180_TL_RECONCILIATION displayed with the Period Begin DT fCalendar highlighted

Click the appropriate date for the Period Begin DT field .

Example shown here: March 24 link.

6. Step 6

Search criteria for the query 180_TL_RECONCILIATION displayed with the Period End DT fCalendar highlighted

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

7. Step 7

Search criteria for the query 180_TL_RECONCILIATION displayed with the Period Begin DT Calendar highlighted

Click the appropriate date for the Period End DT field.

Example shown here: April 6 link.

8. Step 8

Search criteria for the query 180_TL_RECONCILIATION displayed with the View Results button highlighted

Click the View Results button.

9. Step 9

Search criteria for the query 180_TL_RECONCILIATION displayed with the Excel Spreadsheet link highlighted

Click the Excel SpreadSheet link.

10. Step 10

Search criteria for the query 180_TL_RECONCILIATION displayed with the 180 TL Recognition xlsx file highlighted

Click the Open button.

11. Step 11

Excel file displayed with the Enable Editing button highlighted

The Excel file opens in a protected view.

Click the Enable Editing button to work with the data.

12. Step 12

Excel file displayed with Cell 4 H100 highlighted

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

Example shown here: Cell 4 H100

13. Step 13

Excel file displayed with the Inset button highlighted

Click the Insert tab at the top of the screen.

14. Step 14

Excel file displayed with the Pivot Chart button highlighted

Click the PivotChart button

15. Step 15

Create PivotChart dialog box displayed with the OK button highlighted

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

PivotChart displayed with the Employee ID field and Axis (Categories) quadrant highlighted

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

PivotChart displayed with the Last Name field and Axis (Categories) quadrant highlighted

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

PivotChart displayed with the Sum Quantity field and Axis (Categories) quadrant highlighted

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

PivotChart displayed with the total reported hours for the pay period for all employees highlighted

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

20. Step 20

PivotChart displayed with the data button highlighted

Click the Data tab at the top of the screen.

21. Step 21

PivotChart displayed with the Sum of Sum Quantity button highlighted

Click the Sum of Sum Quantity cell in B1.

22. Step 22

PivotChart displayed with the Sort Largest to Smallest icon button highlighted

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

23. Step 23

PivotChart displayed with the Pay Group field and the Filters tool highlighted

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

PivotChart displayed with the Pay Group (All) drop-down menu highlighted

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

PivotChart displayed with the Select Multiple Items option highlighted

Click the Select Multiple Items option.

26. Step 26

PivotChart displayed with the Pay Group 18C checkbox highlighted

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

27. Step 27

PivotChart displayed with the OK button highlighted

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

28. Step 28

PivotChart displayed with the Last Names, Employee ID, and the total hours reported for the specific pay period highlighted

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

PivotChart displayed with the Sort Smallest to Largest  icon button highlighted

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

PivotChart displayed with the hours reported displayed from lest to most highlighted

The hours reported are displayed from lest to most.
Print Article