Hiding root element of Google Organizational Chart in ASP.Net

Last Reply one year ago By PK257

Posted one year ago

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 one year ago Modified on one year ago

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