This guide will show you how to create a report that models pay increases, shows percentage increases and includes department tables.
Building the Report
- Go to Reporting/Reports > Report Admin > Report Designer and select Insert New Record, and select Design New Report
- Follow these steps along each page of the workflow wizard:
1. Design a New Report
2. Select Personal Details as the master table on the left-hand side of your screen and insert the relevant fields listed in the table below:
| Note: Once you have selected Personal Details as your Master Table, do not change it. The other tables you will need can be found at the bottom of the list on the left-hand side. |
| Table | Field Name | Option |
|---|---|---|
| Personal Details | Basic Pay | Compulsory |
| Personal Details | Job Title | Compulsory |
| Personal Details | Name | Compulsory |
| Personal Details | Review Date | Optional |
| Department | Department | Compulsory |
| Pay Basis | Annual Conversion | Optional |
| Pay Basis | Description | Optional |
| Note: 'Department' (OrgUnit1) may be called something different in your system; this is a configurable field. |
3. You do not need to select any data rules
4. Do not include an additional table
5. Include a standard set of current staff
| Note: If you only want to include selected employees, select the Use a subset option, which will allow you to select the desired Subset each time when running the report. |
6. Do not include a filter form
7. Finish your report by including a title, description and selecting a report group
The downloaded Excel document will have 3 worksheets included at the bottom. Once you have enabled editing on your spreadsheet, use the second worksheet called 'Base Data' to include the formulas mentioned below. You can use the Report worksheet to create a Pivot Table.
Increasing pay by a fixed amount
- To simulate a £500 pay increase for each employee, create a new column (adjacent to the last column used) and use the formula below. To change the amount, substitute the number 500 in the formula for your given amount
=[@[Basic Pay]]+500
- To work out what percentage increase this would be, create a new column and use the formula below
=([@[Proposed Pay]]-[@[Basic Pay]])/[@[Basic Pay]]
Increasing pay by a percentage
- To simulate a 3% pay increase for each employee, create a new column and use the formula below. To change the percentage amount, substitute the number 3 for your percentage amount
=[@[Basic Pay]]*103%
- To work out what the amount of this increase would be, create a new column and use the formula below
=[@[Basic Pay]]*3%
- Format your new column(s) as currency
Reporting on department totals
Now that you have calculated the fields you need as base data, you can use a Pivot Table to show your department's totals.
- To include the new fields in your Pivot Table on your Report worksheet, go to Options and Refresh your field list
- Drag the fields from your Pivot Table Field List to the appropriate boxes at the bottom. You can follow the image on the right to see a report based on the increase by department
| Note: You may want to change the presentation of the report by including Employee names in your Pivot Table or by creating a chart before using check in to save the formatting in HR. |
- Once happy with your report, click Edit Settings to enable it and grant access to the appropriate user roles for them to be able to run it from the Reports menu. See the linked guides for more details
| Note: This report can help you simulate pay increases for reviews. To make changes to pay records, you will need to complete a Pay Review in HR. |
Footer
Comments
0 comments
Article is closed for comments.