Employment Data & Analysis System (EDAS) report column mapping, calculations, and validations

The following table describes how Employment Data & Analysis System (EDAS) report column data is mapped to Sparkrock 365 fields as well as how fields are calculated and validation information.

Column No. Name Source of Data and Validations
1 School District Number The Work Location Code field on the Location Card page.
2 Work Location Code Number The Location Code field on the HR Employee Assignment Card is used to link to the Work Location Code field on the Location Card page.
3 General Classification The General Classification field on the HR Position Card page.
4 Rollup Position Code The General Classification field on the HR Position Card page.
5 Employment Status The Type that is associated with the Assignment Type field on the HR Employee Assignment Card page.
The export file record includes one of the following values:
  • P = Permanent
  • T = Temporary
  • C = Casual/TTOC
6 School District Employee ID Number The No. field on the Employee Card page.
7 Unique Identifier - SIN The Social Insurance No. field on the Employee Card page.
8 Date of Birth The Birth Date field on the Employee Card page.
9 Gender Sparkrock 365 uses the following logic to set the value:
  • When the value of the Gender field on the respective Employee Card page is Female, the value is set to F.
  • When the value of the Gender field on the respective Employee Card page is Male, the value is set to M.
  • When the value of the Gender field on the respective Employee Card page is not Male or Female, the value is set to X.
10 Date of Hire The Employment Date field on the Employee Card page.
11 Union Code / Local The Union Code field on the HR Employee Assignment Card page is used to link to the Union Local Code field on the Unions page.
12 School Level The Location Code field on the HR Employee Assignment Card page is used to link to the School Level field on the Location Card page.
The export file record includes one of the following values:
  • 1 = Elementary
  • 2 = Secondary
  • 3 = All Grades
13 Years of Experience Recognition A calculated field that uses the current date and subtracts the Original Hire Date field from the Employee Card page.
14 Current Working Status The EDAS Working Status field on the Causes of Inactivity page.
The export file record includes one of the following values:
  • A = Currently working in a position.
  • L = Employee is on a leave of absence. The leave can be paid or unpaid. SIP or sick leave is reported as L.
  • R = Employee is on layoff or a recall list.
  • D = Employee is on LTD.
  • T = Employee is no longer working with the district. The employment relationship has been severed.
15 Unpaid Sick Days A calculated field that is per HR Emp. Assignment Entry No. and Work Location Code and that is based on the Leave FTE for the one or more causes of inactivity codes that are set up on the EDAS Setup page under 15 Unpaid Sick Days multiplied by the number of working days in the period. To calculate the working days in a period, Sparkrock 365 uses the HR base calendar that is specified on the related HR position. The calculation includes days for SIP and WCB cause of inactivity codes. The calculation does not include days for employees who are on LTD. The value is reset to zero at the beginning of each fiscal period, for example, July 1.
16 Unpaid Sick Hours A calculated field that is per HR Emp. Assignment Entry No. and Work Location Code and that is based on the Leave FTE for the one or more causes of inactivity codes that are set up on the EDAS Setup page under 16 Unpaid Sick Hours multiplied by the number of working hours in the period. To calculate the working hours in a period, Sparkrock 365 uses the HR base calendar that is specified on the related HR position. The calculation includes hours for SIP and WCB cause of inactivity codes. The calculation does not include hours for employees who are on LTD. The value is reset to zero at the beginning of each fiscal period, for example, July 1.
17 Reason for Departure The Reason For Departure field on the ROE Reason Codes page.
The export file record includes one of the following values:
  • A = Active
  • D = Deceased
  • E = Expired Contract or Change of Position.
  • L = Layoff
  • R = Resignation. All codes start with E.
  • S = Retirement. All codes start with G.
  • T = Terminated. All codes start with M.
  • X = On Exchange.
