Generate an Organization Chart from Employee Database

• Download source files - 56.5 KB
• Download demo project - 36.1 KB

Introduction

This article explains a simple procedure for generating an Organization Chart from your employee database tables. It draws the chart based on the hierarchical relationship from employee to employee.

Background

As we needed to generate a chart of this kind, we tried to use any of the readily available and freely shared tools on the web, but couldn't find any. Finally, we arrived at a reasonable solution, which we are sharing with you now.

Tools Used

• Database: Microsoft Access (or SQL Server or Oracle)
• Server side: Active Server Pages
• Client side: HTML
• Client side Scripting: JavaScript / VB Script
• Presentation Layer: Microsoft Excel (for generating the chart)

Methodology

To explain the methodology, let us take the example of a sample employee database having the following hierarchical relationship:

Table 1

RANK	DESCRIPTION	DESIGNATION	Immediate Boss
1	Top Management	DGH	--NIL--
2	Dept. Head	DDH	DGH
3	Section Head	SH	DDH
4	Engineers	DE	SH
4	Draughts men	DM	SH

All the DEs & DMs will be reporting to their respective SH. Similarly, all the SHs will be reporting to their respective DDHs. Please note that there are two categories of employees (DE & DM) under SH.

For this sample database, an Organization chart can be drawn either as:

• An exact inverted tree structure showing the employees of the same rank in a single row (refer Image 1 below) OR
• A partial inverted tree structure by grouping the employees of the same rank and designation under a title block (refer Image 2 below)

STEP 1: From the existing employee database tables, we need to generate a table/view(s) representing this hierarchical model. It may look like this:

Emp ID(Only those Employees representing LAST RANK)	Designation	Immediate Boss	Next Level Boss	Next-Next Level Boss	���	Top Management
DE1	DE	SH1	DDH1			DGH
DE2	DE	SH1	DDH1			DGH
DE3	DE	SH2	DDH1			DGH
DE4	DE	SH3	DDH2			DGH
DM1	DM	SH1	DDH1			DGH
DM2	DM	SH2	DDH1			DGH
�����

Notes

1. Please refer to the sample Microsoft Access database (dbOrganizationChart.mdb) containing the table (tblOrganizationChart).
2. In this table, we have used one additional field named CADRE which may be used for ORDERING the employees within the same group.

STEP 2: In order to group the concerned employees under each section/group, we must use the ORDER BY clause, ordering from required Top Rank onwards till the required Bottom Rank. For example:

SELECT * from tablename ORDER BY DGH, DDH, SH

STEP 3a: Now, we need to arrange the data accordingly for each rank, in the form of arrays containing employee details. For example:

DGH_List={DGH}
DDH_List ={DDH1, DDH2}                  ordered by DGH, EmpID
SH_List = {SH1, SH2, SH3}               ordered by DGH, DDH, EmpID
DE_List = {DE1, DE2, DE3, DE4,DE5}      ordered by DGH, DDH, SH, EmpID
DM_List = {DM1, DM2, DM3}               ordered by DGH, DDH, SH, EmpID

STEP 3b: Now, we need to get the number of immediate employees under each HEAD accordingly for each rank, in the form of arrays. For example:

No_DDH_Under_DGH={2}                    (i.e.) {(DDH1,DDH2)}
No_SH_Under_DDH = {2, 1}                (i.e.) {(SH1,SH2),SH3}
No_DE_Under_SH = {2, 1, 2}              (i.e.) {(DE1,DE2),DE3,(DE4,DE5)}
No_DM_Under_SH = {1, 1, 1}              (i.e.) {DM1,DM2,DM3}

STEP 4: Now, we need to decide the positions (row position and column position) of each of the entities. As already mentioned above, we can do this in two ways as shown in Image 1 and Image 2. However, we will discuss only the method followed for Image 2 below.

When we want to print the chart a bit precisely, we can place employees of equal rank (generally last rank) of that group in the same column one under the other & connect their names to a TITLE (DE/DM) representing them. This title will in turn be connected to the immediate boss properly. For this case, we can assign the row and column positions as mentioned below (refer Image 2*):

Row Position

