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)

Image 1 (Organization Chart Type 1)

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:

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

Store all these row positions and column positions in arrays.

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}

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:

Acknowledgements

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.