Excel Add-In for TSheets

Build 23.0.8839

PayrollReportByJobCode

Payroll Report broken down by jobcode

Table Specific Information

Select

Query the PayrollReportByJobCode table to retrieve a payroll report, broken down by jobcode, with filters to narrow down the results. The add-in will use the TSheets API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the add-in. By default the report will include results for a time range within the last 30 days. You can filter by StartDate and EndDate filters to get a payroll report over a different time range. The date range defined by StartDate and EndDate must not exceed 31 days.

  • GroupId field supports the '=' and IN operators.
  • StartDate, EndDate and IncludeAdvancedOvertime fields support the '=' operator.

For example, the following queries are processed server side:

SELECT * FROM PayrollReportByJobCode WHERE GroupId IN ('29474', '29474') AND IncludeAdvancedOvertime = false
SELECT * FROM PayrollReport WHERE StartDate = '2019-11-01' AND EndDate = '2019-12-31'

Based on the columns selected in projection explicitly, the aggregated data displayed may change. Aggregate payroll metrics broken down by jobcode id:

SELECT * FROM PayrollReport WHERE StartDate = '2019-11-01' AND EndDate = '2019-12-31'

Aggregate payroll metrics broken down by jobcode id per each user:

SELECT UserId, JobCodeId, TotalWorkSeconds, TotalReSeconds, TotalPtoSeconds, OvertimeSeconds FROM PayrollReportByJobCode

Aggregate payroll metrics broken down by jobcode id and date per each user:

SELECT Date, UserId, JobCodeId, TotalWorkSeconds, TotalReSeconds, TotalPtoSeconds, OvertimeSeconds FROM PayrollReportByJobCode

Columns

Name Type Description
UserId Int Id of the users these totals are calculated for.
JobcodeId Int Id of the jobcode the metrics apply to.
StartDate Date Start of reporting timeframe.
EndDate Date End of the payroll reporting timeframe.
Date String Date that these total are calculated for.
TotalReSeconds Int Total time for the current shift, in seconds.
TotalOTSeconds Int Overtime, in seconds.
TotalDTSeconds Int Doubletime, in seconds.
TotalPTOSeconds Int Total PTO time, in seconds.
TotalWorkSeconds Int Total overall time, in seconds.
OvertimeSeconds String The overtime seconds object, where the key is the multiplier rate and the value is the number of seconds of overtime at that rate
FixedRateSeconds String The fixed rate seconds object, where the key is the multiplier rate and the value is the number of seconds of overtime at that rate

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name Type Description
GroupId String A comma-seperated list of group ids. Only time for users from these groups will be included.
IncludeAdvancedOvertime Boolean If true, overtime will be formatted such that it includes the time for individual multipliers and can support more than just 1.5x (ot) and 2x (dt) overtime.

Copyright (c) 2024 CData Software, Inc. - All rights reserved.
Build 23.0.8839