Dos and Donts to report on compensation data of employees with multiple pay components

[FrankErle]

Introduction

Typically, employees have multiple pay components and there is often a requirement for a report to show the amounts ​​of these pay components in different columns. This means that the requirement is that an employee should appear in a single row of a report output and, for example, the first column should report the amount of the base salary, the second column should report the amount of another pay component, and so on. If an employee does not receive a certain pay component, the amount of this entry should be zero.

Figure 1 – Example of an employee with 5 pay components

Figure 1 shows as an example the employee “Rachana Ahmad” with 5 pay components. The aim would therefore be for a report to report “Rachana Ahmad” in a single row and the various pay components in 5 or more columns.

A frequently used report design is illustrated in Figure 2. The objects “Compensation” and “Pay Component” are joined multiple times in the underlying query, with a filter being defined for exactly one pay component. Very poor report performance was observed on several occasions, particularly when up to 20 joins of “Compensation” => “Pay Component” were used in production instances. This is not least because in this case the permission provider is called separately.

Figure 2 – Non-performing query with 5 joins of „Compensation“ => „Pay Component“

The output is of course in the desired format (see Figure 3). However, this design should be avoided, especially because the identical output can easily be achieved by using very simplified queries.

Figure 3 – Report output of the non-performing query from Figure 2

This simplified query, as illustrated in Figure 4, uses a single instance of "Compensation" => "Pay Component".

Figure 4 – Simplified query with single join of „Compensation“ => „Pay Component“

Option 1

First of all, such a query naturally returns one row for each combination of employee and pay component (Figure 5).

Figure 5 – Report Output of the simplified query

However, as you see in Figure 6 if you use the table type “Cross-Tab” and use “Pay Component” as a column, you get an output that is very similar to the report output in Figure 3.

Figure 6 – Report output as „Cross-Tab“ with „Pay Component“ as Column

Option 2

Another option is to define a calculated column for each pay component based on the simplified query (Fig. 4), each of which contains the value of an individual pay component (Fig. 7). If, for example, you want to have 5 columns for the 5 pay components, you would have to define 5 calculated columns accordingly. 

Figure 7 – Calculated Column to derive the amount of each individual pay component

Figure 8 illustrates the report output. You can see that the report output is very similar to the output of option 1.

Figure 8 – Report output based on the example with 5 calculated columns

Option 3

Another option is to display the employees in one table and the pay components in a second table. These two tables are linked using "Link Analysis" so that by clicking on a row in the upper table, the lower table displays the pay components of the selected employee (Figure 9). This design also has the advantage that the upper table displays the respective number of the employee's pay components. It would also be possible to display the total sum of the accumulated pay components in a simple way in the upper table.

Figure 9 – Report output of 2 tables, connected via „Link Analysis“