Calculating employee reporting levels to their respective managers from employee table

[Ritnoa]

In the above image need to write a script to calculate the number of employees reporting to their respective managers including their Level. So we have 9 levels here showing number of employees reporting in each level.

E.g. Level3 has 1 employee reporting

Level4 has 11 employees reporting

Columns are 

Employee_ID   Employee_Name    LineManager_ID    LineManager_Name   Emp_Band   

[jwjackso · Accepted solution]

Probably an easier way, but I used the HierarchyBelongsTo with the data below:

Data:
Load * Inline [
LineManager_ID,Employee_ID,Employee_Name
P1,P2,'N2'
P2,P3,'N3'
P2,P4,'N4'
P2,P5,'N5'
P3,P6,'N6'
P3,P7,'N7'
P4,P8,'N8'
P4,P9,'N9'
P5,P10,'N10'
P5,P11,'N11'
P10,P12,'N12'
];

PC_Tree:
HierarchyBelongsTo(Employee_ID,LineManager_ID,Employee_Name,AncestorID,AncestorEmployee_Name,DepthID)
Load LineManager_ID,
Employee_ID,
Employee_Name
Resident Data;

PC_Tree_Temp:
load max(DepthID) as MaxDepthID
Resident PC_Tree;

Let vMaxDepthID=Peek('MaxDepthID',0,'PC_Tree_Temp');

[Ritnoa]

Thanks for the solution, I got 89k records and actual records are 11,196.However I tried using Hierarchy function it worked. 

HR:

Load*From HR.xlsx

OE:
Hierarchy (Emp_ID, Manager_ID,Layer)
LOAD
Global_Employee AS Emp_ID,
Line_Manager AS Manager_ID,
[Global Employee] AS Layer

Resident HR;

So i already go the layers, i.e. L1, L2, L3 through Hierarchy function .... i need to use them to create the below chart. Any suggestions.

Regards,

Sameer.

[Saravanan_Desingh]

Like this?

[Ritnoa]

Perfecto !!!

Could you please let me know how to achieve this.

Regards,

Sameer

[Saravanan_Desingh · Accepted solution]

Try this.

[Saravanan_Desingh · Accepted solution]

I forgot to share the expression.

%

Sum(Volume)/Sum(TOTAL Volume)

2nd%

-Sum(Volume)/Sum(TOTAL Volume)

3rd

Background Color: (for % and 2nd%)

Color(2)

[Saravanan_Desingh]

Input data:

tab1:
LOAD * INLINE [
    Level, Volume
    L1, 0
    L2, 0
    L3, 1
    L4, 11
    L5, 31
    L6, 436
    L7, 1484
    L8, 1008
    L9, 65
];