Create an Organization Chart in MOSS 2007 Using a Contacts List

Note: This article was originally posted on NothingButSharePoint.com, but some of the code snippets were unescaped when it was published, so I decided to post it on my own blog with the correct code snippets.

I was recently tasked with finding a way to maintain an org chart for my department. If this were a single-use resource that didn’t need to be updated frequently, I would have chosen to create it in a program such as Visio or Adobe Illustrator (even PowerPoint would work well with its SmartArt capabilities). However, this is something we want to be as maintenance-free as possible so we aren’t rebuilding it as people come and go.

The department’s operations team already maintains a list of everyone in the department by using a SharePoint Contacts list, so we can pull data from that and build the org chart dynamically. This method does not require any custom code or web parts, so there’s no need to deploy anything on the server!

Sample Org Chart

This is the org chart that will be generated.

Before I go any further, I want to point out the benefits of maintaining a department contact list on SharePoint (rather than relying on distribution lists and looking people up on their MySite page every time you need a phone number). If your department doesn’t do this already, consider these benefits:

  • Everyone in the department has a central place they can bookmark to quickly look for someone’s contact information (email, work phone, mobile phone, etc.)
  • The list can be printed if employees want to have a hard copy of all department personnel
  • The list is easy to maintain if it is incorporated with the current on-boarding workflow that the operations team manages
  • Information in the list can be used for “fun” projects like listing upcoming birthdays or anniversaries
  • Information in the list can be used for practical projects like this org chart or a “meet the team” page for site visitors

I also recommend this series of articles that explains how the XSLT works in SharePoint. It’s not required reading for this post, but it will help explain why we’re doing some of the things that make the org chart work.

A Synergy of Web Technologies

The org chart in this example uses a combination of CSS, jQuery, Google Charts, and XSLT to create the final product. The scripts that I used can be found here:

For now we can load jQuery from Google’s CDN. I also decided to simply load the Google Charts API directly from Google’s web site. For production it might be better to copy the scripts into a document library on your SharePoint site and load them that way.

The Nested List to Google Org Chart plugin is not hosted anywhere, so that script must be stored somewhere on SharePoint. I have a document library called “webresources” that I use to house all of the centralized style sheets, images, and scripts I use for our department’s SharePoint site, so I uploaded the jQuery plugin here. I also created an “orgchart.css” style sheet and uploaded it here as well (at this point it doesn’t have any styles defined, but I may decide to customize the look and feel of the org chart in the future).

Now that the resources are taken care of, it’s time to start building!

Setting Up the Contacts List

The Contacts list has several columns out of the box, but we’ll need to create at least two additional columns: Department (or Team—this will be displayed on the org chart so viewers can easily see which team or sub-department everyone belongs to) and Supervisor (make this a “Person or Group” column). I also recommend changing as many of the default columns to a “Person or Group” column type as possible. This will ensure that they show the latest information pulled from Active Directory.

To change the default columns to a “Person or Group” column type, first find out what the Internal Name of the column is. You can do this on the list settings page by clicking on a column name to edit it. In the URL look for a parameter called “Field.” The Internal Name of the column will be after the “=” sign. The Full Name column, for example, has an internal name of “FullName” with no spaces.

Internal Name

The internal name of the field can be found in the URL query string.

Delete the column, then create a new column and enter “FullName” (no spaces) as the new Column name, and select “Person or Group” for the column type. At the bottom of the page, select “Name (with presence)” in the Show field drop-down and click OK to save the new column.

Create New Column

Delete the text columns and replace them with Person or Group columns.

Now that the new column is created, edit it again and add a space in the column name. This will keep the Internal Name the same (“FullName”), but will change the Display Name to “Full Name” with a space. If we had created the new column and called it “Full Name” with a space to begin with, the Internal Name would have been “Full_x0020_Name” which wouldn’t match the original column’s internal name (SharePoint converts spaces in column names to “_x0020_” when new columns are created).

Add space(s) to the new column name(s)

Add spaces back into the new column names.

Do this for all other columns that you wish to change to a Person or Group type (simply change the Show field drop-down to show the appropriate information). I recommend changing the following at a minimum if you’re using MOSS 2007 or later, because all of this information should be in Active Directory:

Column Display NameInternal NameShow field Selection
E-mail AddressEmailWork e-mail
Job TitleJobTitleTitle
Business PhoneWorkPhoneWork phone

I also changed the column order so the new columns are not at the bottom of the EditForm.aspx and DispForm.aspx pages.

Column Order

Change the column order so the new columns are not all at the bottom.

Now it’s simply a matter of entering each person in the department into the Contacts list. This may take a while initially, but once it’s set up it only takes a few seconds to add or remove personnel from the list in the future.

Creating the Data View Web Part

The next step is to create the Data View Web Part (DVWP) that will build the nested lists of contacts. Create a new blank web part page on your site called “OrgChart.aspx.” Open the page in SharePoint Designer (you may need to detach the page from its layout), click on an empty web part zone (you’ll need to be in Design or Split view mode), and go to Data View > Insert Data View… to add a new DVWP. In the Data Source Library pane on the right, click on the Contacts list and select “Show Data”.

DVWP - Show Data

Select the Contacts list to show data from the list.

Now the Data Source Details pane will be visible. Select just one of the fields; at this point it doesn’t matter, so I went with the Last Name field. Now choose to insert the selected field as a Multiple Item View. The DVWP should now be displaying the last names of everyone in the Contacts list.

Insert Multiple Item View

Insert the selected field as a "Multiple Item View."

Hover over the DVWP and click on the small white box with the right-arrow that appears in the top-right corner of the DVWP (this is called the “Common Data View Tasks” menu). From here we need to set a few options. First, click on “Paging” and make sure that “Display all items” is selected.

Set the Paging

Set the paging so all items are shown.

