Settings
For cleaner data, we need to define what are acceptable values in each of the fields. We do this in the Settings sheet.
DO NOT CHANGE COLUMN HEADERS OR FIELD NAMES IN ROW 6. YOU CAN CHANGE IN ROW 5 AND THEN MODIFY NAMES IN POWER BI. EMAIL [email protected] FOR ASSISTANCE.
CHANGING FIELD NAMES WILL BREAK THE POWER BI REPORT
Enter the data for Gender, Hire Source, Ethnicity fields and list Manager Names.
Enter list of Office Locations and the City/State/Country information.
Enter list of Departments and Job classification fields (Job Type, Job Category and Job Level).
Enter list of Job Titles
Enter list of Exit reasons you want to capture from employees who are leaving the company.
Enter Leave related Attributes
Enter which days of the week are weekends and which days are company holidays. For weekends, just enter 1 next to the day. 0 represents a working day and not a weekend.
Define the types of leave and then assign a symbol to each Leave Type.
Please note that the template supports a maximum of 5 leave types.
Enter list of reasons why an employee’s profile will change. For example, when an employee gets promoted, gets a salary increase, or changes teams.
Entering Data in Excel – List of Employees
Enter list of all employees in your company.
click on image to see full size
If you need historical reporting, please make sure that employees from the past are also entered.
- Each employee should have a unique Employee Number.
- Each Employee should have a start date (date when they joined the company) and Date of birth (used for reporting on age)
- Gender and Ethnicity are used for Diversity reporting
- Office Location and Department fields are used in reporting within specific departments and locations.
- Jobs can be classified in 3 levels – Job Type, Job Category, Job level. In addition Job Title defines the employee’s specific job title.
- Manager name will also be a drop-down which was taken as input from the Employee Attributes in the Settings sheet.
Please do not enter more than one record for a single employee.
By Default, you can enter up to 600 employees.
Entering Data in Excel – Employee Updates
When an employee profile changes, please enter the new information in the EMPLOYEE UPDATES sheet.
Choose Employee, enter the effective date of profile change, and reason.
Choose a reason why the employee’s profile changed. For example, when an employee gets promoted or changes teams.
Please note that if it is a new Manager that you'll add in the Employee Updates sheet, ensure that the same is first added in the Settings sheet under the Employee Attributes.
Please do not enter more than one record on the same date for a single employee.
By Default, you can enter up to 3000 employee update records.
Entering Data in Excel – Employee Exits
When an employee leaves the company, please enter in the EXITS sheet.
Please do not enter more than one record on the same date for a single employee.
By Default, you can enter up to 300 employee exits.
Enter Leave
Entering leave data is very easy. In the LEAVE sheet, enter the leave data.
- Choose Employee name.
- Enter Start and End Dates for the leave.
- Choose type of leave.
- The template supports partial leave days as well.
By Default, you can enter up to 2000 leave entries.
Ongoing Data Entry
Now that we have covered all the input points in the Excel file, let’s take a look at when you should update the file with new data.
- When a new employee joins, please add a row to the EMPLOYEES table.
- When a change happens in employee profile (except exit or termination), enter a record in EMPLOYEE UPDATES table
- When an employee leaves, enter a new record in EXITS table
- When an employee plans to take leave, enter the information in LEAVE sheet
For next step on how to set parameters in Power BI, please visit this article
Setting up Parameters and connection in Power BI