This is another posting from our BI presentations last week in Houston and Dallas.  This is the first part of creating a dynamic Visio report, with the second part coming out later this week.  For that post, I plan to tackle the question of creating a dynamic Visio timeline linked to project data. 

In the meantime, this is the prerequisite information on how to modify the Office Data Connector (ODC) files to do single project reporting. (The same principles should apply to creating an ODC file in 2007.)

The default out of the box ODC files in Project Server 2010 are pretty good, but most users will eventually have to modify them – particularly if doing single project reporting in Visio. 

Here’s a quick way to modify the ODC files to get the results you’re looking for.

Scenario: I want to quickly create an ODC file that only displays the top level information for a specific project to support a Visio report.

1) Open the ODC file that appears closest to what you’re looking for.  This will create a new Excel document with the data connection parameters.

2) In the Data menu, select Connections to review the embedded data connection file.  Click on Properties.

3) Edit the data connection.  I find the easiest way is to just grab the command text at the bottom and paste it into Word.

4) Add the filter statement to the data connection.  To do this scroll to the bottom of the string, and add the filter before the ORDER statement.  In this case, I am adding the following statement:

WHERE MSP_EpmProject_UserView.ProjectName=’BI Demo 1’ AND MSP_EPMTask_UserView.TaskOutlineLevel=1

This will filter the ODC results to display a single project and only the top level (i.e. phase level) summary tasks for that project.

5) Paste the resulting statement back into Excel and test the results.  Note that you may have to redo the quotes around the filtered text, as Word will insert curvy quotes, whereas the statement requires vertical quotes.  There’s a way to do that in Word, but I couldn’t recall how at the moment.

6) Change the name of the connection at the top of the dialog box.

7) Export the connection as a new ODC file.

8) Post the ODC file back to SharePoint, or keep local to your desktop for future use.  You may now use this file to drive Excel or Visio reports.  My guess is that for those organizations using Visio reporting, it will probably become routine to create specialized ODC files for each project or program being reported on.