Then click on “Change Layout…” and select the bulleted list layout. This would be a good time to save the page as well.

Unordered List Layout

Choose the unordered list layout.

Now let’s tidy up the XSLT that SP Designer generates and remove any SharePoint-specific HTML as-needed. Switch to code view and find the <XSL> tag inside the DVWP. Right-click on the tag and select “Select Tag” to highlight the entire XSL style sheet. Now press Shift + Tab repeatedly until all of the indentations are gone from the XSLT.

Select Tag

Right-click on the <XSL> tag and choose "Select Tag" to highlight the entire XSL style sheet.

We’re going to be working with the XSL templates quite a bit, so first find each <xsl:template>…</xsl:template> and add an extra blank line before and after each template (i.e. add a blank line before <xsl:template> and after </xsl:template>). I also prefer to re-indent the XSL templates by hand to show nested elements better (use the Tab key, not the Spacebar) so my XSL style sheet looks something like this:

<XSL>
<xsl:stylesheet xmlns:x="http://www.w3.org/2001/XMLSchema" xmlns:d="http://schemas.microsoft.com/sharepoint/dsp" version="1.0" exclude-result-prefixes="xsl msxsl ddwrt" xmlns:ddwrt="http://schemas.microsoft.com/WebParts/v2/DataView/runtime" xmlns:asp="http://schemas.microsoft.com/ASPNET/20" xmlns:__designer="http://schemas.microsoft.com/WebParts/v2/DataView/designer" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt" xmlns:SharePoint="Microsoft.SharePoint.WebControls" xmlns:ddwrt2="urn:frontpage:internal">
<xsl:output method="html" indent="no"/>
<xsl:decimal-format NaN=""/>
<xsl:param name="dvt_apos">'</xsl:param>
<xsl:variable name="dvt_1_automode">0</xsl:variable>

<xsl:template match="/" xmlns:x="http://www.w3.org/2001/XMLSchema" xmlns:d="http://schemas.microsoft.com/sharepoint/dsp" xmlns:asp="http://schemas.microsoft.com/ASPNET/20" xmlns:__designer="http://schemas.microsoft.com/WebParts/v2/DataView/designer" xmlns:SharePoint="Microsoft.SharePoint.WebControls">
	<xsl:call-template name="dvt_1"/>
</xsl:template>

<xsl:template name="dvt_1">
	<xsl:variable name="dvt_StyleName">BulTitl</xsl:variable>
	<xsl:variable name="Rows" select="/dsQueryResponse/Rows/Row" />
	<ul>
		<xsl:call-template name="dvt_1.body">
			<xsl:with-param name="Rows" select="$Rows" />
		</xsl:call-template>
	</ul>
</xsl:template>

<xsl:template name="dvt_1.body">
	<xsl:param name="Rows" />
	<xsl:for-each select="$Rows">
		<xsl:call-template name="dvt_1.rowview" />
	</xsl:for-each>
</xsl:template>

<xsl:template name="dvt_1.rowview">
	<li class="ms-vb">
		<xsl:value-of select="@Title" />
		<xsl:if test="$dvt_1_automode = '1'" ddwrt:cf_ignore="1">
			<br /><span ddwrt:amkeyfield="ID" ddwrt:amkeyvalue="ddwrt:EscapeDelims(string(@ID))" ddwrt:ammode="view" />
		</xsl:if>
	</li>
</xsl:template>

</xsl:stylesheet>
</XSL>

Now we can see the code a bit easier. Look for <ul> and change it to <ul id="orgchart">, then find <li class="ms-vb"> in the “dvt_1.rowview” template and remove the class attribute so it’s just <li>. This would be a good time to save the page now that we’ve gone to the trouble of reformatting the code and removing the SharePoint-specific class from the XSLT.

Parameters

I set up my org chart so that it uses two URL query string parameters. The first is “TopLevel” to determine which person to start the org chart with. For example, if the URL were OrgChart.aspx?TopLevel=Josh%20McCarty then the org chart would start with me at the top. This makes the XSLT re-usable to show team-specific charts (by using a team manager as the “TopLevel”) or department-wide charts (by using the Director/VP/etc. of the department as the “TopLevel”). To create this parameter, switch back to design view and open the Common Data View Tasks menu, then click on “Parameters…”

To create the parameter, click on the New Parameter button and type “TopLevel” as the Name. Select “Query String” as the Parameter Source, and enter “TopLevel” again as the Query String Variable. The Default Value field can be left blank, or it can be set to the head of the department (this will need to be manually updated if that person leaves the department). Click OK to create the new parameter.

Parameter

Create new parameters for the org chart.

The second parameter I use is “Levels” to determine how many levels deep the org chart should go. For example, if the URL were OrgChart.aspx?TopLevel=Josh%20McCarty&Levels=3 then the org chart would show three levels of subordinates below me (if only I were that high up the ladder!). Create the “Levels” parameter using the same method as the “TopLevel” parameter, but in this case I would specify a default value so the org chart never shows just one person. I used 10. This would be another good time to save the page.

How This All Works

Before we dive into the XSLT and start making further changes, I want to explain how this is going to work. This is the basic logic that the XSLT will use:

  1. Select the contact whose @FullName matches the value in the “TopLevel” query string parameter, then display that contact in the <li> of the unordered list.
  2. Create a nested <ul> within the <li> of the top level contact.
  3. Select all of the contacts whose @Supervisor column matches the @FullName of the top level contact, then display those contacts as <li>’s of the nested <ul>.
  4. Create nested <ul>’s for each of the nested <li>’s of the second level contacts.
  5. Select all of the contacts whose @Supervisor column matches the @FullName of the second level contacts, then display those contacts as <li>’s of the second nested <ul>’s.
  6. Repeat until the level specified in the “Levels” parameter is reached or until there are no more contacts.