18 Date of Departure The Termination Date field on the Employee Card page.
19 Year to Date Earnings by Position A calculated field that sums the Amount on the Payroll Ledger Entries page for specific payroll codes grouped by the HR Emp. Assignment Entry No. field. To determine the payroll codes, Sparkrock 365 uses the setup on the EDAS Setup page.
20 Full Time Equivalent The Active FTE field on the HR Employee Assignment Card page.
21 Salary Category The setup on the EDAS Setup page is used to link to the Pay Grade Code field on the HR Employee Assignment Card page.
22 BCCT Certification The Qualification Code on the HR Employee Assignment Details page is used to link to the BCCT Certification field on the Qualifications page.
The export file record includes one of the following values:
  • L = Letter of Permission
  • P = Professional
  • B = Basic
  • T = Trades
  • C = Conditional
  • D = Development Standard Term
  • F = First Nations Language
23 Scale Placement The Pay Step Code field on the HR Employee Assignment Details page.
24 Increment Month Populated with 00.
25 Annual Base Salary Populated with 0.
26 Annual Vacation Entitlement Paid Out A calculated field that sums the Quantity on the Payroll Ledger Entries page for specific payroll codes grouped by the HR Emp. Assignment Entry No. field and location code. To determine the payroll codes, Sparkrock 365 uses the setup on the EDAS Setup page. Sparkrock 365 validates the field value to ensure that the the amount is greater than or equal to zero and less than or equal to 65,000.
27 Sick Leave Days Accumulated Populated with 000.
28 Paid Sick Leave Days Used A calculated field that is per HR Emp. Assignment Entry No. and Work Location Code and that involves the following steps:
  1. Calculate the quantity based on the Leave FTE for the one or more causes of inactivity codes that are set up on the EDAS Setup page under 28 Paid Sick Leave Days Used multiplied by the number of working days in the period.
    To calculate the working days in a period, Sparkrock 365 uses the HR base calendar that is specified on the related HR position.
  2. Calculate the quantity for absence registrations based on the causes of absence codes that are set up on the EDAS Setup page under 28 Paid Sick Leave Days Used multiplied by the number of working days in the period.
    To calculate the working days in a period, Sparkrock 365 uses the HR base calendar that is specified on the related HR position.
  3. Add the quantities from step 1 and step 2.
The value is reset at the beginning of each fiscal cycle, for example, July 1st. Sparkrock 365 validates the field value to ensure that the amount is greater than or equal to zero and less than or equal to 135.
29 Teacher Teaching on Call Days A calculated value that uses the HR Emp. Assignment Entry No. on the Compensation Ledger Entries page and the Entry No. field on the HR Employee Assignment Card page. A count of the Date Worked field is used to determine the total number of days. The data for specific HR pay codes is identified based on the setup on the EDAS Setup page.
30 Teacher Teaching on Call Pay Salary A calculated value that uses the HR Emp. Assignment Entry No. on the Compensation Ledger Entries page and the Entry No. field on the HR Employee Assignment Card page. A sum of the Amount field is used to determine the total number of days. The data for specific HR pay codes is identified based on the setup on the EDAS Setup page.
31 Job Type No. The Job Type No. field on the HR Position Card page.
The export file record includes one of the following values:
  • 1 = 10 Months
  • 2 = 12 Months
  • 3 = 11 Months
  • 4 = Other
