Hiding root element of Google Organizational Chart in ASP.Net

Last Reply on Sep 20, 2016 12:05 AM By PK257

Posted on Aug 22, 2016 06:40 AM

I tried making organogram for my company using this link- http://www.aspsnippets.com/Articles/Create-Organizational-Chart-from-Database-using-Google-Organizational-Chart-in-ASPNet.aspx  
It worked nicely. But when I tried filtering the list of employees department wise, root element's employee ID (who is not a part of the department but he is the reporting to employee of the 2nd level employee) is shown. How to hide it?

Posted on Sep 20, 2016 12:05 AM Modified on on Sep 20, 2016 01:28 AM

Hi dharmendr,

I got one new but tricky solution. I am following this steps:

1) create temporary table with an extra field-temp.

2) insert values from Employee table in it. Insert actual Repto_ID in temp column.

3) Update ReportingManager = NULL where Temp = NULL

4) Select Query. 

It works fine. Thanks a lot for your help :)

Following is the query: 

CREATE TABLE [dbo].[EmployeesHierarchy](
 [EmployeeId] [int] IDENTITY(1,1) NOT NULL,
 [Name] [varchar](100) NULL,
 [Designation] [varchar](30) NULL,
 [ReportingManager] [int] NULL

SET IDENTITY_INSERT [dbo].[EmployeesHierarchy] ON 

INSERT [dbo].[EmployeesHierarchy] ([EmployeeId], [Name], [Designation], [ReportingManager]) VALUES (1, N'Nancy Davolio', N'Sales Representative', 5)
INSERT [dbo].[EmployeesHierarchy] ([EmployeeId], [Name], [Designation], [ReportingManager]) VALUES (2, N'Andrew Fuller', N'CEO', 3)
INSERT [dbo].[EmployeesHierarchy] ([EmployeeId], [Name], [Designation], [ReportingManager]) VALUES (3, N'Janet Leverling', N'MD', NULL)
INSERT [dbo].[EmployeesHierarchy] ([EmployeeId], [Name], [Designation], [ReportingManager]) VALUES (4, N'Margaret Peacock', N'Sales Representative', 5)
INSERT [dbo].[EmployeesHierarchy] ([EmployeeId], [Name], [Designation], [ReportingManager]) VALUES (5, N'Steven Buchanan', N'Sales Manager', 2)
INSERT [dbo].[EmployeesHierarchy] ([EmployeeId], [Name], [Designation], [ReportingManager]) VALUES (6, N'Michael Suyama', N'Sales Representative', 8)
INSERT [dbo].[EmployeesHierarchy] ([EmployeeId], [Name], [Designation], [ReportingManager]) VALUES (7, N'Robert King', N'Sales Representative', 8)
INSERT [dbo].[EmployeesHierarchy] ([EmployeeId], [Name], [Designation], [ReportingManager]) VALUES (8, N'Laura Callahan', N'Sales Manager', 2)
INSERT [dbo].[EmployeesHierarchy] ([EmployeeId], [Name], [Designation], [ReportingManager]) VALUES (9, N'Anne Dodsworth', N'Sales Representative', 5)
SET IDENTITY_INSERT [dbo].[EmployeesHierarchy] OFF

DECLARE @EmployeesHierarchy AS TABLE(EmployeeId INT,Name VARCHAR(20),Designation VARCHAR(20),ReportingManager INT,Temp VARCHAR(50))
INSERT INTO @EmployeesHierarchy
SELECT E1.EmployeeId,E1.Name,E1.Designation,E1.ReportingManager,E2.ReportingManager
FROM EmployeesHierarchy E1 
LEFT OUTER JOIN EmployeesHierarchy E2 ON E1.ReportingManager = E2.EmployeeId
WHERE ISNULL(E2.Name,'') > ''
UPDATE @EmployeesHierarchy
SET ReportingManager = NULL
WHERE ReportingManager NOT IN (SELECT EmployeeId FROM @EmployeesHierarchy)
SELECT * FROM @EmployeesHierarchy