Because we are going to be repeating the same process, we can use a recursive XSL template. For each contact, the template will call itself again to display the next level of contacts until there are no more people below the previous contact. It will do this independently on each branch of the org chart, so it will always follow the branches to their lowest level (or until they reach the number of levels specified by the “Levels” parameter in the query string).

Creating the XSLT

Switch back to code view in SP Designer and find the “dvt_1.rowview” template. Below this we are going to create a new “contact_details” template that will display details about each contact in the org chart. We’ll call this template each time we need to display a contact on the org chart. Let’s keep it simple for right now while we’re still developing. This will display just the name (with presence) of the contact:

<xsl:template name="contact_details">
	<xsl:param name="Rows" />
	<xsl:param name="FullName" />
	<xsl:for-each select="$Rows[substring-before(substring-after(substring-after(@FullName, '?ID='), '&gt;'), '&lt;') = $FullName]">
		<xsl:value-of select="@FullName" disable-output-escaping="yes" />
	</xsl:for-each>
</xsl:template>

Notice that <xsl:for-each> has an XPath expression. This is used to select the contact whose @FullName column matches the $FullName parameter that is passed whenever this template is called. Because this column includes presence information, we need to use the substring method to strip out the HTML and get to the raw text of the @FullName column in order to compare it to $FullName. In our “dvt_1.body” and “dvt_1.rowview” templates we are going to use <xsl:call-template> to call the “contact_details” template and pass the $FullName parameter to it.

Now find the “dvt_1.body” template. Modify it by adding a slightly different XPath expression to the select attribute of <xsl:for-each>:

<xsl:for-each select="$Rows[substring-before(substring-after(substring-after(@FullName, '?ID='), '&gt;'), '&lt;') = $TopLevel]">

This will filter all of the people in our Contacts list (the “$Rows”) to select the contact whose @FullName value matches the $TopLevel parameter in the query string. We also need to create a variable to track the current level of the org chart so we can test for when the “Levels” parameter from the query string is satisfied. Do this by adding <xsl:variable select="number(0)" /> after <xsl:for-each>. This will set the top level of the org chart to zero (use the “number” method to set the variable to a number value instead of a text string).

To display this contact in the org chart, create a <li> element inside <xsl:for-each> (after the variable we just created). Inside that <li> element, call the “contact_details” template with the Rows and FullName parameters (again use XPath for the FullName to strip out the presence information):

<xsl:template name="dvt_1.body">
	<xsl:param name="Rows" />
	<xsl:for-each select="$Rows[substring-before(substring-after(substring-after(@FullName, '?ID='), '&gt;'), '&lt;') = $TopLevel]">
		<xsl:variable name="CurLevel" select="number(0)" />
		<li>
			<xsl:call-template name="contact_details">
				<xsl:with-param name="Rows" select="$Rows" />
				<xsl:with-param name="FullName" select="substring-before(substring-after(substring-after(@FullName, '?ID='), '&gt;'), '&lt;')" />
			</xsl:call-template>
			<xsl:call-template name="dvt_1.rowview" />
		</li>
	</xsl:for-each>
</xsl:template>

After calling the “contact_details” template, the <li> element will call our “dvt_1.rowview” template to display all of the contacts below the top level. This is where we need to filter the second level of the org chart to select only the contacts whose @Supervisor column matches the @FullName column of the top level contact. We can accomplish this by creating a Supervisor parameter, setting its value to use the @FullName of the top level contact, and passing that value to the “dvt_1.rowview” template. We also need to pass the $CurLevel variable so the additional levels of the org chart can test for when the “Levels” parameter from the query string is satisfied:

<xsl:template name="dvt_1.body">
	<xsl:param name="Rows" />
	<xsl:for-each select="$Rows[substring-before(substring-after(substring-after(@FullName, '?ID='), '&gt;'), '&lt;') = $TopLevel]">
		<xsl:variable name="CurLevel" select="number(0)" />
		<li>
			<xsl:call-template name="contact_details">
				<xsl:with-param name="Rows" select="$Rows" />
				<xsl:with-param name="FullName" select="substring-before(substring-after(substring-after(@FullName, '?ID='), '&gt;'), '&lt;')" />
			</xsl:call-template>
			<xsl:call-template name="dvt_1.rowview">
				<xsl:with-param name="Rows" select="$Rows" />
				<xsl:with-param name="CurLevel" select="$CurLevel" />
				<xsl:with-param name="Supervisor" select="substring-before(substring-after(substring-after(@FullName, '?ID='), '&gt;'), '&lt;')" />
			</xsl:call-template>
		</li>
	</xsl:for-each>
</xsl:template>

Note: Because we are using a query string parameter to determine which contact is at the top of the org chart, the design view in SP Designer will never show any contacts (unless you specified a default value for the “TopLevel” parameter), so don’t worry if you don’t see anything. You can check progress any time by saving the page and opening it in a browser and including the query string parameters.

To create the second level of the org chart, edit the “dvt_1.rowview” template to receive the $Rows and $Supervisor parameters. Then wrap a <ul> around an <xsl:for-each> with an XPath expression to select only the contacts whose @Supervisor column matches the $Supervisor parameter. Include a <li> inside <xsl:for-each> and call the “contact_details” template:

<xsl:template name="dvt_1.rowview">
	<xsl:param name="Rows" />
	<xsl:param name="Supervisor" />
	<ul>
		<xsl:for-each select="$Rows[substring-before(substring-after(substring-after(@Supervisor, '?ID='), '&gt;'), '&lt;') = $Supervisor]">
			<li>
				<xsl:call-template name="contact_details">
					<xsl:with-param name="Rows" select="$Rows" />
					<xsl:with-param name="FullName" select="substring-before(substring-after(substring-after(@FullName, '?ID='), '&gt;'), '&lt;')" />
				</xsl:call-template>
			</li>
		</xsl:for-each>
	</ul>
