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.

The Challenge

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:

The Solution

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.

Final Result

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.