Downloading Wagesheet Data

Last updated by Andrew Treadwell on July 23, 2020 12:52

The wagesheet data can be downloaded in multiple formats. Most of the export formats require configuration to ensure they produce the correct data to be used by third-party services such as Sage Payroll or Pegasus Opera. The export formats marked with a * below will only be enabled if you require this format. You can have multiple formats enabled; please speak to us if this is required.

When downloading a file, the system will check to ensure that the correct information has been provided to populate the file. For example, the Sage Payroll file will check that all staff in the wagesheet

CSV

This format will produce a list of each staff member’s name with the number of hours they are owed per wage. Each wage will be listed on a different line so staff can appear in multiple rows. This is followed by the hourly rate in £ and the total hours in decimal minutes.


Rob abashekhTOFS rate 17.055.82
Joy AshurstTOFS rate 17.0511.87
Stephanie AshurstTOFS rate 17.055.03
Stephanie AshurstEnts rate 17.055.55
Michael atkinTOFS rate 17.0510.12
Sara BaileyTOFS rate 17.054.95
Heather BrandumEnts rate 17.053.8
James BurtallyEnts rate 37.357.03
Joseph CampsTOFS rate 17.0510.25
Alice carstairsTOFS rate 17.056.77
Emma CarterTOFS rate 17.0511.1

Sage 50 Accounts File*

This file is formatted for use with Sage. The file includes your staff member’s Employee number used in Sage followed by the Sage payment reference to use and the decimal hours and decimal hourly rate.

This format requires the Payment Reference to be set on the Wage’s Account plus the employee’s Sage reference being assigned.


Employee ReferencePayment ReferenceHoursRate
3135115.827.05
31571111.877.05
3188115.037.05
3188215.557.05
32141110.127.05
3225114.957.05
3111213.87.05
2856237.037.35
31941110.257.05
3120116.777.05

7.6.3.3. Sage Payroll

We provide two different formats for Sage Payroll; both the official default format their documentation recommends and a custom format that can be used with a custom import template. This provides names of employees and removes their rate of pay so Safe Payroll will use the rates set on their account.
Sage Payroll File: Default Format*

This format is designed for Sage Payroll Column A is Sage Payroll employee reference, Column B is the pay element ID, column C is the decimal hours worked and column D is the hourly rate.

This format requires the employee’s Sage Payroll reference being assigned. This can be done via the Control Staff page under Staff > Staff Settings.

You will also need to assign the payment reference (Pay Element ID) to each wage. This is configured under the Payroll / Journal Reconciliation section of each Pay Element.


Employee ReferencePayment ReferenceHoursRate
3135115.821.00
31571311.872.00
3188115.031.00
3188145.552.00
32141110.121.00
3225114.951.00
3111113.81.00
2856117.031.00
31941210.251.00
3120146.771.00
31341101.00

Sage Payroll File: With Names*
This format is designed for Sage Payroll. It is similar to the default format but provides an employee name and drops the rate of pay column. This format can be imported to Sage Payroll using a custom import template you create within Sage Payroll.

Column A is the employee name, column B is Sage Payroll employee reference, column C is the pay element ID and column D is the decimal hours worked.

This format requires the employee’s Sage Payroll reference being assigned. This can be done via the Control Staff page under Staff > Staff Settings.

You will also need to assign the payment reference (Pay Element ID) to each wage. This is configured under the Payroll / Journal Reconciliation section of each Pay Element.


NameEmployee ReferencePayment ReferenceHours
James Andrew3135115.82
Andrea Franks31571311.87
John Hopkin3188115.03
James Edwards3188145.55
Stephanie Jones32141110.12
Tom Aindow3225114.95
Jennifer Jenkins3111113.8
Annabell Rice2856117.03
Thea Bolton31941210.25
Boris Hugo3120146.77
Peter Westwood3134110