</xsl:template>

Now we need to modify the “dvt_1.rowview” template so it calls itself (i.e. make it recursive) until the “Levels” parameter from the query string is satisfied. To do this, first add <xsl:param name="CurLevel" /> so the $CurLevel parameter is received from the “dvt_1.body” template, then create a new variable called “LevelTest” to set the second level to $CurLevel + 1 so we can increment the level of the org chart:

<xsl:template name="dvt_1.rowview">
	<xsl:param name="Rows" />
	<xsl:param name="Supervisor" />
	<xsl:param name="CurLevel" />
	<xsl:variable name="LevelTest" select="number($CurLevel + 1)" />
	<ul>
		<xsl:for-each select="$Rows[substring-before(substring-after(substring-after(@Supervisor, '?ID='), '&gt;'), '&lt;') = $Supervisor]">
			<li>
				<xsl:call-template name="contact_details">
					<xsl:with-param name="Rows" select="$Rows" />
					<xsl:with-param name="FullName" select="substring-before(substring-after(substring-after(@FullName, '?ID='), '&gt;'), '&lt;')" />
				</xsl:call-template>
			</li>
		</xsl:for-each>
	</ul>
</xsl:template>

Next we need to test for the value of $LevelTest and only display the contacts if $LevelTest is less than or equal to the “Levels” parameter in the query string. Add this <xsl:if> around the <ul> element:

<xsl:template name="dvt_1.rowview">
	<xsl:param name="Rows" />
	<xsl:param name="Supervisor" />
	<xsl:param name="CurLevel" />
	<xsl:variable name="LevelTest" select="number($CurLevel + 1)" />
	<xsl:if test="($LevelTest &lt;= number($Levels))">
		<ul>
			<xsl:for-each select="$Rows[substring-before(substring-after(substring-after(@Supervisor, '?ID='), '&gt;'), '&lt;') = $Supervisor]">
				<li>
					<xsl:call-template name="contact_details">
						<xsl:with-param name="Rows" select="$Rows" />
						<xsl:with-param name="FullName" select="substring-before(substring-after(substring-after(@FullName, '?ID='), '&gt;'), '&lt;')" />
					</xsl:call-template>
				</li>
			</xsl:for-each>
		</ul>
</xsl:if>
</xsl:template>

We don’t want to display anything if there are no contacts that match the $Supervisor parameter, so we need to nest another <xsl:if> inside the <xsl:if> for the $LevelTest parameter and test if any contacts exist whose @FullName column matches the $Supervisor parameter:

<xsl:template name="dvt_1.rowview">
	<xsl:param name="Rows" />
	<xsl:param name="Supervisor" />
<xsl:param name="CurLevel" />
<xsl:variable name="LevelTest" select="number($CurLevel + 1)" />
	<xsl:if test="($LevelTest &lt;= number($Levels))">
		<xsl:if test="$Rows[substring-before(substring-after(substring-after(@Supervisor, '?ID='), '&gt;'), '&lt;') = $Supervisor]">
			<ul>
				<xsl:for-each select="$Rows[substring-before(substring-after(substring-after(@Supervisor, '?ID='), '&gt;'), '&lt;') = $Supervisor]">
					<li>
						<xsl:call-template name="contact_details">
							<xsl:with-param name="Rows" select="$Rows" />
							<xsl:with-param name="FullName" select="substring-before(substring-after(substring-after(@FullName, '?ID='), '&gt;'), '&lt;')" />
						</xsl:call-template>
					</li>
				</xsl:for-each>
			</ul>
		</xsl:if>
</xsl:if>
</xsl:template>

Finally we need to make the template recursive by calling itself. Call the “dvt_1.rowview” template after calling the “contact_details” template, and pass all the same parameters that we used when calling “dvt_1.rowview” from the “dvt_1.body” template, except use $LevelTest when selecting the “CurLevel” parameter instead of using the number zero. This means that when “dvt_1.rowview” is called again, it will continue to increment the value of $CurLevel by +1 each time, allowing the template keep calling itself until it satisfies the “Levels” parameter in the query string:

<xsl:template name="dvt_1.rowview">
	<xsl:param name="Rows" />
	<xsl:param name="Supervisor" />
	<xsl:param name="CurLevel" />
	<xsl:variable name="LevelTest" select="number($CurLevel + 1)" />
	<xsl:if test="($LevelTest &lt;= number($Levels))">
		<xsl:if test="$Rows[substring-before(substring-after(substring-after(@Supervisor, '?ID='), '&gt;'), '&lt;') = $Supervisor]">
			<ul>
				<xsl:for-each select="$Rows[substring-before(substring-after(substring-after(@Supervisor, '?ID='), '&gt;'), '&lt;') = $Supervisor]">
					<li>
						<xsl:call-template name="contact_details">
							<xsl:with-param name="Rows" select="$Rows" />
							<xsl:with-param name="FullName" select="substring-before(substring-after(substring-after(@FullName, '?ID='), '&gt;'), '&lt;')" />
						</xsl:call-template>
						<xsl:call-template name="dvt_1.rowview">
							<xsl:with-param name="Rows" select="$Rows" />
							<xsl:with-param name="CurLevel" select="$LevelTest" />
							<xsl:with-param name="Supervisor" select="substring-before(substring-after(substring-after(@FullName, '?ID='), '&gt;'), '&lt;')" />
						</xsl:call-template>
					</li>
				</xsl:for-each>
			</ul>
		</xsl:if>
	</xsl:if>
</xsl:template>

Save the page, then open it in your browser and take a look (don’t forget to use include the query string in your URL). You should see a bulleted list of contacts, grouped according to supervisor. Try changing the “Levels” parameter in the query string to see the org chart change in depth.

