Canvas/Employee Central Advanced Reporting: How to return a single row for each employee while reporting on multiple pay components
I hold training sessions and consult with clients on Canvas/Advanced Reporting and one of the most frequently requested topics that clients want to know more about is how to return a single row for each employee on a report that includes Pay Components. In this blog post, I’ll show you how to do just that in the easiest and most efficient way.
Each pay component and corresponding monetary amount is stored on its own row in the compensation table. So, when you create a query that includes specific pay component amounts for each employee who has multiple pay components, multiple rows will be returned. Here’s an example for a single employee:
To generate a report that shows an employee’s multiple pay components on a single line, use two features that aren’t too frequently used in Advanced Reporting: Table Duplication and Table Restrictions (or Table Filters).
Build Your Query
Start with the Job Information table, and then pull in Compensation:
Next, duplicate the Compensation table by clicking on the Edit Table icon:
And then click Duplicate Table:
Note: You cannot duplicate the first table that is added to a query, which is why we didn’t begin the query with the Compensation table.
You now have a second version of the Compensation table that is a replica of the first, including the same selected columns. Both of these Compensation tables are linked directly to Job Information:
For this example, Employee 455 has three Pay Components. In this case, you will need to duplicate the Compensation table to create a third:
Let’s pause and look at the results at this point.
Whoa—we’ve gone from three rows for this employee to … many more. There are 27 in total—for the mathematics fans reading this, each combination of those three pay components are returned: 3 x 3 x 3 = 27 rows.
Fear not—we’ll pare it back down!
Modify the Duplicated Tables
There are a couple more steps to reach our goal of a single row for each employee.
First, take a step back and note the ID/Name pairs for each of the Pay Components. We will use the ID in the next step:
- 28 = BASIC BZ
- 36 = Childcare Allowance Brazil
- 119 = Transportation Allowance
Tip: The ID/Name pairs will vary for each implementation; be sure to note the specific ID/Name pairs for your instance.
For each Compensation table, use Table Restrictions so that only one Pay Component Amount is returned.
Start with the first Compensation table by clicking the Edit Table icon:
Then, click the Edit Restrictions filter icon:
Filter Designer is returned, which looks just like the Query Filter. Conceptually it is no different than the Query Filter, but this filter will be applied only to this Compensation table.
Here is the Table Restriction I’ve set up for BASIC BZ, which corresponds to ID 28:
Now, we require a couple of edits for this table. Rename the Amount column to BASIC BZ. Then remove the Pay Component and Pay Component (Name) fields. The result:
Finally, repeat these Modify the Duplicate Tables steps for Compensation (2) and Compensation (3) tables.
Once you’ve made the above modifications to the remaining tables, your query will look something like this:
And more importantly, your result set is as follows:
Yes! The bliss of a single row for each employee!
Thanks for reading—hopefully you learned something new. Look for more to come!
If you have any questions, please feel free to reach out to me directly.
Great stuff. Was looking this for quite a while for wagetype reporting.
Great Effort and Nice Solution
I want to add an alternative solution that I went through which is creating number of custom fields with the same number of Pay Components needed for reporting in Compensation Information. then creating a business rule that fill the custom fields with the equivalent pay component amount in Recurring Pay Components.
This makes the pay component amounts as fields so they can be used normally in add-hoc report.
Excellent solution and extremely clear instructions. This has solved an issue that we were using Excel to mitigate and how have built it directly into reporting.
Thanks a lot. This was very well explained.
I need to do the same using Detailed Reporting in ORD, but it doesn't appear to be possible. We have three pay component to display on one single row.
How do I include employees who have some or none of these pay components and show a "0" value?