32 Support Staff Hours A calculated field that sums the Hours field on the Payroll Ledger Entries page grouped by the HR Emp. Assignment Entry No. field. Support staff are determined based on the General Classification field on the HR Position Card page.
33 Support Staff Casual Earnings Including A calculated field that sums the Total Amount field on the Payroll Ledger Entries page for specific HR pay codes grouped by the HR Emp. Assignment Entry No. field. To determine the payroll codes, Sparkrock 365 uses the setup on the EDAS Setup page. Sparkrock 365 validates the field value to ensure that the amount is greater than or equal to zero and less than or equal to 78,000.
34 Hourly Rate of Pay The Amount field on the HR Employee Assignment Card page.
35 Regular Paid Earnings Support Staff A calculated field that sums the Amount field on the Payroll Ledger Entries page grouped by the HR Emp. Assignment Entry No. field.
36 Overtime Hours A calculated field that sums the Hours field on the Payroll Ledger Entries page for specific HR pay codes grouped by the HR Emp. Assignment Entry No. field. To determine the payroll codes, Sparkrock 365 uses the setup on the EDAS Setup page.
37 Overtime A calculated field that sums the Total Amount field on the Payroll Ledger Entries page for specific HR pay codes grouped by the HR Emp. Assignment Entry No. field. To determine the payroll codes, Sparkrock 365 uses the setup on the EDAS Setup page.
38 Annual Vacation Entitlement Paid out - SU A calculated field that sums the Total Amount field on the Payroll Ledger Entries page for specific HR pay codes grouped by the HR Emp. Assignment Entry No. field. To determine the payroll codes, Sparkrock 365 uses the setup on the EDAS Setup page.
39 Sick Leave Hours Accumulated A calculated field that sums the Quantity field on the Payroll Ledger Entries page for specific HR pay codes grouped by the HR Emp. Assignment Entry No. field. To determine the payroll codes, Sparkrock 365 uses the setup on the EDAS Setup page.
40 Paid Sick Leave Hours Used A calculated field that sums the Hours field on the Payroll Ledger Entries page for specific HR pay codes grouped by the HR Emp. Assignment Entry No. field. To determine the payroll codes, Sparkrock 365 uses the setup on the EDAS Setup page.
41 Employer Health Tax (EHT) A calculated field that sums the Total Amount field on the Payroll Ledger Entries page for specific HR pay codes grouped by the HR Emp. Assignment Entry No. field. To determine the payroll codes, Sparkrock 365 uses the setup on the EDAS Setup page.
42 MSP - Coverage Status Not required.
43 Dental A calculated field that sums the Total Amount field on the Payroll Ledger Entries page for specific HR pay codes grouped by the HR Emp. Assignment Entry No. field. To determine the payroll codes, Sparkrock 365 uses the setup on the EDAS Setup page.
44 Dental - Coverage Status The Benefit Plan Status field on the Employee Benefits page.
The export file record includes one of the following values:
  • S = Single
  • C = Couple
  • F = Family
  • O = Opted out
45 EHB A calculated field that sums the Total Amount field on the Payroll Ledger Entries page for specific HR pay codes grouped by the HR Emp. Assignment Entry No. field. To determine the payroll codes, Sparkrock 365 uses the setup on the EDAS Setup page. Sparkrock 365 validates the field value to ensure that the amount is greater than or equal to zero and less than or equal to 7,500.
46 EHB - Coverage Status The Benefit Plan Status field on the Employee Benefits page. When the value of Assignment Type is C, the report displays a null value.
The export file record includes one of the following values:
  • S = Single
  • C = Couple
  • F = Family
  • O = Opted out