Note: A value of “0” for the “Levels” parameter will show just the person in the “TopLevel” parameter. A value of “1” will show one level below the person at the top; a value of “2” will show two levels below the person at the top, and so on.

Nested Lists

The XSL should now be generating a nested list of contacts.

Now that our nested lists are working, let’s add additional information to the “contact_details” template. All of our changes will take place inside <xsl:for-each>. Go ahead and add any additional columns that you want displayed. For example, if you wanted to display name, job title, work phone, and email (with a mailto link) your template might look something like this:

<xsl:template name="contact_details">
	<xsl:param name="Rows" />
	<xsl:param name="FullName" />
	<xsl:for-each select="$Rows[substring-before(substring-after(substring-after(@FullName, '?ID='), '&gt;'), '&lt;') = $FullName]">
		<xsl:value-of select="@FullName" disable-output-escaping="yes" /><br />
		<xsl:value-of select="substring-before(substring-after(substring-after(@JobTitle, '?ID='), '&gt;'), '&lt;')" /><br />
		<xsl:value-of select="substring-before(substring-after(substring-after(@WorkPhone, '?ID='), '&gt;'), '&lt;')" /><br />
		<a href="mailto:{substring-before(substring-after(substring-after(@Email, '?ID='), '&gt;'), '&lt;')}"><xsl:value-of select="substring-before(substring-after(substring-after(@Email, '?ID='), '&gt;'), '&lt;')" /></a>
	</xsl:for-each>
</xsl:template>

Note how we took a substring of most of the columns. By default, all columns that are a “Person or Group” type include presence information. We don’t need that displayed more than once, so we can remove it from all columns except the @FullName column. Also be sure to include the disable-output-escaping="yes" attribute to the @FullName column, otherwise the actual HTML code will be output as text on the page. Save the page and take a look in your browser. The result will look like this:

Contact Details

Details for each contact will now be displayed in the list.

The Data View Web Part is now completed. Here is the entire XSL style sheet if you want to copy and paste this into your DVWP:

<Xsl>
<xsl:stylesheet xmlns:x="http://www.w3.org/2001/XMLSchema" xmlns:d="http://schemas.microsoft.com/sharepoint/dsp" version="1.0" exclude-result-prefixes="xsl msxsl ddwrt" xmlns:ddwrt="http://schemas.microsoft.com/WebParts/v2/DataView/runtime" xmlns:asp="http://schemas.microsoft.com/ASPNET/20" xmlns:__designer="http://schemas.microsoft.com/WebParts/v2/DataView/designer" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt" xmlns:SharePoint="Microsoft.SharePoint.WebControls" xmlns:ddwrt2="urn:frontpage:internal">
<xsl:output method="html" indent="no"/>
<xsl:decimal-format NaN=""/>
<xsl:param name="dvt_apos">'</xsl:param>
<xsl:param name="ListID">47FC6EED-1305-4425-B6BF-3818D6298ECE</xsl:param>
<xsl:param name="TopLevel" />
<xsl:param name="Levels" />
<xsl:variable name="dvt_1_automode">0</xsl:variable>

<xsl:template match="/" xmlns:x="http://www.w3.org/2001/XMLSchema" xmlns:d="http://schemas.microsoft.com/sharepoint/dsp" xmlns:asp="http://schemas.microsoft.com/ASPNET/20" xmlns:__designer="http://schemas.microsoft.com/WebParts/v2/DataView/designer" xmlns:SharePoint="Microsoft.SharePoint.WebControls">
	<xsl:call-template name="dvt_1"/>
</xsl:template>

<xsl:template name="dvt_1">
	<xsl:variable name="dvt_StyleName">BulTitl</xsl:variable>
	<xsl:variable name="Rows" select="/dsQueryResponse/Rows/Row" />
	<ul id="orgchart">
		<xsl:call-template name="dvt_1.body">
			<xsl:with-param name="Rows" select="$Rows" />
		</xsl:call-template>
	</ul>
</xsl:template>

<xsl:template name="dvt_1.body">
	<xsl:param name="Rows" />
	<xsl:for-each select="$Rows[substring-before(substring-after(substring-after(@FullName, '?ID='), '&gt;'), '&lt;') = $TopLevel]">
		<xsl:variable name="CurLevel" select="number(0)" />
		<li>
			<xsl:call-template name="contact_details">
				<xsl:with-param name="Rows" select="$Rows" />
				<xsl:with-param name="CurLevel" select="$CurLevel" />
				<xsl:with-param name="FullName" select="substring-before(substring-after(substring-after(@FullName, '?ID='), '&gt;'), '&lt;')" />
			</xsl:call-template>
			<xsl:call-template name="dvt_1.rowview">
				<xsl:with-param name="Rows" select="$Rows" />
				<xsl:with-param name="CurLevel" select="$CurLevel" />
				<xsl:with-param name="Supervisor" select="substring-before(substring-after(substring-after(@FullName, '?ID='), '&gt;'), '&lt;')" />
			</xsl:call-template>
		</li>
	</xsl:for-each>
</xsl:template>

<xsl:template name="dvt_1.rowview">
	<xsl:param name="Rows" />
	<xsl:param name="CurLevel" />
	<xsl:param name="Supervisor" />
	<xsl:variable name="LevelTest" select="number($CurLevel + 1)" />
	<xsl:if test="($LevelTest &lt;= number($Levels))">
		<xsl:if test="$Rows[substring-before(substring-after(substring-after(@Supervisor, '?ID='), '&gt;'), '&lt;') = $Supervisor]">
			<ul>
				<xsl:for-each select="$Rows[substring-before(substring-after(substring-after(@Supervisor, '?ID='), '&gt;'), '&lt;') = $Supervisor]">
					<li>
						<xsl:call-template name="contact_details">
							<xsl:with-param name="Rows" select="$Rows" />
							<xsl:with-param name="FullName" select="substring-before(substring-after(substring-after(@FullName, '?ID='), '&gt;'), '&lt;')" />
						</xsl:call-template>
						<xsl:call-template name="dvt_1.rowview">
							<xsl:with-param name="Rows" select="$Rows" />
							<xsl:with-param name="CurLevel" select="$LevelTest" />
							<xsl:with-param name="Supervisor" select="substring-before(substring-after(substring-after(@FullName, '?ID='), '&gt;'), '&lt;')" />
						</xsl:call-template>
					</li>
				</xsl:for-each>
			</ul>
		</xsl:if>
	</xsl:if>
