Depicting Detailed Timeline Views with Visio Reports

This is a question that’s come up before, i.e. how to depict a detailed (but not too detailed) summary timeline of project data in SharePoint.  In fact, I’ve even blogged it before (here for standalone projects, and here for portfolio timelines).

image

Seeing as I had to develop an example for the recent Project Conference, I figured it was worth revisiting.  This is pretty much the same story, but has been streamlined a bit to make it easier to use.

Below is another screenshot.  The format is different than the timeline view above, but the technique to develop should essentially be the same.

image

Preparing the Visio File

After a couple tries, I figured the easiest thing was to take an offline copy of the project schedule, then import it into one of the Visio schedule views. 

image

That yields something like this.

image

Preparing the ODC File

From here, it’s a simple step to prepare the ODC file.  In previous incarnations of this exercise, I simply imported the data and then manipulated it within Visio.  This time around, I realized it’s easier to just modify the ODC to bring the data in the right format into the Visio file.  The main thing we do with this ODC file is convert the field names to map to the out of the box fields built into the Gantt Chart.

Here’s the SQL query…

SELECT     dbo.MSP_EpmProject_UserView.ProjectName AS Project, dbo.MSP_EpmTask_UserView.TaskName AS Name, 
                      dbo.MSP_EpmTask_UserView.TaskStartDate AS [Start Date], dbo.MSP_EpmTask_UserView.TaskFinishDate AS [End Date], 
                      dbo.MSP_EpmTask_UserView.TaskDuration AS Duration, dbo.MSP_EpmTask_UserView.TaskPercentCompleted AS [Percent Complete], 
                      dbo.MSP_EpmTask_UserView.TaskActualDuration AS [Actual Duration], dbo.MSP_EpmTask_UserView.TaskActualStartDate AS [Actual Start], 
                      dbo.MSP_EpmTask_UserView.TaskActualFinishDate AS [Actual End], CAST(dbo.MSP_EpmTask_UserView.TaskUID AS nvarchar(100)) AS UID
FROM         dbo.MSP_EpmProject_UserView INNER JOIN
                      dbo.MSP_EpmTask_UserView ON dbo.MSP_EpmProject_UserView.ProjectUID = dbo.MSP_EpmTask_UserView.ProjectUID
WHERE     (dbo.MSP_EpmProject_UserView.ProjectName = N'Acquisition Target Analysis')

The only hiccup here is that you have to add the filter for the project name into the ODC file.  There’s probably a workaround here to just create a universal ODC, but that seems the simplest route to get to where we’re going.

If that’s an issue for you, then some alternative approaches to try might include creating a custom SQL view with the appropriate field aliases.  I seem to recall Visio prompting the user to filter rows when I pointed it directly at a SQL database.  That might allow the user to control the project to be used.  There may also be a Visio VBA angle here, but I wouldn’t know where to begin.

Mapping the Data to Visio

Back in the Visio file, select the option to Link Data to Shapes.  Note that this option only appears in Visio Professional.  If you’re using Visio Standard, you’ll need to upgrade to get the option to connect to external data.

image

Select the option to connect to the ODC file.

image

Follow the default options until you get to the option to set the ID that Visio will use to map the data to the shapes whenever a refresh is triggered.  Set this to use the UID field in the ODC.

image

The data now appears in the External Data window at the bottom of the screen.  Go back up to the Ribbon and select the option to automatically link the data.  Map the data by the name.  This is only for the first time we map the data.  Any data refreshes will be controlled by the UID field.

image

The data is now linked to the shapes…..and will refresh based on the UID data field.  Save the file to Visio to leverage Visio Services to display.

Caveats

Note as a caveat that the file has to be opened in Visio to refresh the position of the shapes.  The data will refresh automatically in Visio Services (if configured properly)….but the X or Y position of the shapes will not change until the file is opened in Visio and the data refreshed.

Extra Credit

If you really want to amaze your friends, note that you can connect the Visio WebPart to a list.  So for instance, as you click around on the Visio Webpart, you can send the name or UID of the item selected to another Webpart – then have the list display specific information about the selected item.

Advertisements
Depicting Detailed Timeline Views with Visio Reports

4 thoughts on “Depicting Detailed Timeline Views with Visio Reports

  1. Barry says:

    Could you use this to create the equivilant of a Timeline View in a Project Site by leveraging the ProjUID Filter, or is that over complicated? Looking for a way to show the Timeline view on the Project Site as i feel this is a very powerful tool that is unfortunatly limited to Project Prfessional as Standard

    Thanks

    1. Theoretically possible, but a bit more complicated than may be feasible. The issue I’ve found is that it’s a manual step to get it set up for each project….haven’t found a good way to programmatically map it to new projects.

  2. Alejandra Baldor says:

    Hi Andrew,

    I am relatively new to Project 2010. I’ve been following your blogs about integration of Visio and Project and I was able to replicate your examples for timeline and gantt chart (only 1 project at the time).
    My company wants to have the timeline in a web part and pass the ID of the project from another web part. This way the timeline would refresh depending on the project selected. Is this feasible? what is the best approach? Thank you

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s