Creating an Org Chart from a SQL Database
The SharePointOrgChart web part can draw an organization chart from any relational database structure that follows these rules.
- Has a Primary Key - a field that uniquely identifies each row. For example it could be a full name or more likely a unique Identifier
- Has a Parent Field -a field that identifies the parent of this row.
Creating an organization chart from a database is a two stage process.
- Step one is creating a suitable database structure to hold the data for the org chart
- Step two is configuring the Org Chart Web part to display the list
Enter edit mode
This is based on Microsoft SQL Server Express and Microsoft SQL Server Management Express
Using SQL Server Management Studio create a new database table.
Add a column called UniqueId setting the data type to int and set the Identity Specification of the column to true.
For the best performance an index can be created on this column.
Add fields
Next add three additional fields: ManagerId, Name and Job Title
Save the table
Save the Table as OrgChart
Enter data into the table
Now enter data into the table. In the example shown a person called Jane has been added.
As she is the head of the company her ManagerId has been left as null.
Review table
The table has been populated with two additional rows of data. As both people report to Jane the value of their ManagerId has been set to the value of Jane's UniqueId.
Now we are ready to configure the org chart.
Create a new page and add the OrgChart web part
Create a new page called DatabaseOrgChart by selecting the Add Pages option from the Site Actions menu.
Select the OrgChart web part from Custom Categories and add the web part to the page.
Once added the OrgChart web part requires configuring. Click the link on the web part to open the tool pane.
Data Source configuration
In the Data Source configuration settings set following
- DataSource Type: Select Database from the drop down list
- Primary Key: Set this to be UniqueId
- Manager Set this to be ManagerId
- SQL Server Connection String: Set this to be the connection string to the database
- SQL Query: This should be the SQL statment required to return the data to the web part.
Apply your changes
Finally press Apply and the configured org chart should be displayed as shown.