</xsl:template>

<xsl:template name="contact_details">
	<xsl:param name="Rows" />
	<xsl:param name="CurLevel" />
	<xsl:param name="FullName" />
	<xsl:for-each select="$Rows[substring-before(substring-after(substring-after(@FullName, '?ID='), '&gt;'), '&lt;') = $FullName]">
		<div>
			<xsl:value-of select="@FullName" disable-output-escaping="yes" /><br />
			<xsl:value-of select="substring-before(substring-after(substring-after(@JobTitle, '?ID='), '&gt;'), '&lt;')" /><br />
			<xsl:value-of select="substring-before(substring-after(substring-after(@WorkPhone, '?ID='), '&gt;'), '&lt;')" /><br />
			<a href="mailto:{substring-before(substring-after(substring-after(@Email, '?ID='), '&gt;'), '&lt;')}"><xsl:value-of select="substring-before(substring-after(substring-after(@Email, '?ID='), '&gt;'), '&lt;')" /></a>
		</div>
	</xsl:for-each>
</xsl:template>

</xsl:stylesheet>
</Xsl>

Adding Scripts to Generate the Org Chart

The next step is to load the scripts onto the page that generate the org chart. Minimize your browser and go back to SP Designer and switch to design view. Insert a Content Editor Web Part (CEWP) on the page, switch to code view, and find the content section of the CEWP:

<Content xmlns="http://schemas.microsoft.com/WebPart/v2/ContentEditor"><![CDATA[]]></Content>

Create a few blank lines between the “[]” brackets so you have some room to work:

<Content xmlns="http://schemas.microsoft.com/WebPart/v2/ContentEditor">
	<![CDATA[

	]]>
</Content>

All of the content in a CEWP goes between those two brackets. Add the following scripts inside the CEWP, making sure to change the source attribute accordingly (I’m loading jQuery and the org chart visualization from Google for development purposes; you may want to save a copy of the scripts to your server and load them from there when the org chart is in production; note the Nested List to Google Org Chart jQuery plugin must be saved to your server because it’s not hosted anywhere else, so I have it in a “webresources” document library):

<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.2/jquery.min.js"></script>

This loads the jQuery library.

<script type='text/javascript' src='http://www.google.com/jsapi'></script>

This loads the Google charts API.

<script type="text/javascript">
	google.load('visualization', '1', {packages:['orgchart']});
</script>

This loads the org chart visualization.

<script type="text/javascript" src="http://servername/site/WebResources/jquery.g_orgchart.js"></script>

This loads the Nested List to Google Org Chart jQuery plugin from my “webresources” library.

<script type="text/javascript">
	$(document).ready(function(){
		$("#orgchart").g_orgchart({ 'size' : 'small' , 'allowCollapse' : 'true' });
	});
</script>

This tells the Org Chart jQuery plugin which nested list to use for the org chart and specifies any options to use for the org chart such as size.

The entire CEWP content section should look like this:

<Content xmlns="http://schemas.microsoft.com/WebPart/v2/ContentEditor"><![CDATA[
	<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.2/jquery.min.js"></script>
	<script type='text/javascript' src='http://www.google.com/jsapi'></script>
	<script type="text/javascript">
		google.load('visualization', '1', {packages:['orgchart']});
	</script>
	<script type="text/javascript" src="http://newsource/extaffairs/WebResources/jquery.g_orgchart.js"></script>
	<script type="text/javascript">
		$(document).ready(function(){
			$("#orgchart").g_orgchart({ 'size' : 'small' , 'allowCollapse' : 'true' });
		});
	</script>
]]></Content>

Save the page and view it in your browser. You should now have an org chart!

Org Chart

The org chart is now generated by the scripts on the page.

Most likely you’re viewing the org chart in Internet Explorer, so the actual nodes for each person will not look very appealing (white background with a light-blue border). If you view the org chart in a browser with CSS3 support (Firefox, Chrome, Safari, etc.) you should see a much nicer version of the chart with rounded corners, a background color gradient, and drop-shadows:

Org Chart Chrome

The org chart will appear with more visual effects in a browser that supports CSS3.

To make the chart a little more visually appealing in Internet Explorer, add the following CSS below the scripts in the CEWP to give the nodes a background color:

<style type="text/css">
.google-visualization-orgchart-node {
	background-color: #edf7ff;
}
.google-visualization-orgchart-nodesel {
	background-color: #fff7ae;
}
</style>

There, that looks better!

Org Chart with IE CSS

The org chart looks a little better in Internet Explorer with some additional CSS.

Internet Explorer also renders the nodes in different sizes; I assume it’s just the way IE handles tables (the org chart is actually created using table cells and cell borders).

From this point on you can customize the org chart even further. For example, by altering the “contact_details” template and using some additional CSS and JavaScript, we can create pop-up windows that display additional information about the user when clicked.

An Alternative Org Chart with No JavaScript

If you’d prefer not to use any JavaScript for the org chart, you can get a fair approximation of the org chart (minus the connecting lines) by using just CSS in the CEWP:

