Please enable JavaScript.
Coggle requires JavaScript to display documents.
BambooHR dashboard automation Documentation - Coggle Diagram
BambooHR dashboard automation Documentation
employees_directory
Data in employees directory needed to be unnested, otherwise we could not work with that data, so created from that data set: EMPLOYEES DIRECTORY UNNESTED
From Employees Directory Unnested, we created another table to do the following calculations. The table is called JOINED EMPLOYEE DIRECTORY UNNESTED CALCULATED SALARIES
FIRST: did a left join of different data sets to appear in 1 table. This way we match everything via employee ID from both tables. We also removed all the cases that appear null in department or location.
Employee directory
Calculated salaries
THIRD: Calculations
Contract Agreement
I added the location of Malta are Maltese based employees
Except James Bason (ID 312) since he is a SP
The location Germany are Startdowns or Digital World
I added that all the rest are Service Providers
SECOND: showing data
employee ID
From the table employee directory
Name
From the table employee directory
Manager
From the table employee directory
Location
From the table employee directory
Department
From the table employee directory
Division
From the table employee directory
Salary Original
From the calculated salaries table
This is the salary number appearing in Bamboo without any transformation
Job title
From the table employee directory
Currency
From the calculated salaries table
Salary euro
From the calculated salaries table
This is all salaries calculated in euros
Day rate euro
From the calculated salaries table
All salaries calculated in euro and per day
Hire date
From the calculated salaries table
Status
From the calculated salaries table
Active or Inactive: showing the employees that in Bamboo HR are active or terminated (inactive)
Custom report
Here we have created 2 tables to operate the salaries, which the end result is the creation of the table Calculated Salaries ( and used here below in employees directory unnested)
First table (called temporary table and starting WITH), given the name SPLIT _RATE. It is taking the data from the data set of custom report
We select the columns
Employee ID
Hire Date
status
Second table (after From and starting with Select)
We select the following columns to show, by using the data from Split Rate (First table here above)
Salary Original
coming from rate
Currency
employee_id
Hire date
Status
We create a case(IF in excel): called Salary Euro
USD = 0.89*rate
GBP = 1.17*rate
All the salaries in euro we leave them as rate (doesnt make any calculations)
We finish the case with END as salary_euro
Then we want to make the case above as a day rate, so we need to take all the case expression and divide it by 260 (working days) and then we add a comma to round it by 2 decimals.
Employee changes
Time off requests