More along the lines of playing at the intersection of Visio reporting and Microsoft Office Project Server 2010.  The question I was looking at today was how to export the Resource Pool from Project Server into an organization chart format in Visio. 

I used the Microsoft demo image and was able to generate a rough Contoso org chart as appears below:

The first question is why would anyone want to do such a thing?  Honestly, I am not sure what that answer is quite yet, but this sort of report would seem to be a nice hook to add other reporting data to – for instance I can create an org chart, and then pair it with Availability/Work data, Risk/Issue data, or even a list of tasks – thus allowing me to navigate through an intuitive org chart format to get the data I want.  This exercise also allows me to see what’s possible, and what’s a bit less possible with Visio and the integration with Project Server.

The Visio Organization Chart Wizard

The Visio Organization Chart Wizard can import data from your Exchange Database or from an Excel table to automatically create an organizational structure.  When importing from Excel, the reporting dependencies are defined using a column called “Reports_to.”  For instance, if the table looks like this…

….then Visio will import a diagram that looks like this:

So, the first thing we need to do is define the reporting relationships within the Resource Pool.  There’s a number of different ways to do this, but if we wish to work with the Visio Org Chart Wizard, then we need to use names of other resources as part of our Reports To field.  In theory, we could use any field where a manager’s name might be listed, perhaps something like the Timesheet Manager or Default Assignment Owner.  I considered using those fields for this example, but my feeling is that the Timesheet Manager is rarely defined to the level of granularity that would be required to develop an org chart.

The Wizard can import data from Exchange – and as a result, you may not have to go through the following steps to develop an org chart using Project Server data specifically.  It would probably be much easier to just generate an org chart from AD and then use that for reporting purposes.

My assumption for this exercise is that we decided to go it alone, use Project Server data, and don’t want to mess with AD.

Preparing the Project Server Data

So in this case, I created a new field called Reports To and tied it to a Lookup Table listing Departmental Heads.  Here is the screenshot of the Lookup Table configuration.

You’’ll note that this is actually not too hard to keep updated, based on the fact that each of the items are tied to a resource using the Lookup Table GUID.  This means that I can swap out Lisa Andrews, the head of IT, with Landon Howell, the hypothetical new manager, and all of the resources will automatically take on the new change as an attribute.

Then I create a new Resource field called Reports To and tie it back to the Lookup Table.  You’ll note the exciting (to me anyway) feature introduced in 2010 that allows you to include comments on the new Enterprise Field.

In this case, I have set Don Funk as our corporate head that everyone reports to.

Now, we have one simple task to get the data ready – assign a Reports To selection to each of the resources in the Resource Pool.  In the future, I could envision this as being part of the new resource setup procedures – just like any other custom Resource field.  To accomplish this, I navigate to the Resource Center, filter on named resources, and open them all into Microsoft Project.

Again, here you’ll note some minor improvements over the 2007 interface.  In 2010, I can click on the column header in a PWA view and get a number of options to sort, filter and/or group the data.

Modify in Microsoft Project and save back to the Resource Pool.  I assign the Departmental Heads a Reports To of Don Funk, our hypothetical corporate CEO.  Don Funk’s Report To selection should technically be blank – but we can fix that later on when we import into Visio.

Save the data back to Microsoft Project Server, close the file, and we’re done with Project.

Getting the Data Into Excel

We now have a couple of options on how to get that data into Visio.  We can simply open up a Visio chart and link directly to the Resource_UserView table in the Reporting Database.  That will give us all of the data we want, but it would seem to bypass the Org Chart Wizard import mechanism that creates all of the reporting relationships for us.  Those dependencies can be rebuilt using some custom coding, but my preference is for a no code solution.  We could import directly from a SQL database, but I have to admit that after some research, I still haven’t quite figured out how to do that.  (But when I do, I’ll provide an update to this post).

Therefore, I had to go another route, bring the Resource Data into Excel, and then import it into Visio, thus allowing us to trigger the Org Chart Wizard to import the data.  This will not be a dynamic link, and we will have to refresh the workbook and reimport when the resource data changes significantly – but that would not be too labor intensive.

In this case, I could do a cut/paste from PWA or MPP to Excel, or in order to create a repetitive process, I am going to just create a new Office Data Connection back to the MSP_EPMResource_UserView table in the Reporting Database.  This will give me a spreadsheet that I can refresh on a routine basis to feed back into the Visio Org Chart Wizard to provide a new base for reports.  Open Excel, select Data, create a new ODC for SQL Server.  (We have to create a new ODC, because the default out of the box ODC files are hard coded with the default fields, and will not include our new custom Reports To field.)

Configure the ODC file to hit the right table in the database.

This will generate a table in Excel replicating the MSP_EPMResource_UserView table.  We filter out the generic resources and eliminate the superfluous columns.  (Note that can all be done when creating the ODC file as well).  You may wish to consider including the Resource UID field to link the org chart with future data from the Project Server database.  In this step, I am also going to rename the columns to match what Visio looks for in the import process – although that is not strictly required since we can also map the columns during the import process.

Save the resulting file to your desktop or to a SharePoint library.

Import into Visio

Now we can open up a new file in Visio using the Business > Organization Chart template.  Kick off the Org Chart Import Wizard, and point the dialog box to our existing Excel file.

Make sure the columns all map appropriately:

Pick the fields to display in the shapes.

Pick data (if any) to show next to the shapes.  My preference is not to show anything now, as it just clutters up the chart.

Choose the appropriate options for a single page vs. multiple page layout.

You can now modify the results to include a page for each department – or roll everything up to one page.

By selecting modify page, we can assign the overall oversight role to Don Funk.

And we now have an org chart with all of our Resource Pool nicely laid out – and ready for publishing to SharePoint using Visio Web Services.

We can do a lot of things with this org chart, things that I will be blogging about in upcoming posts.  Some examples may be:

1) Linking the report to other webparts as documented here.

2) Adding OLAP Cube or other Reporting Database data to the Report to provide a navigation interface for portfolio data.