For employees other than those belonging to the last rank, it is nothing but the rank of that designation (Refer Table 1). For the title of the grouped employees of the last rank, it is the same as that of the last rank. Now, for the employees in each group, the row position can be incremented by '+1' starting from the title.

Column Position

As per the hierarchy in the above-mentioned example, each section head (SH) will be having two categories of employees namely DE & DM. In order to represent the same properly, SH shall come in between the DE & DM, i.e. each SH will occupy 3 (=2+1) columns. Hence, we have used the formula (3x-1) for SH column position where 'x' is nothing but the position in the SH array. Obviously, the DE & DM column positions will be one left and one right to the SH position, i.e. We can take "DE column position = SHposition-1" & "DM column position = SHposition+1". Once the SH column positions are obtained, the column positions for the above ranks will be the same as that of the average of the column positions of those employees immediately under them.

It can be understood that, if there are 'n' number of categories reporting to SH level, that will occupy 'n+1' positions & the SH position can be obtained as '(n+1)*x-1'. Accordingly, the immediate subordinate positions can be towards the left and right respectively.

Notes

1. Store all these row positions and column positions in arrays.
2. In the given sample coding, we have demonstrated the second method of STEP 4.

Example: From STEP 3a & 3b,

DDH_List = {DDH1, DDH2, .....}	No_SH_Under_DDH = {2, 1}
SH_List = {SH1, SH2, SH3,......}	No_DE_Under_SH = {3, 2, 3}, No_DM_Under_SH = {2, 0, 4}

Hence, the column positions for SHs use the formula (3x-1) as explained above (here X takes the value {1,2,3} since we have {SH1, SH2, SH3}

Col_SH                 = {3*1-1, 3*2-1, 3*3-1} = {2,5,8} 
DEs are Col_DE(Title)  = {2-1, 5-1, 8-1}       = {1, 4, 7}
DMs are Col_DM(Title)  = {2+1, 5+1, 8+1}       = {3, 6, 9}
DDHs are Col_DDH       = {(2+5)/2, 8/1}        = {3.5, 8}
DGH is Col_DGH         = {(3.5+8)/2}           = {5.75}

STEP 5: Now that we are ready with all the arrays for all the employee ranks, we just need to pass on these arrays to Excel macros for drawing the rectangles & connectors.

Drawing Rectangles & Connectors using Excel Macro

We need to receive all the above arrays as arguments into the macro, for drawing the organization chart.

• Draw the rectangles based on the row positions and column positions. Simultaneously, we shall name the rectangle so that we can connect them later.
• Add the text data (Employee Name, etc.) from the array into the respective rectangle.
• Create a Shape connector and connect its tips to the rectangles properly.

Please refer to the macro for detailed coding on how to finish the above tasks. However, let me add that ShapeRectangle takes five arguments (Shape,LEFT,TOP,WIDTH,HEIGHT). We have used PositionWidth for arriving at the location (left and top) of the rectangle, while BoxWidth and Box Height for arriving on the size of the rectangle (width and height).

How to Run this Sample Project

Copy all the files in the *_SRC.ZIP file to a virtual directory (created using IIS or PWS) and run the OrganizationCreate.asp file in the browser:

• http://localhost/NAMEofVIRTUALDIRECTORY/OrganizationCreate.asp (local machine)
• http://NAMEofVIRTUALDIRECTORY/OrganizationCreate.asp (remote machine)

Important Note

Before generating the output, please change the Internet security settings as mentioned below. In the Internet browser, Go to Tools menu -> Internet Options -> Security tab -> (select - Local Intranet) -> Custom Level and proceed as follows:

• If you have provision to sign the ActiveX controls, enable "Script ActiveX controls marked safe for scripting".
• Else if you are not having any such provision to sign it, enable the option "Initialize and script ActiveX controls not marked as safe".

Acknowledgements

We wish to acknowledge gratefully the support rendered by Mr. T.G.Bhaskar (DDH-Computerization), Mr. S.MallikarjunaRao, Mrs. A.Subhasree, Mr. S.Md.Saleem & Mr. R.Ugandhar.

We welcome suggestions from readers for improving this article in any manner.