<style type="text/css">
#orgChart {
	font-size: 10px;
	font-family: arial, helvetica, sans-serif;
	line-height: 1.1em;
	text-align: center;
}
#orgChart, #orgChart ul {
	float: left;
	list-style-type: none;
	margin: 5px 0 0 0;
	padding: 0;
	text-align: center;
}
#orgChart li {
	display: inline;
	float: left;
	margin: 10px 5px;
	padding: 0;
}
#orgChart li div {
	-webkit-box-shadow: rgba(0, 0, 0, 0.496094) 3px 3px 3px;
	background: -webkit-gradient(linear, 0% 0%, 0% 100%, from(#EDF7FF), to(#CDE7EE));
	background-color: #EDF7FF;
	border: 2px solid #CDE7EE;
	border-radius: 5px;
	cursor: default;
	display: block;
	height: 60px;
	width: 150px;
	margin: 0 auto;
	padding: 5px;
}
</style>

Note: Using the CSS-only method will cause your org chart nodes to wrap below each other if the width of the page is too small to include all of the nodes side-by-side. You can avoid this by specifying a width for #orgChart that is large enough to include all org chart nodes.

That just about covers everything I can think of for creating an org chart in MOSS 2007 without deploying any custom web part code. If you come up with any cool ways to enhance the org chart (like adding functionality with the “contact_details” template), be sure to let me know!

25 thoughts on “Create an Organization Chart in MOSS 2007 Using a Contacts List

  1. Hi,
    I have followed the same steps…But am getting Blank as output.
    Pls help me to comlete it….

    Thanks in advance.

    Regards,
    Sudhaaa

    • Hi Sudhakar,
      It’s hard to say where the problem is without more information. At what step did your data-view web part stop working? Were you ever able to generate a bulleted list on the page? Try getting the bulleted list to work first before adding any JavaScript to the page. Also keep in mind that you have to specify a toplevel parameter in the URL. If you don’t have a toplevel parameter, your page will be blank because the DVWP is looking for a specific name in the toplevel parameter to start the org chart with. Let me know if you are able to get it working!

  2. Hi Josh,
    Thanks for posting this valuable requirement .
    I have followed the below steps.
    1.Created a “Contacts” custom list.
    2.Added the FullName,Supervisor,E mail address,work phone,job title.
    3.From SPD added a DVWP and added the Parameters like this
    TopLevel parameter orgchart.aspx?TopLevel=ABC%5Fx002e%5Fdef
    Levels Parameter is “5″.
    Note:in my org the names in abc.def format(not space)
    After this am able to see bulleted list.
    Now am adding “contact_details” part.
    Now also am able to see Bulleted list .
    after adds below steps code

    i din’t see any data
    Again i have cotinued but no luck…

    Pls help me…it’s very urgent requirement for me…

    Thank you!!!

    • If you’ve got the bulleted list working with the contact details, the problem might be with the scripts on the page. Make sure you load the scripts in the proper order – jQuery, then Google charts API, then the org chart visualization, and finally the Google org chart plugin for jQuery. Also make sure your toplevel parameter is valid; it has to exactly match the name of the person stored in the contacts list.

  3. You are so freaking cool Josh! This is a great solution and written so clearly that anyone (even me!) can follow it. It may be no big to you but for us aspiring SP developers the level of detail is extremely helpful. Keep it up man and you will make a name for yourself in the SP world. You already have several fans!

    Thanks for being so willing to share, Jenn

  4. Great article. Easy to follow for just about anybody. I have just about everything working. However when I add the code for ‘Org Chart jQuery plugin which nested list to use for the org chart and specifies any options to use for the org chart such as size’ and then view the page I only see the Full Name listed. The Job Title, Work Phone, and Email do not show up. If I remove this section all of the columns show up but of course they show up in the bulleted view without the nice org chart sections. I am loading thejquery.min.js, the jsapi, and the jquery.g_orgchart.js from a SharePoint document library. Not sure if this matters but I would think it would work as long as the code is correct. I have tried to copy the code and also manually type it in just incase. No luck. Can you think of anything I may be missing here? Thanks.

    • Hi Jonathan,

      If you’ve got everything displayed before you apply the org chart jquery plugin, then we know it isn’t a bug in the XSL templates. My first instinct is that the scripts aren’t loaded in the proper order, or you didn’t wrap all of the personal info in a div within the list item.

      Try using Firebug or the Chrome developer tools (open the page in Chrome, right-click on one of the names and select “Inspect element”) to take a look at the DOM and see what’s happening. The DOM structure for each person in the org chart should look something like this before you apply the scripts:

      <li>
      <div>
      Name<br />
      Job Title<br />
      Phone<br />
      Email<br />
      </div>
      </li>
      etc. Notice how there is a div around all of the fields? Once you verify that the DOM structure is correct, load the scripts on the page and look at the DOM again. See if the additional information is present and simply hidden, or if it’s not inside each org chart box at all. Let me know if you figure it out or have more questions!

      • Thanks Josh. I walked through the code and corrected some issues based off of your reply and it seems to have worked. Good call. This is a pretty slick Org Chart option for SharePoint. I like that it is list based which makes it easy to update or change. I also like that it reads from AD. Thanks for sharing your knowledge. I will be putting it to good use and taking credit for it come review time at my company.

  5. This is great. I have been looking for a way to create org chart in SharePoint as a super user, not an admin, this seems to be the answer. I am able to create the org chart following your instruction here, but have a couple issues:
    1. my chart doesn’t show the green/yellow bullet by the names.
    2. I put the style script in CEWP, and it does nothing to the chart (I am using IE)
    There’s one more thing I hope to accomplish. Is there a way to limit the number of colunmns and unlimited rows to show under a supervisor? For example, when a supervisor has 6 employees, I would like the chart to show them in 2 columns and 3 rows.
    Thanks.

    • Hi Erika,
      The colored bullet is the presence information based on their status in Microsoft Office Communicator. Do you see it next to any names in SharePoint (for example is it shown after the name in the “Created By” field for a list item)? If not, your environment must not be set up to use presence.

      I’m not sure what you mean by your second issue. When you say “chart” do you mean the bulleted list of people? Is your issue that you can’t get it to render using the boxes and connecting lines? Are you using IE 8/9 or an older version? Double-check your script references and the order that you call them.

      Your last request, to format subordinates in columns, is well beyond the scope of this article; you’d have to do some serious modification of Google’s charting scripts. Instead of that, you might want to try avoiding JavaScript and instead try formatting your list of employees using CSS only. The key would be to make the children <li> elements half the width of their parent, and float them or display them inline.

  6. Hi Josh!

    Great solution,

    I just wanted to if it is possible to show the picture of each contact.

    Regards
    Waqas Iftikhar

      • Unfortunately neither the Picture nor the Assistant fields can be displayed using this solution. If only it were as simple as adding a “User Picture” and “User Assistant” column to the contacts list.

        You could fetch that information using AJAX if they are included in the User Information list for your site (yoursite/_layouts/userdisp.aspx?Force=True). I don’t know about the Manager Assistants field, but the Picture field is included. Keep in mind that using AJAX like this might take a long time if you are displaying lots of people in the org chart. You’d need to get the user ID of each person in the chart, and make the AJAX call using that ID as a parameter in the query string to the User Information list display form page. That’s also beyond the scope of this article, but I’m sure you could manage it using jQuery (I’ve done something similar for a commenting system in SharePoint).

        • Hi Josh,

          Thanks for your reply, you were rite the AJAX calls makes the rendering a bit slow, Plus another disadvantage is that users with limited access can not access the page because some of the pictures require Login credentials.

          I understand than I can not display assistants, is there a way by which I can Manipulate the CSS of the Assistant google org chart box, based on a flag “Assistant”. i.e if the employee under the Top Level is an Assistant then the google Org chart box/node should have a different background color say “Red” and rest of the employees to the default color.

          Thanks,
          Waqas Iftikhar

          • Yes it’s possible to make an org chart box have a different background color, but you’d have to modify the jQuery org chart plugin or write your own plugin to do it.

            First, you’d need to output some conditional XSLT that detects the “Assistant” flag. Something like this in the dvt_1.rowview template:

            <xsl:template name="dvt_1.rowview">
            	<xsl:param name="Rows" />
            	<xsl:param name="Supervisor" />
            	<xsl:param name="CurLevel" />
            	<xsl:variable name="LevelTest" select="number($CurLevel + 1)" />
            	<xsl:if test="($LevelTest &lt;= number($Levels))">
            		<xsl:if test="$Rows[substring-before(substring-after(substring-after(@Supervisor, '?ID='), '&gt;'), '&lt;') = $Supervisor]">
            			<ul>
            				<xsl:for-each select="$Rows[substring-before(substring-after(substring-after(@Supervisor, '?ID='), '&gt;'), '&lt;') = $Supervisor]">
            					<li>
            						<xsl:if test="@Assistant = 'Yes'">
            							<xsl:attribute name="style">background-color:red</xsl:attribute>
            						</xsl:if>
            						<xsl:call-template name="contact_details">
            							<xsl:with-param name="Rows" select="$Rows" />
            							<xsl:with-param name="FullName" select="substring-before(substring-after(substring-after(@FullName, '?ID='), '&gt;'), '&lt;')" />
            						</xsl:call-template>
            						<xsl:call-template name="dvt_1.rowview">
            							<xsl:with-param name="Rows" select="$Rows" />
            							<xsl:with-param name="CurLevel" select="$LevelTest" />
            							<xsl:with-param name="Supervisor" select="substring-before(substring-after(substring-after(@FullName, '?ID='), '&gt;'), '&lt;')" />
            						</xsl:call-template>
            					</li>
            				</xsl:for-each>
            			</ul>
            		</xsl:if>
            	</xsl:if>
            </xsl:template>

            Then you’d need to make the jQuery plugin detect that style, detect the row index of that contact in the org chart, and add the style to the appropriate row using the setRowProperty() method before the plugin draws the org chart.

            I might attempt to do this in the future, but I don’t have a solution for this right now.

            Another option would be to use the DVWP to write the JavaScript for the Google Org Chart script directly, rather than translate it from nested lists. I did something similar in this post: http://wp.me/p1iF71-fm.

            If you come up with a solution, let me know!

  7. Hi Josh,

    I came up with a solution based upon your suggestions. Instead of using the style as an attribute I passed in an Id and other attributes as flags.

    I also modified your Jquery file to apply the appropriate css to nodes based on each flag

    give me your email and I will share the updated code with you.

    Waqas Iftikhar
    Student Developer

  8. Hi Josh,
    Thanks for posting – legendary work! Got it working a treat, styled to match the site css: added pictures, mailto:, links, etc., and fits nicely with our existing contacts list.
    EXCEPT – we were confirming the output before publishing and two teams were missing! Both Supervisors were displayed but no child level – they have an apostrophe in their name (e.g. O’Neil) which is surely the issue and I have tried all the hacks I can think of (enabling/disabling output escaping, trying to compare substrings, forcing replacements – ' ' etc. etc.) to try to workaround. Any idea what I’m missing? So-o near – can you help??

    • Hi Andy,

      I’m not sure why it’s not working out. I just re-created this solution from scratch using a couple of employee names with both straight apostrophes and angled apostrophes and it still worked for me.

      Maybe you could change the Supervisor column to use the Account or ID values from Active Directory instead of the Name value? You’ll also want to add an “AccountName” or “AccountID” column and populate it for all employees so you can compare the value of the Supervisor column to that instead of to the FullName column. Let me know if you have better luck with this option!

  9. I have a question, do you have to use the contacts list? can you use a list you have already created (from a sql query) to use instead?

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>