• Home
  • Blog
  • Quick Tips for SAP SuccessFactors People Analytics

Quick Tips for SAP SuccessFactors People Analytics

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, 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.

About the Authors

GP Strategies Corporation
GP Strategies is a global performance improvement solutions provider of sales and technical training, e-Learning solutions, management consulting and engineering services. GP Strategies' solutions improve the effectiveness of organizations by delivering innovative and superior training, consulting and business improvement services, customized to meet the specific needs of its clients. Clients include Fortune 500 companies, manufacturing, process and energy industries, and other commercial and government customers.

Get in touch.

Learn more about our talent transformation solutions.

Transformation doesn’t happen overnight if you’re doing it right. We continuously deliver measurable outcomes and help you stay the course – choose the right partner for your journey.

Our suite of offerings include:

  • Consulting Services | Aligning vision and strategy to deliver integrated and systemic business results to drive growth and change through people.
  • Learning Services | Modern learning strategies, content, experiences, and delivery approaches that optimise workforce performance.
  • Technologies | An ecosystem of learning and talent tools, systems, platforms, and expertise that enable learning and talent transformation.






Powered by Translations.com GlobalLink OneLink Software