Getting all direct and indirect reports for each manager using Advanced Reporting

[sbartoli]

Introduction

There are times when you are asking about a way to have a table with all the employee hierarchy within the company for different reasons. I will highlight at least two scenarios:

• It can help the HR department to view and calculate headcount from each manager level (you can group the counting later by Company, Divisions, Job Classification, etc).


• Maybe an SAP Cloud Platform application (extension) of SuccessFactors needs to display data to every manager but just to his/her direct and indirect reports. It sounds cool but the technical team realized that something is missing from where the application can read all dependencies between employees.

Well, there is a report template from the SAP Help Portal that can help us to achieve the goal.

My idea behind this article is to add content that might help the community to understand better this report so all of us will have more ideas of the advantages we can take from Advanced Reporting to overcome challenges we might face in the future. I like to think the best way to understand something is by putting into practice all the theories you've read. Even though we can import report templates definitions from SAP Help Portal and make adjustments to them depending on our instance configuration, it is always a good practice to make sure we understand the logic behind something that is already built even more if there are custom calculations.

You can always start from scratch and build your report step by step if that fits better to your learning instead of importing a pre-delivered report and start to launch it (you could use it as a starting point theory for what you are trying to achieve). This is the approach I took.

Important: All screenshots/images created by me. Employee's names and their IDs are fake.

Details

The problem: Create a table with all direct and indirect reports for each manager in the company.

The solution: By using Advanced Reporting we can create a report with employees dependencies. There are templates already built by SAP and one of them is the "Employee Hierarchy Report Template (5 Levels)" which will be the starting point.

Let’s go!

The "Employee Hierarchy Report Template (5 Levels)" template can be found on the SAP SuccessFactors People Analytics page. Once you are there, you have to look at the following two sub-sections

Screenshot source: SAP SuccessFactors People Analytics page 1H 2021

Template Canvas Reports: this is where you download the ZIP containing the "Employee Hierarchy Report Template (5 Levels)" definition plus many other templates.

Link to ZIP file containing the templates for Advanced reports. Screenshot source: Template Canvas Reports 1H 2021

Employee Central Advanced Reporting Technical Guide: this is a document with technical information about the templates. Read it! This post is not a replacement for that documentation.

The next step is to import the template, run the report and that’s it! …. Well, not that fast folks. Let’s dig into the foundations behind this.

***Remember, we need a table where we can search for a manager and have all his/her direct and indirect reports.***

Let's break down our problem into small ones (divide-and-conquer paradigm)

First of all, let's define the sentence all direct and indirect reports. The direct and indirect reports of any X manager in the system are those employees from once you start going up in their manager-chain relationships, you reach X manager at some point. Example:

Managers with their direct and indirect reports

**Managers IDs are indicated for later reference in the final result

There are 3 managers in the above image

• Lara Hamilton has a total of 12 reports – 3 direct and 9 indirect


• Casey Leon has a total of 9 reports – 7 direct and 2 indirect


• Jackson Bradley has 2 reports which are just direct reports.

To calculate Lara Hamilton's reports we need to go up levels from all employees in the system until Lara is reached. The number of levels to travel from each employee will depend on the level where the employee is in the company.

Great! So now we have two smaller sub-problems to solve rather than the big one. By combing their solutions, the main problem is solved.

1. Calculate the levels of all employees in the company


2. Calculate the manager for each level

Here we go:

1. Calculate the levels of all employees in the company

How many levels to calculate? It will depend on the levels the company has. For simplicity, let’s say the company we are working for on this article has 4 levels (like the image above).

How do we calculate the levels? It will be calculated based on the manager field, that’s our key

• Level 1 – This is the top of the hierarchy, the employees which don’t have a manager (employe’s manager = NO_MANAGER).


• Level 2 – Employees whose manager’s manager doesn’t exist (employe’s manager.manager = NO_MANAGER).
  *Let’s abbreviate the sentence*


• Level 3 – employee.(manager)x3 doesn’t exist (= NO_MANAGER).


• Level 4 – employee.(manager)x4 doesn’t exist (= NO_MANAGER).

As you might see, the definition of the number of levels we need to go up is (Level Number) – 1

• Level 1 – we need to go up 0 levels because in level 1 there is no manager of the manager.


• Level 2 – we need to go up 1 level to get the manager
  ……


• Level Ν – we need to go N-1 up levels.

Now let’s see how it looks in the reporting tool. In Advanced Reporting, the driven table is going to be Global Job Information (the job info) because from this table we can go up to the manager table and then to another upper and so on (blue tables in below image). For our case, we just need 3 Supervisor tables because we have 4 levels. Date type is set to the current date (as default) to get current records from all tables.

Driving table and Supervisor tables relationship

Employee ID is selected in the driving table, we want to pull all active employees and calculate their levels by using a calculated column with conditional IF – ELSE IF – ELSE statement

Employee level calculation

2. Calculate the manager for each level

The last part will consist of adding manager levels columns to the report, again by using calculated columns to fill in each one the right manager depending on the employee level. We will need 3 managers columns: Manager Level 1, Manager Level 2, and Manager Level 3 (remember, the company level for this article is 4. In case you are facing more levels, you just need to modify the level calculation to add it and add more Manager Level columns)

Manager calculation

Please look at how these three calculations are written. Manager Level 3 calculation is different from Manager Level 1 and Leve 2, all of them work but Manager Level 3 calculation structure is cleaner and it can make the whole sentence with fewer logic operators if you need to include more levels.

Finally, we get our desired table that shows the hierarchy displayed in the org chart

Employee hierarchy

**Employee name is a calculated column that concatenates last name, first name.

You can later schedule the execution of this report using the report distributor (format will be XLS or XLSX). Depending on your requirements it can be sent by email, SFTP, or run offline.

Conclusion

Advanced Reporting can help us to create a table with all the reporting lines for each manager within the company. This output can be later be used by users or some program/interface built in a middleware.

Last but not least, I invite you to go to the following two parts of the Community:

• SAP SuccessFactors People Analytics tag and follow it from there. You can see all the information about reporting gathered so far from questions, comments, and posts. There is a lot to learn and help others too. 


• SAP SuccessFactors topic page from where you can see all the content of your interest that might look interesting for your learning in SuccessFactors.