Click here to Skip to main content
16,017,351 members
Articles / Web Development / HTML
Article

Generate an Organization Chart from Employee Database

,
Rate me:
Please Sign up or sign in to vote.
3.23/5 (18 votes)
18 Nov 20076 min read 264.9K   14.3K   56   24
Generate an organization chart from employee database using ASP & Microsoft Excel

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)
pic1
Image 1 (Organization Chart Type 1)
Pic2
Image 2 (Organization Chart Type 2)

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:

SQL
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}
Sample screenshot
Image 2* (Values in brackets indicates Row and column positions respectively)

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:

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.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


Written By
Web Developer L&T
India India
Software Engineer in EDRC, L&T-ECC, Chennai, INDIA.

Written By
Web Developer
India India
Software Engineer in EDRC, L&T-ECC, Chennai, INDIA.

Comments and Discussions

 
QuestionOrganizational Chart/Model Pin
Member 1033785628-Jan-14 23:13
Member 1033785628-Jan-14 23:13 
QuestionError Type mismatch in function Pin
abhinendra gautam14-Nov-12 20:32
abhinendra gautam14-Nov-12 20:32 
Generalneeded similar thing in asp.net Pin
svknair2-May-11 20:35
svknair2-May-11 20:35 
GeneralRe: needed similar thing in asp.net Pin
Y Jayan25-May-11 1:12
Y Jayan25-May-11 1:12 
GeneralOrganization Chart for Excel Pin
Albert NANA11-Mar-10 2:58
Albert NANA11-Mar-10 2:58 
QuestionExact Inverted Tree Structure [modified] Pin
Voyeurism20-Aug-09 5:26
Voyeurism20-Aug-09 5:26 
GeneralOganistion Chart Component for ASP.NET Pin
orgchartcomponent30-Jan-09 2:55
orgchartcomponent30-Jan-09 2:55 
QuestionCan't get it to work! Pin
leaning7-Dec-08 9:04
leaning7-Dec-08 9:04 
QuestionOrg chart Pin
Donlmartin29-Oct-08 10:34
Donlmartin29-Oct-08 10:34 
AnswerRe: Org chart Pin
BSRK3-Nov-08 0:29
BSRK3-Nov-08 0:29 
QuestionThe Same only in Excel ? Pin
Jean-Jacques Pisano11-Nov-07 22:09
Jean-Jacques Pisano11-Nov-07 22:09 
AnswerRe: The Same only in Excel ? Pin
BSRK16-Nov-07 1:14
BSRK16-Nov-07 1:14 
GeneralRe: The Same only in Excel ? Pin
Member 841551121-Nov-11 1:07
Member 841551121-Nov-11 1:07 
GeneralHelp for More Complex Chart Pin
shelal3-Aug-06 1:13
shelal3-Aug-06 1:13 
GeneralRe: Help for More Complex Chart Pin
BSRK16-Nov-07 22:25
BSRK16-Nov-07 22:25 
Generalproblem continues with organisation chart Pin
giridharans2-Dec-04 20:54
giridharans2-Dec-04 20:54 
GeneralRe: problem continues with organisation chart Pin
BSRK6-Dec-04 1:10
BSRK6-Dec-04 1:10 
GeneralRe: problem continues with organisation chart Pin
giridharans6-Dec-04 20:29
giridharans6-Dec-04 20:29 
GeneralCan't Create object "Excel Application" Pin
mm12843-Aug-04 6:26
mm12843-Aug-04 6:26 
GeneralRe: Can't Create object "Excel Application" Pin
BSRK3-Aug-04 18:48
BSRK3-Aug-04 18:48 
GeneralRe: Can't Create object "Excel Application" Pin
dude_4u7-Jun-05 21:02
dude_4u7-Jun-05 21:02 
GeneralRe: Can't Create object "Excel Application" Pin
BSRK13-Jun-05 3:58
BSRK13-Jun-05 3:58 
GeneralRe: Can't Create object "Excel Application" Pin
USeagle3-Jul-06 4:00
USeagle3-Jul-06 4:00 
GeneralRe: Can't Create object "Excel Application" Pin
markashy2-Jul-07 19:29
markashy2-Jul-07 19:29 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.