I have a SQL query that I used with SSRS for creating a hierarchical employee report. SSRS had the ability to have recursive parent groupings, which explains the following table structure of the data. This is how I organized my hierarchical employee list table: EmpNTID name RMNTID TopManagerEmpNTID Level 1000 Carmen 0 1001 Annette 1000 1000 1 1002 Shireen 1000 1000 1 1003 Jeffrey 1000 1000 1 1004 Eric 1001 1000 2 1005 Alton 1002 1000 2 1006 John 1002 1000 2 1007 Lynn 1003 1000 2 1008 Abhishek 1003 1000 2 1009 Alan 1004 1000 3 1010 Courtney 1004 1000 3 1011 Robert 1004 1000 3 1012 Michael 1004 1000 3 This is how I am creating my employee table: (Thanks @ValNik) with r as( select 0 as lvlN ,EmpNTID as TopManagerId,EmpNTID,name,RMNTID ,cast(concat(name,':') as varchar(1000)) path from Hierarchy where TopManagerEmpNTID is null union all select lvlN+1 as lvlN ,TopManagerId,t.EmpNTID,t.name,t.RMNTID ,cast(concat(path,'->',t.name) as varchar(1000)) path from r inner join Hierarchy t on t.RMNTID=r.EmpNTID and r.EmpNTID<>t.EmpNTID ) We've moved on from SSRS to using Angular/Highcharts for reporting. The output needs to be in a tabular format so we don't want to use the HighCharts Organization chart. The Manager can have other managers below them or just have a set of direct reports. So looking at my sample data set, Carmen has 3 Managers and 2 of those managers have only direct reports while one of those managers has a manger below them. I need to be able to have a table that starts by only showing Carmen (there is going to be other data shown next to Carmen's name as well as the other employee's names). Clocking on Carmen will show Annette, Shireen, and Jeffrey. Clicking on Annette will show Eric, etc. + Carmen + Annette + Eric Alan Courtney Robert Michael + Shireen Alton John + Jeffrey Lynn Abhishek How can I do this using Angular Material or HighCharts? Do I need to restructure my data and if so, how? Continue reading...
