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).
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.
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.
That yields something like this.
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.
Select the option to connect to the ODC file.
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.
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.
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.
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.
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.