As you’ll quickly find in Project Server 2010, if you want to use the default Business Intelligence Center data connections then in order to include your own custom fields, then you’ll need to update the connectors. Here’s a quick guide on doing that to get to that data for your first Project Status Report.

Before I get into it don’t forget that the OLAP connections are automatically created, so in many cases you can use those, but if you’re like me and prefer direct reporting from the Reporting Database then you’ll have to create your own connections.

Step 1: Start from an existing connection file

For this I am going to use "Project Server – Project and Task Data" from the default location "BC CenterData Connections – English (…)", find the existing file and save it somewhere locally using the Send to – Download a Copy option.

Now open that ODC file in Notepad and you’ll see the XML contents and hopefully the important part the section labelled <odc:CommandText> where the SQL "SELECT …" statement is, that’s what we need to change to include our custom fields.

Step 2: Add your Custom Field details using SQL Management Studio

Using SQL Management Studio is the easiest way to confirm that you have the right field names to add to the query, to do that find your ProjectServer_Reporting database and in particular what you will want is the View named dbo.MSP_EpmProject_UserView (or one of the others if you want task or resource fields). Select the view and using the ‘Select Top 1000 Rows’ option to generate a SELECT query returning all data.

You’ll end up with something like this:

/****** Script for SelectTopNRows command from SSMS ******/

SELECT TOP 1000 [ProjectUID]

,[ProjectName]

[ …. Snip lots of built in fields….]

,[ProjectBaseline10Duration]

,[Project Departments]

,[Sample Business Need]

,[Sample Areas Impacted]

,[Sample Proposal Cost]

,[Sample Compliance Proposal]

,[Sample Assumptions]

,[Sample Goals]

,[Sample Post Implementation Review Date]

,[Sample Post Implementation Review Notes]

,[Sample Proposed Start Date]

,[Sample Proposed Finish Date]

,[Sample Approved Start Date]

,[Sample Approved Finish Date]

FROM [PWA_ProjectServer_Reporting].[dbo].[MSP_EpmProject_UserView]

The nice thing is in the views all the custom fields are listed last, as you can see from above the out of the box ‘Sample’ fields are all there to see. So next you just have to copy those field names into the ODC file opened earlier and save your changes.

Step 3: Update the ODC file

The only catch with updating the ODC file as you probably noticed is the format includes the view or table name prefix, such as:

MSP_EpmProject_UserView.ProjectBaseline10FinishDate as [ProjectBaseline10FinishDate],
MSP_EpmProject_UserView.ProjectBaseline10Duration as [ProjectBaseline10Duration],

So all you need to know is that unless your changing the name you don’t need the bit after ‘as’, so if you search and replace to prefix each of your custom fields to add the "MSP_EpmProject_UserView." before the "," so you end up with something like:

,MSP_EpmProject_UserView.[Sample Business Need]
,MSP_EpmProject_UserView.[Sample Areas Impacted]
,MSP_EpmProject_UserView.[Sample Proposal Cost]

Now the last step is to paste those lines into the ODC file in Notepad, but note that the commas are at the start not the end, so you just need to make sure that when you paste the new lines that you have just one comma between each line except for the last line before the "FROM". You should end up with something like this:

MSP_EpmTask_UserView.TaskBaseline10Duration as [TaskBaseline10Duration],
MSP_EpmTask_UserView.TaskBaseline10DurationString as [TaskBaseline10DurationString],
MSP_EpmTask_UserView.[Health] as [Health]
,MSP_EpmProject_UserView.[Sample Business Need]
,MSP_EpmProject_UserView.[Sample Areas Impacted]
,MSP_EpmProject_UserView.[Sample Proposal Cost]
FROM
dbo.MSP_EpmProject_UserView
INNER JOIN dbo.MSP_EpmTask_UserView ON
MSP_EpmProject_UserView.ProjectUID = MSP_EpmTask_UserView.ProjectUID
ORDER BY
MSP_EpmProject_UserView.ProjectName,
MSP_EpmTask_UserView.TaskIndex,
MSP_EpmTask_UserView.TaskName
</odc:CommandText>

Step 4: Upload the new ODC file to your Data Connection library

Now your just about done, save the file and back in your BI Center upload the file to a new location, make sure that you don’t try to overwrite the default file as those may be replaced by future service packs, I upload them directly into the Data Connections library.

Once uploaded you can now use them in Excel as normal. You might want to update or copy the existing templates and edit the data connection properties to point to this new ODC file, or otherwise just add them in as you normally would and carry on..