Data & Insights' Open Payroll allows you to visualize the details of your employees and payroll in an interactive way. The interface presented by Open Payroll is dynamically generated by your data. This document describes the dataset that is required for Open Payroll.
For Open Payroll to work, your dataset must be organized into a flat-file. You can think of this as having all your data on a single tab in Microsoft Excel. The dataset described below has three categories of columns/fields:
- Required Fields: These fields are required by Open Payroll to function.
- Recommended Fields: When these fields are provided, the usefulness of Open Payroll will become substantially better for constituents.
- Optional Fields: These fields are optional as they are useful for users (e.g. analysts) accessing the raw data directly as well and may be used in future enhancements of the Open Payroll product.
Dataset Fields
|
Recommended Column Name |
Type |
Value |
Notes |
|
year |
Required |
Number |
Used to distinguish entries across multiple years. |
|
transaction_date |
Required |
Date/Time |
The date of the paycheck. For annual data, add years in the format 1/1/[year]. |
|
Employee Information |
|||
|
employee_id |
Required |
Text |
This unique identifier is used to associate multiple years of data to the same person, enabling a trend visualization. |
|
name_last |
Recommended |
Text |
Used in searching and show data on a specific individual. |
|
name_first |
Recommended |
Text |
Used in searching and show data on a specific individual. |
|
name_suffix |
Recommended |
Text |
Used in searching and show data on a specific individual. |
|
Position Details |
|||
|
department / division / etc. |
Required |
Text |
Used to aggregate payroll for a given department/division name. |
|
position_title |
Required |
Text |
Used to present a human-readable title for the position. |
|
position_type |
Required |
Text |
Used to aggregate payroll by employment type (ex. Full Time, Part Time, Contract), and to properly represent aggregate statistics (e.g. averages and medians). |
|
service_start_date |
Recommended |
Date |
Calculate length of service since initial employment. |
|
service_end_date |
Recommended |
Date |
Calculate length of service since initial employment. |
|
Actual/YTD Pay Details |
|||
|
pay_total_actual |
Required |
Number |
The actual amount that the position has received in total pay in the paycheck. |
|
pay_base_actual |
Recommended |
Number |
The actual amount that the position has received in base/salary pay in the paycheck. |
|
pay_benefits_actual |
Recommended |
Number |
The actual amount that the position has received in benefits pay in the paycheck. |
|
pay_overtime_actual |
Recommended |
Number |
The actual amount that the position has received in overtime pay in the paycheck. |
|
pay_other_actual |
Recommended |
Number |
The actual amount of other pay (e.g. supplemental pay, bonuses, etc) in the paycheck. |
|
Target Pay Details |
|||
|
projected_total |
Optional |
Number |
The projected total annual earnings for the employee. |
|
Supplementary Information |
|||
|
memorandum_of_understanding |
Optional |
Text |
Show employees connected to a specific MOU. |
|
contract |
Optional |
Text |
Show employees connected to a specific union contract. |
|
bargaining_group |
Optional |
Text |
Show employees connected to a specific bargaining group. |
|
Any other field |
Optional |
Text |
Used for data analysts looking at government specific data. |
Comments
Article is closed for comments.