47 LTD A calculated field that sums the Total Amount field on the Payroll Ledger Entries page for specific HR pay codes grouped by the HR Emp. Assignment Entry No. field. To determine the payroll codes, Sparkrock 365 uses the setup on the EDAS Setup page.
48 Pension A calculated field that sums the Total Amount field on the Payroll Ledger Entries page for specific HR pay codes grouped by the HR Emp. Assignment Entry No. field. To determine the payroll codes, Sparkrock 365 uses the setup on the EDAS Setup page.
49 Group Life /ADD A calculated field that sums the Total Amount field on the Payroll Ledger Entries page for specific HR pay codes grouped by the HR Emp. Assignment Entry No. field. To determine the payroll codes, Sparkrock 365 uses the setup on the EDAS Setup page.
50 CPP A calculated field that sums the Total Amount field on the Payroll Ledger Entries page for specific HR pay codes grouped by the HR Emp. Assignment Entry No. field. To determine the payroll codes, Sparkrock 365 uses the setup on the EDAS Setup page.
51 EI A calculated field that sums the Total Amount field on the Payroll Ledger Entries page for specific HR pay codes grouped by the HR Emp. Assignment Entry No. field. To determine the payroll codes, Sparkrock 365 uses the setup on the EDAS Setup page.
52 WorkSafeBC Top Up A calculated field that sums the Total Amount field on the Payroll Ledger Entries page for specific HR pay codes grouped by the HR Emp. Assignment Entry No. field. To determine the payroll codes, Sparkrock 365 uses the setup on the EDAS Setup page.
53 Short-term Disability A calculated field that sums the Total Amount field on the Payroll Ledger Entries page for specific HR pay codes grouped by the HR Emp. Assignment Entry No. field. To determine the payroll codes, Sparkrock 365 uses the setup on the EDAS Setup page. Sparkrock 365 validates the field value to ensure that the amount is greater than or equal to zero and less than or equal to 2,500.
54 Maternity Leave Top-Up A calculated field that sums the Total Amount field on the Payroll Ledger Entries page for specific HR pay codes grouped by the HR Emp. Assignment Entry No. field. To determine the payroll codes, Sparkrock 365 uses the setup on the EDAS Setup page.
55 Isolation Allowance A calculated field that sums the Total Amount field on the Payroll Ledger Entries page for specific HR pay codes grouped by the HR Emp. Assignment Entry No. field. To determine the payroll codes, Sparkrock 365 uses the setup on the EDAS Setup page. Sparkrock 365 validates the field value to ensure that the amount is greater than or equal to zero and less than or equal to 15,000.
56 SIP Allowance A calculated field that sums the Total Amount field on the Payroll Ledger Entries page for specific HR pay codes grouped by the HR Emp. Assignment Entry No. field. To determine the payroll codes, Sparkrock 365 uses the setup on the EDAS Setup page. Sparkrock 365 validates the field value to ensure that the amount is greater than or equal to zero and less than or equal to 2,300.
57 Mileage Allowance A calculated field that sums the Total Amount field on the Payroll Ledger Entries page for specific HR pay codes grouped by the HR Emp. Assignment Entry No. field. To determine the payroll codes, Sparkrock 365 uses the setup on the EDAS Setup page.
58 Positions of Special Responsibility (POSR Allowances) A calculated field that sums the Total Amount field on the Payroll Ledger Entries page for specific HR pay codes grouped by the HR Emp. Assignment Entry No. field. To determine the payroll codes, Sparkrock 365 uses the setup on the EDAS Setup page.
59 Other Allowances A calculated field that sums the Total Amount field on the Payroll Ledger Entries page for specific HR pay codes grouped by the HR Emp. Assignment Entry No. field. To determine the payroll codes, Sparkrock 365 uses the setup on the EDAS Setup page.
60 Education Leave - Paid A calculated field that sums the Total Amount field on the Payroll Ledger Entries page for specific HR pay codes grouped by the HR Emp. Assignment Entry No. field. To determine the payroll codes, Sparkrock 365 uses the setup on the EDAS Setup page.
61 Paid Union Leave A calculated field that sums the Total Amount field on the Payroll Ledger Entries page for specific HR pay codes grouped by the HR Emp. Assignment Entry No. field. To determine the payroll codes, Sparkrock 365 uses the setup on the EDAS Setup page.
62 Unpaid Union Leave Days A calculated field that is per HR Emp. Assignment Entry No. and Work Location Code and that involves the following steps:
  1. Calculate the quantity based on the Leave FTE for the one or more causes of inactivity codes that are set up on the EDAS Setup page under 62 Unpaid Union Leave Days multiplied by the number of working days in the period.
    To calculate the working days in a period, Sparkrock 365 uses the HR base calendar that is specified on the related HR position.
  2. Calculate the quantity for absence registrations based on the causes of absence codes that are set up on the EDAS Setup page under 62 Unpaid Union Leave Days multiplied by the number of working days in the period.
    To calculate the working days in a period, Sparkrock 365 uses the HR base calendar that is specified on the related HR position.
  3. Add the quantities from step 1 and step 2.