Dealing with multiple rates per Sage Pay Element ID
Sage Payroll badly handles multiple rates of pay per Pay Element Id. It will simply error or fail to import a second line for the same Sage Payroll Pay Element Id. This means that pay might be missed.

To resolve this issue, StaffSavvy will export just one line per Sage Pay Element Id. If there are multiple rates of pay for that Pay Element Id (e.g. the rate of pay changed mid-month), then the export will report the highest rate of pay.

We’ll then also add an “adjustment” line to that staff member. This adjustment line will contain a negative number to adjust the gross pay back down to the correct level for the wage sheet.

The Sage Payroll Element Id that you want the system to use for this adjustment line must be set under the Global Settings for Sage Payroll.

Sage Payroll Bureau File*

This is a human readable format designed to support a format used by external Sage Payroll Bureaus. The format includes full names, employee numbers, departments and holiday hours. The format also allows for manual edits to be made in pre-set columns.

This format requires the Sage Payroll Rule to be set on the Wage plus the employee’s Sage reference being assigned.

Midland iTrent Export File*

This format is designed to be used with Midland iTrent and supports the iTrent employee numbers, Element codes and cost codes.

This format requires the Element code to be set on the Wage, the default Cost Code set per Venue plus the employee’s iTrent reference being assigned.

You can also decide into additional information and split the time entries per occupancy codes or employee references based on how your iTrent system is configured.

Export per_ref_no (one reference per staff member)

This is the default option. Every employee as a single employee reference that is assigned via Control Staff. This is used for all time entries)

Export occ_ref_no (one occupancy reference per staff member per venue)

This allows you to specify different occupancy references per staff member per venue. It allows you to assign the staff costs to different cost centres within iTrent. The default occupancy reference is still assigned via Control Staff but other occupancy references can then be assigned for each employee for each of the venues where they work.

When this mode is enabled, an additional column is added to the Mange Venues page (under the employee’s profile page and click Actions menu to find this page).

Here you can then set the occupancy code per venue. If no code is provided then the system will use the employee’s default code set on the Control Staff page.

Export reference per role (one reference per staff member per role)

This allows you to override the employee’s iTrent reference for particular roles so that they can be assigned to different cost centres in iTrent.

Cost codes can also be overwritten per day on Manage Shifts and on each time entry under Daily Shift Review or Unprocessed Wages.

 StaffSavvy Simple One*

This format is designed to be the start of a manual process to provide hours to a third party. The export includes employee names, employee numbers, total pay, total hours and breakdowns of hours for holiday and sickness.

The export also breaks the hours our per venue (not per venue export name but the actual venue name) and also per rate.

Total hours are shown along with a total cost per that rate.

Additional columns such as gratuity, additions and deductions can be edited/added outside of StaffSavvy.

Pegasus Opera 3*

This format is designed to create the standard import format used by Pegasus Opera 3.

To create the format, the system needs to know the Transaction Code on each pay element. Set this under the Pay > Wage Settings > Pay Elements. Any pay rates with the same Transaction Codes will be merged together in the exported file.

Each employee will also need their Opera Employee Reference number. Set this under Staff > Staff Settings > Control Staff.

Exchequer Journal*

Contents and details to be confirmed following approval of this format.

ADP Freedom*

This format works with the ADP Freedom software. It allows for a staff employee reference, cost centre code and earn code to be assigned.

The format also includes GL BUS Code but this will use the cost centre code.

Employee_codeEARN_CODEEE_EARN_AMTEE_EARN_QTYEE_EARN_RATECOST_CENTRE_CODEGL_BUS_CODETRANS_DATE
625833TEAM1ENTSENTS23/01/2017
485932TEAM1ENTSENTS06/02/2017
484932TEAM1ENTSENTS13/02/2017
484932TEAM1.42SHOPSHOP15/02/2017
284921FINANCE3OFFICEOFFICE15/02/2017
222374ENTS8.25ENTSENTS15/02/2017

