Although SharePoint comes with the out-of-the-box organizational chart, there are scenarios where a customer would like to create their own chart based on custom data. One of our customers at Sparked created a list to store all of their worldwide SharePoint employees. This was a pretty straightforward contact list with a name, job title, region and manager as metadata. The challenge was creating an organizational chart based at this list without the help of a third party tool! There are, of course, tools to facilitate this requirements. Here is one covered recently in a blog review worth checking out. Unfortunately, we couldn’t acquire one so I had to look for a different solution. After doing some research I stumbled upon a solution! A collaboration between SharePoint, Excel and Visio. Let’s take a closer look.
Let’s start with SharePoint
We start with creating a custom list to store all employees. In our example we create a list with SharePoint employees. The following figure illustrates the example:
There is a neat little trick to automatically get the full name without manually entering it. Create a new calculated column with the following formula:
=[First name]&” “&[Last name]
Works like a charm!
Next step: Excel!
Our contact list is done; it contains all of the metadata and employees. The next step is exporting the list to Excel. Open the ribbon and click on List and select Export to Excel:
We have to clean up the spreadsheet a bit by manually removing the last two columns. The export only shows the data from the current view of the list. This offers you flexibility by creating other views with other metadata fields to create multiple Excel files.
Instead of exporting the list to Excel after every change, you can automatically update Excel. Save the file and close it. Enter a new item to the list:
Reopen the Excel file, open the ribbon and click on Data and Refresh All:
The new row with sales employee Gemma is available in Excel! How cool is this? In our example we only use single line of text columns. I can imagine you use a managed metadata column for job titles or another metadata fields. The export to Excel looks a bit different for managed metadata columns:
There is an easy solution to remove the IDs:
The only downside is you have to do this after every synchronization.
Build your organizational chart in Visio
Now it’s time to open Visio and create a new organization chart. The first pop-up appears:
Click on Next:
Click on Next:
Select the Excel file and preferred language and click on Next (You have to close the Excel file otherwise you cannot continue):
Select Full name, Manager and set the last field at <none>. Click on Next:
Select the fields you want to display in the chart, in our case only full name and job title. Click on Next:
Be sure to use the same fields you selected in the previous screen and click on Next:
In case you have pictures ready, you can select the folder and tell Visio which metadata field to connect it with. We are doing this afterwards, so click on Next
Our chart is relatively small so we can leave this with the default option. Otherwise, you can configure this yourself with the first option. Click on Finish:
Visio created an organization chart based at our Excel data that’s based at our SharePoint data! We can use the Org Chart tab in the ribbon to customize the look & feel:
Look at that! You have to admit, this is pretty awesome. We can also configure a connection between Visio and Excel to load new or modified data in Visio. Click in the ribbon on Data and Link Data to Shapes:
We pick the first option and click on Next:
Select the Excel workbook and click on Next:
Click on Next:
We only select the columns we use in our chart, click on Next:
We choose the second option and click on Finish. An additional screen is displayed with all of the Excel data.
To synchronize new or modified data, we click in the ribbon on Data and Refresh All. Before you get too excited, at least I really did, there is one major flaw I can’t seem to fix. You would expect to be able to drag the new entry to the diagram and it automatically gets the same look and feel as the current shapes. It doesn’t:
You have to manually adjust the shape. I searched everywhere on the Internet, but couldn’t find a solution. Although this may be dissapointing, there is a cool synchronization option between the current shapes and the SharePoint/Excel data. In case of a change within current employee information, for example the last name, you want Visio to automatically update the organizational chart. That’s possible! Click in the ribbon on Data and Automatically Link:
Click on Next:
We select the two fields we use and that’s full name and job title. Connect the data columns to the shape fields. Click on Next and Finish in the next window. All of the shapes get the callout with the data. We have to manually remove these by selecting all the shapes, right click, and click on Data and Remove Data Graphic. A change made in the SharePoint contact lists is synchronized with Excel and Visio synchronizes with Excel. Again, don’t get too excited because this isn’t perfect. A change in the hierarchy, for example a promotion or demotion, isn’t synchronized in the chart. You have to manually reload the chart and start over.
Final step: SharePoint
We want to share our beautiful organizational chart with the world, at least your internal SharePoint world, so it’s time to upload all data to SharePoint. Choose a document library for the Excel and Visio files. SharePoint comes with a web part to display Visio charts:
This completes the circle between SharePoint, Excel and Visio.
I realize this solution isn’t perfect because it can require manual adjustments after certain actions. Try to take a step back and see what you can do with all of these out-of-the-box features and it’s safe to say: That’s pretty awesome!