63 Unpaid Union Leave Hours A calculated field that is per HR Emp. Assignment Entry No. and Work Location Code and that involves the following steps:
  1. Calculate the quantity based on the Leave FTE for the one or more causes of inactivity codes that are set up on the EDAS Setup page under 63 Unpaid Union Leave Hours multiplied by the number of working hours in the period.
    To calculate the working hours in a period, Sparkrock 365 uses the HR base calendar that is specified on the related HR position.
  2. Calculate the quantity for absence registrations based on the causes of absence codes that are set up on the EDAS Setup page under 63 Unpaid Union Leave Hours multiplied by the number of working hours in the period.
    To calculate the working hours in a period, Sparkrock 365 uses the HR base calendar that is specified on the related HR position.
  3. Add the quantities from step 1 and step 2.
64 Parenthood or Parental Paid Days of Leave of Absence A calculated field that is per HR Emp. Assignment Entry No. and Work Location Code and that involves the following steps:
  1. Calculate the quantity based on the Leave FTE for the one or more causes of inactivity codes that are set up on the EDAS Setup page under 64 Parenthood or Parental Paid Days of Leave of Absence multiplied by the number of working days in the period.
    To calculate the working days in a period, Sparkrock 365 uses the HR base calendar that is specified on the related HR position.
  2. Calculate the quantity for absence registrations based on the causes of absence codes that are set up on the EDAS Setup page under 64 Parenthood or Parental Paid Days of Leave of Absence multiplied by the number of working days in the period.
    To calculate the working days in a period, Sparkrock 365 uses the HR base calendar that is specified on the related HR position.
  3. Add the quantities from step 1 and step 2.
65 Parenthood or Parental Paid Hours of Leave of Absence A calculated field that uses the Quantity for records that appear on the Absence Registration page based on the cause of absence code setup on the EDAS Setup page grouped by HR Emp. Assignment Entry No. field and Location Code.
66 Parental/Maternity Days A calculated field that is per HR Emp. Assignment Entry No. and Work Location Code and that is based on the Leave FTE for the one or more causes of inactivity codes that are set up on the EDAS Setup page under 66 Parental/Maternity/Adoption Days multiplied by the number of working days in the period. To calculate the working days in a period, Sparkrock 365 uses the HR base calendar that is specified on the related HR position.
67 Parental/Maternity/Adoption Hours A calculated field that is per HR Emp. Assignment Entry No. and Work Location Code and that involves the following steps:
  1. Calculate the quantity based on the Leave FTE for the one or more causes of inactivity codes that are set up on the EDAS Setup page under 67 Parental/Maternity/Adoption Hours multiplied by the number of working hours in the period.
    To calculate the working hours in a period, Sparkrock 365 uses the HR base calendar that is specified on the related HR position.
  2. Calculate the quantity for absence registrations based on the causes of absence codes that are set up on the EDAS Setup page under 67 Parental/Maternity/Adoption Hours multiplied by the number of working hours in the period.
    To calculate the working hours in a period, Sparkrock 365 uses the HR base calendar that is specified on the related HR position.
  3. Add the quantities from step 1 and step 2.
68 Compassionate / Bereavement Leave Days A calculated field that is per HR Emp. Assignment Entry No. and Work Location Code and that involves the following steps:
  1. Calculate the quantity based on the Leave FTE for the one or more causes of inactivity codes that are set up on the EDAS Setup page under 68 Compassionate / Bereavement Leave Days multiplied by the number of working days in the period.
    To calculate the working days in a period, Sparkrock 365 uses the HR base calendar that is specified on the related HR position.
  2. Calculate the quantity for absence registrations based on the causes of absence codes that are set up on the EDAS Setup page under 68 Compassionate / Bereavement Leave Days multiplied by the number of working days in the period.
    To calculate the working days in a period, Sparkrock 365 uses the HR base calendar that is specified on the related HR position.
  3. Add the quantities from step 1 and step 2.