Miracle*

This format works with the MircalePay NAV software. It allows for a staff employee reference, global dimension codes and job titles.

To configure this, you will need to assign each employee their Miracle reference under Staff > Staff Settings > Control staff.

Each venue will then need to have it’s Miracle Global Dimension 1 Code assigned. Go to System > Venues > Control Venues and set the dimension 1 code as needed.

Finally the details about each wage account needs to be set. Go to Pay > Wage Settings > Pay Element Accounts. Set both the Miracle Global Dimension 2 Code and the Miracle Column Title. The column title is used to determine which columns should be added to the Miracle export format.

Carval*

This format works with the Carval HR software and provides the correct payroll numbers and cost centres to export the data in an importable format.

All staff need their Carval employee references set. Do this under Staff > Staff Settings > Control Staff. You should then see a column for each staff member's Carval Employee reference. Every staff member just needs to have their reference added.

The Carval cost centres are the codes used to link the hourly rates of pay to the department. These are manage via Pay Element Accounts (Pay > Wage Settings > Pay Element Accounts). You'll need a wage account for each Carval cost centre.

Raw Data including shift breakdown

This format will export every time entry for each employee within the wagesheet. It will include an employee number, their names, a name per venue, date worked, start and end times, hours, hourly rate and gross pay.

OCCUPANCY REFFIRST NAMELAST NAMEVENUEWORK ORDERDATESTARTENDHOURSWAGEVALUE
RobabashekhOld Fire Station2017-04-0621:4503:355.827.0541.01
JoyAshurstOld Fire Station2017-04-0620:3003:456.757.0547.59
JoyAshurstOld Fire Station2017-04-0722:3203:385.127.0536.07
StephanieAshurstOld Fire Station2017-04-0722:3003:325.037.0535.49
StephanieAshurstOld Fire Station2017-04-0622:0003:325.557.0539.13
MichaelatkinOld Fire Station2017-04-0622:3003:345.077.0535.72
MichaelatkinOld Fire Station2017-04-0722:3003:335.057.0535.6
SaraBaileyOld Fire Station2017-04-0722:3203:294.957.0534.9
HeatherBrandumOld Fire Station2017-04-0720:3000:183.87.0526.79

Excel Export Format 1

Note: This format can be enabled under Global Settings.

Excel Format 1 is designed to provide a summary of all time entries to be transcribed to another system. It will also highlight staff on permanent and casual contracts.

Hours are shown as a total number of hours that includes holiday and sick pay. The notes column provides a breakdown of holiday and sick page hours.

Yellow rows denotes staff on casual contracts, white rows are everyone else.

Excel Export Format 2

Note: This format can be enabled under Global Settings.

Excel Format 2 provides a breakdown of hours per staff member per venue export name. It allows total costs (not hours) to be seen per cost centre and provides breakdown of working, holiday and sickness costs.

The Venue Export Name is set under each venue and hours from venues with the same export name will be combined. This allows you to create your own cost centres covering one or more venues and have this export display them separately.

Each staff member will be listed with the venue export name and the total costs. If the staff member has worked in multiple venues that have multiple export names then they will be listed several times. If the venues all share the same export name then only one line will be shown.

Excel Export Format 3

Note: This format can be enabled under Global Settings.

Excel Format 1 is designed to provide a summary of all time entries to be transcribed to another system. It will also highlight staff on permanent and casual contracts.

Hours are shown as a total number of hours that includes holiday and sick pay. The notes column provides a breakdown of holiday and sick page hours.

Excel Export Format 4

Note: This format can be enabled under Global Settings.

Excel Format 4 is designed to provide a total cost per venue export name. This can then be used for journal entries.


The total is provided as a cost in pounds (£) for each of the different venue export names.

Any venue with the same export names will be combined into a single line in this export.

This allows different cost centres to be created that might including several venues under one export name.

Excel Export Format 5

