Report Builder - Calculated Fields

In the Report Builder, there are a number of fields that are calculated by the system and made available in the dataset. Below are explanations of the logic behind each of these fields:

Employee Dataset

• Length of service - This is the employee's leave date minus their start date for leavers OR the date today minus the start date for current employees expressed in whole years (e.g. 4)
• Length of service decimal - This is the employee's leave date minus the leavers' start date OR the date today minus the start date for current employees expressed as a decimal (e.g. 4.23)
• Next anniversary - This is the date of the employee next work anniversary (i.e. their next start date anniversary)
• Next birthday - Thi is the employee's next birthday date
• Age - This is the date today minus the employee's date of birth expressed as whole years (e.g. 28)
• Age decimal - This is the date today minus the employee' date of birth expressed as a decimal (e.g. 28.32)
• Annualised salary - To arrive at this figure, we look at 2 main elements: the employee's pay rate and their working patterns.
  • If the employee is paid per annum, this is simply the per annum cost.
  • If the employee is paid monthly, this is per month cost * 12.
  • If the employee is paid weekly, this is per week cost * 52.
  • If the employee is paid daily, this is per day cost * the number of days the employees are set as working this year. This could be from a regular working pattern or from a custom working pattern (the custom working pattern will always have precedence). If neither of these is set, we will default to 260 days.
  • If the employee is working hourly, this is per hour * the number of hours the employee is set as working this year. This could be from a regular working pattern or from a custom working pattern (the custom working pattern will always have precedence). If neither of these is set, we will default to 1950 hours (37.5 hours per week x 52 weeks).
• Annualised benefits - This is total of all the employee's benefits
• Annualised cost - This is the annualised salary plus the annualised benefits
• Annualised FTE salary - This is the (annualised salary divided by FTE) * 100. For example, annualised salary is 15,000 and FTE is 0.75 so the Annualised FTE salary is (15000 / 0.75) * 100 = 20000. If the FTE is set to 0, then this calculation is not used.
• Annualised FTE cost - This is the annualised FTE salary + annualised benefits.

Training Dataset

• Calculated employee cost - This uses the same calculation as annualised salary above to work out a daily cost for the employee. If the training is 3 days long, we then multiply the daily costs by 3 to get the employee cost.
• Calculated total cost - This the total of the Calculated employee cost and the Calculated course cost.
• Calculated course cost - This is based on 2 elements. First, if you have entered an "Other cost" against the training, then this is included. This could be used, for example, to track the cost of an expense for the training.

When the add this to the course cost, which is worked out based on the unit you have entered for the cost:

• If the training cost unit is per hour, this is the number of days training * cost * 7.5 plus the other cost
• If the training cost unit is per half-day or a day, this is the number of day training * cost plus the other cost
• If the training cost unit is per week, this is the number of days training * cost/5 plus the other cost
• If the training cost unit is per delegate, this is entered cost plus the other cost
• If the training cost unit is Complete price, this will depend on whether the training is linked to other training entries - Training is considered to be lined if you added multiple people to the request. When setting up the training, you entered name 1 then name 2 then name 3, etc. the submitted as a single request.
  • If the training is not linked, this is entered cost plus the other cost
  • If the training is linked, this is the (entered cost/number of participants) plus the other cost. For example, if you entered training with a cost of 5000 and entered 4 people, each person's record would then be costed as 1250.