Salary change reports are key reports for HR, payroll and compliance teams. Employee Central Adhoc reports can easily provide current wage data, but cannot a report with provide both the current wage, as well as the previous wage, for comparison.
This is the final output of the report, showing current wages, and also the previous wages.
To begin creating this report:
- Goto Analytics
- Create a new Report
- Choose Global Job Information as the driving table
- Select the fields you want to see in your report (Name, ID Location, Recurring Pay Component etc.)
Select Base pay as a Secondary Table and Select the the fields you want to see in your report (Amount, Effective End Date, Effective Start Date) Effective Start and End dated are required to report on new and previous wages.
Once you have selected the fields you want in the report, COPY this table by clicking on the tab and copying table.
The table Base Pay is duplicated and will contain CURRENT wage data- we will change this in the next step
The copied table is identical, except for the name Base Pay (2)
Your Tables should look now look like this- a Global Job Info table- and two Base Pay tables. If the report is run, you will see duplicate fields. In the next few steps, we will use the Base Pay table to show CURRENT wage date and modify the Base Pay (2) Table to filter and display all wage data.
Modifying the Tables
In order to compare the current and historical data – the Base Pay (2) table will have to be modified to pull all wages current and past, not just the default value of current wage data.
Effective dated options are managed in the top menu through the Date Options (calendar icon).
Ensure that the Global Job Information and Base Pay Tables are set to default (Current Date). No changes need to be to these tables.
he default value of “Current” should be overwritten for Base Pay (2) to “SHOW ALL” compensation values. This will show the current wage history, plus all historical wage history for each employee.
Now, your data will be set up to compare:
-
Base Pay – will have current wage data only
-
Base Pay (2) will have ALL wage data (current plus historical) for each employee.
Calculated Column
Behind the scenes, the current wage will always have a 12/31/9999 end date. By calculating the difference in days between the start date of the current wage, and the end date of all the other wages, you will be able to determine the immediately preceding wage –the difference will always equal 1.
A calculated column is added to filter out all historical wages other than the wage value immediate preceding the current value. This is done by filtering on dates.
All other dates which have a value larger than 1 can be discarded.
For example:
An employee has a new hourly wage of $20 beginning on 2/1/2019 and her previous wages was $15 an hour, beginning on 1/1/2017 and ending on 1/31/2019.
| Start Date | Amount | End Date |
|---|---|---|
| 1/1/2017 | 15 | 1/31/2019 |
| 2/1/2019 | 20 | 12/31/9999 |
The topmost, or most recent wage will always have a 12/31/9999 end date. This rows start date (2/1/2019) will be used to determine the immediately preceding wage row, ($15 hour) as its end date (1/31/2019) will always be one day earlier that the start date (2/1/2019) of the current row. We will use this effective dating design to help us filter out all other historical wage changes.
Effective Start Date(BasePay) – Effective End Date(Base Pay (2))=1
The calculated column will now have a 1 if the dates are separated by 1 day, otherwise the value will be a “2”. Now a filter will be set up to only show a value of 1 for this column.
Filtering
The data will need to be filtered to narrow the report constraints:
-
Active Employees
-
Specified Business Unit
-
Base Wage Change since the beginning of last Month
-
Display current wage and wage immediately preceding- no other historical wage data.
Renaming Columns
Column headers can be renamed to better match the data and reporting expectations.
Sort Columns by Last Modified Date
Report Output
We are finished!
Note the difference in the start date of the current wages and the end date of the historical wages, they are separated by one day.