69 Compassionate / Bereavement Leave Hours A calculated field that is per HR Emp. Assignment Entry No. and Work Location Code and that involves the following steps:
  1. Calculate the quantity based on the Leave FTE for the one or more causes of inactivity codes that are set up on the EDAS Setup page under 69 Compassionate / Bereavement Leave Hours multiplied by the number of working hours in the period.
    To calculate the working hours in a period, Sparkrock 365 uses the HR base calendar that is specified on the related HR position.
  2. Calculate the quantity for absence registrations based on the causes of absence codes that are set up on the EDAS Setup page under 69 Compassionate / Bereavement Leave Hours multiplied by the number of working hours in the period.
    To calculate the working hours in a period, Sparkrock 365 uses the HR base calendar that is specified on the related HR position.
  3. Add the quantities from step 1 and step 2.
70 Unpaid Compassionate Leave Days (as per ES Agreement and TC Agreement) A calculated field that uses the Quantity for records that appear on the Absence Registration page based on the cause of absence code setup on the EDAS Setup page grouped by HR Emp. Assignment Entry No. field and Location Code.
71 Unpaid Compassionate Leave Hours (as per ES Agreement and TC Agreement) A calculated field that uses the Quantity for records that appear on the Absence Registration page based on the cause of absence code setup on the EDAS Setup page grouped by HR Emp. Assignment Entry No. field and Location Code.
72 Partial Medical Leave FTE A calculated field that uses the Quantity for records that appear on the Absence Registration page based on the cause of absence code setup on the EDAS Setup page grouped by HR Emp. Assignment Entry No. field and Location Code.
73 Other Paid Leaves Outlined in the Collective Agreement Days A calculated field that uses the Quantity for records that appear on the Absence Registration page based on the cause of absence code setup on the EDAS Setup page grouped by HR Emp. Assignment Entry No. field and Location Code.
74 Other Paid Leaves Outlined in the Collective Agreement Hours A calculated field that uses the Quantity for records that appear on the Absence Registration page based on the cause of absence code setup on the EDAS Setup page grouped by HR Emp. Assignment Entry No. field and Location Code.
75 Other Unpaid Leaves Outlined in the Collective Agreement Days A calculated field that is per HR Emp. Assignment Entry No. and Work Location Code and that involves the following steps:
  1. Calculate the quantity based on the Leave FTE for the one or more causes of inactivity codes that are set up on the EDAS Setup page under 75 Other Unpaid Leaves Outlined in the Collective Agreement Days multiplied by the number of working days in the period.
    To calculate the working days in a period, Sparkrock 365 uses the HR base calendar that is specified on the related HR position.
  2. Calculate the quantity for absence registrations based on the causes of absence codes that are set up on the EDAS Setup page under 75 Other Unpaid Leaves Outlined in the Collective Agreement Days multiplied by the number of working days in the period.
    To calculate the working days in a period, Sparkrock 365 uses the HR base calendar that is specified on the related HR position.
  3. Add the quantities from step 1 and step 2.
76 Other Unpaid Leaves Outlined in the Collective Agreement Hours A calculated field that is per HR Emp. Assignment Entry No. and Work Location Code and that involves the following steps:
  1. Calculate the quantity based on the Leave FTE for the one or more causes of inactivity codes that are set up on the EDAS Setup page under 76 Other Unpaid Leaves Outlined in the Collective Agreement Hours multiplied by the number of working hours in the period.
    To calculate the working hours in a period, Sparkrock 365 uses the HR base calendar that is specified on the related HR position.
  2. Calculate the quantity for absence registrations based on the causes of absence codes that are set up on the EDAS Setup page under 76 Other Unpaid Leaves Outlined in the Collective Agreement Hours multiplied by the number of working hours in the period.
    To calculate the working hours in a period, Sparkrock 365 uses the HR base calendar that is specified on the related HR position.
  3. Add the quantities from step 1 and step 2.
77 District Position Title The HR Position Name field on the HR Employee Assignment Card page.

See also

Employment Data & Analysis System (EDAS) reporting
Employment Data & Analysis System (EDAS) report data exchange definition

Feedback

To send feedback about this page, select the following link:

feedback-product-docs@sparkrock.com