Note: This format can be enabled under Global Settings.

This format is designed to provide wagesheet information in a format for import. This format includes the Sage Reference from each employee, their full name, hours to be paid, rate of pay and cost code.

The cost code is taken from the venue where the work was completed. You can set this per venue as the “title for exports”.

Shifts with the same venue, rate of pay and employee will be combined together onto one row. Staff who work multiple pages or for different rates of pay will be listed multiple times.

Excel Export Format 6

Note: This format can be enabled and renamed under Global Settings.

This format simply includes your employee reference number, names, normal pay as a total, holiday pay (if included), an empty advance pay for manual editing and a final gross pay column.

Excel Export Format 7

Note: This format can be enabled and renamed under Global Settings.

This format provides a raw export of rates and decimal hours for each employee. The column names for the rates and hours can be edited under the settings.

In addition, a notes column can be added and automatic notes applied for different contract types.

Each of the employee’s rates of pay will be listed and the totals shown.

Excel Export Format 8

Note: This format can be enabled and renamed under Global Settings.

This format provides a raw export of rates and decimal hours for each employee. The column names for the rates and hours can be edited under the settings.

Each of the employee’s rates of pay will be listed and the totals shown. The file will automatically adjust based on the rates that are included in the file. Only rates of pay that one of the staff members has been paid within that wage sheet will be included.

The file also includes a subtotal column, number of holiday hours and total holiday value. Finally, it also shows a total pay.

Excel Export Format 9

Note: This format can be enabled and renamed under Global Settings.

This format will provide a total number of hours per staff member including the rate of pay, total hours and a grand cost. Staff will have multiple lines if they have multiple rates of pay per wage sheet.

ReferenceStaffRateHours after breaksRow Total
230James Bailey7.354223101.7
230Jesica Bailey7.055283722.4
44Sam barnsdale7.054032841.15
44Sam barnsdale7.354072991.45

Excel Export Format 10

Note: This format can be enabled and renamed under Global Settings.

This format provides an export of all wage sheet costs grouped per venue, cost code 1 and cost code 2.

It is perfect to provide a total value of time sheet per cost code during the wage sheet.

WE 09.04.22
VenueCost Code 1Cost Code 2Total Expenditure
Example 13840.57
Example 23840.57

Excel Export Format 11

Note: This format can be enabled and renamed under Global Settings.

This format creates an Excel file with the following columns:

  • Reference
  • First name
  • Last name
  • Date Worked
  • Pay Element (special reference on each pay element)
  • Hours (decimal)
  • Event Cost Code (if provided)
  • Venue (Using the Title for Exports)
  • Custom Field (Ability to add a custom field to the export)

There will be a single row for each combination of staff, date worked, pay element, cost code and venue.

Reference NumberFirst NameLast NameDate WorkedPay ElementHoursEvent Cost CodeVenue[Custom Field]
10302310AJamesBilal2019-05-29GRADE C3Uno
10302310AJamesBilal2019-05-31GRADE C2.5Uno
0023AAAmyChalakova2019-05-28GRADE C3.25Uno
00013AMLaraCoull2019-05-31GRADE B U256.25Uno

Excel Export Format 12

Note: This format can be enabled and renamed under Global Settings.

This is a cross-charge report that is designed to show the total cost per staff member where they have worked in a different venue from their home venue.

The report includes the employee name, their home venue and then a column for each venue in the system. The report will then include the value of hours under each column where they are not within the home venue.

StaffHome VenueUnoCHOfficeVO
Jake BaileyVO234.56000
James BaileyVO045.4500
Peter BollonsUno0023.23178.45

Combining Wagesheets

If you have created multiple wagesheets for any reason then you are able to combine them into a single file and export it in your preferred format.

Simply use the checkboxes on the View Wagesheet page to select the required wage sheets. Then use the “Combine Selected Wagesheets into Export” option under the Actions menu to download your preferred format.