Getting all direct and indirect reports for each manager using Advanced Reporting
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.
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.
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.
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:
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.
- Calculate the levels of all employees in the company
- 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.
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
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)
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
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.
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.
Thank you so much for this wonderful article. I have couple of questions though, When I try to download the XML file I am not unable to open it as I got error message when I run. Is it possible to do all this on Excel? Could you show us what steps to follow. Since I am using such report always, I would be much appreciated if this can be done in Excel after downloading EC Employee file report for example. I will automate this process using python once I know how to do it in Excel. Thank you so much 🙂
Thanks for your feedback. What tool are you using to open the XML? What error are you getting? The report definition is in XML and can be imported to Report Centre. The output of the report can be XLS or XLSX by using the report distributor. I'm not sure what you mean when you asked if it is possible to do all this in Excel. Could you please clarify this?
Sorry for replying late. I am using Excel to open XML file. I got error saying "cannot be parsed". I have used different tools to open XML file like notepad++ but still the same error pops up. Thanks
I have a scenario where, Employee and Manager records should be stacked in rows. Its a replica of UDF Export. Where, Employee and Manger column will be maintained and the manager record will be reflecting in the actual employee record with his/her manager. How do I achieve this solution ?