What are calculated fields in Report builder?
In the Natural HR report builder, there are a number of fields which are calculated by us and made available in the report data source (sometimes referred to as the dataset). Below are explanations of the logic behind each of these fields:
Length of service - this is the employee leave date minus their start date for leavers OR today minus their start date for current employees expressed in whole years. e.g. 4
Length of service decimal - this is the employee leave date minus their start date for leavers OR today minus their 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 - this is the date of the employee next birthday
Age - this is today minus the employee date of birth expressed as whole years e.g. 28
Age decimal - this is 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 two main elements. The employee pay rate and their working pattern.
- 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 * 12
- If the employee is paid daily, this is per day cost * number of days 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 are set we will default to 260 days.
- If the employee is paid hourly, this is per hour * 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 are set we will default to 1950 hours (37.5 x 52).
Annualised benefits - this is a total of all the employee benefits
Annualised cost - this is annualised salary plus 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 (15,000 / 0.75) * 100 which is 20,000. If the FTE is set to 0 then this calculation is not used.
Annualised FTE cost - this is annualised FTE salary + annualised benefits.
Time off dataset
Absence cost - absence cost is ONLY calculated for time off types where authorised is set to NO. Additionally, you need to ensure you have set up your Absence cost settings - a minimum of 1 value is needed (this is usually the Employers tax liability rate (%) which is used for Employers NI for example.
Once populated, this is worked out based on the same logic as annualised salary above. For example, someone is off for 2 days and their salary is 20,000 so we work out a daily cost for that employee and multiply it by 2 and so on.
Calculated employee cost - this uses the same calculation as annualised salary above to work out an hourly or daily cost for the employee. If the training has learning hours entered then we will use the learning hours multiplied by hourly cost for the employee whereas if the training has no learning hours then we multiply daily cost by the number of days to get the employee cost.
Calculated course cost - this is based on two elements. Firstly if you have entered an "Other cost" against the training then this is included - this could be used, for example, to track an expenses cost for the training.
We then 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 day, this is the number of days 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 the 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 linked if you added multiple people to the request - i.e. when setting up the training you entered name 1 then name 2 then name 3 etc and then submitted as a single request.
- If the training is not linked, this is the 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 5,000 and entered 4 people then each person's record would be costed at 1250.
Calculated total cost - this is the total of the Calculated employee cost and the Calculated course cost.
Please sign in to leave a comment.