Our blog is filled with useful information. The latest information on Project Server, SharePoint Server and Project Management technologies. Whether you are troubleshooting error messages, attempting to build a master schedule or creating dashboard reports, our team of experts can help. Our blog is updated regularly, so please subscribe and check back often.

How To: Create New Excel Services report by modifying RDB connected ODC file

Step Action Details
1 Launch Project Web App and navigate to the Business Intelligence Center image
2 Navigate to Business Intelligence Center > Sample Reports > English (United States) image

Expose the contextual menu for the Simple Projects List > Edit in Microsoft Excel



If presented with a security warning, click the Enable Content button



Excel launches and the report generates


We are going to add a field to this report that is not currently part of its ODC file. Perform the following:

Select the Data Ribbon > Connections

If the Connections button is disabled, be sure to place cursor focus somewhere on the report.



Highlight the existing connection and click the Properties button.



When the Connection Properties dialog box opens, select the Definition tab.

Now you can see the SQL query that needs to be updated in the Command Text box.

What I do in this case, in order to understand the format of my select command, is highlight and select one of the fields being selected in the SELECT statement.  In this case I selected MSP_EpmProject_UserView.ProjectName as [ProjectName]

10 Next, Launch NotePad and past the selection.  The format of the command is basically the name of the view in which the field is being harvested from, followed by a period and then the name of the field.  The latter can be gathered directly from Enterprise Custom Fields and Lookup Tables from within Project Web App > Server Settings.  In the red box you can see I added the following fields

Project Status Summary (note I placed the field in brackets.  This is necessary when the field name contains spaces


Again, the format is TableName.fieldname.  Also note that a comma must separate the fields.

Now copy the entire text from NotePad
11 Returning to your Connection information, paste the previously copied text into the Command text property as shown to the right.  If you didn’t past over the previously selected Project Name field, it will appear twice.  The SQL will still work, however, you may consider deleting the duplicate so your pivot table field list will not show the field twice image

Now click the Export Connection File button.


Because we are editing an ODC file that is located in the Data Connections list, we are prompted to save the file here. This is good because this is where we ultimately want it.

Specify the name:

Project Server – (MPN) Simple Projects List

Save as Type: Office Database Connection

Click the Save button.

Note: Our other option would have been to save the file locally and upload it to the Data Connections list.


Next we are presented with the Properties page.

Content Type: Office Data Connection File

Title: Project Server – (MPN) Simple Projects List

Keywords: Project Server – (MPN) Simple Projects List

Click the OK button when finished.


In PWA, navigate Business Intelligence Center > Data Connections > English (United States).

Locate your new ODC file in the list. It should have the word “New” next to it.

From the Contextual menu, select Edit Properties.

Note: You expose this menu by clicking on the downward pointing arrow to the right of the ODC name while the cursor is hovering over it.


Update the Title to Project Server – (MPN) Simple Projects List.

Click the Save button.


Again From the contextual menu of your new ODC file, select Publish a Major Version.

19 If desired, enter a comment.  Finally, click the OK button. image

Return to your Excel file. The Connection Properties dialog box should still be open.

Click the Cancel Button


In the Workbook Connections dialog, highlight the existing connection and click Remove.


Click OK at the Removing Connection(2)… warning.


Still in the Workbook Connections dialog, click the Add button.


In the Existing Connections dialog box, click the Browse for More button.

To facilitate locating the ODC file, we’ll harvest a URL.

25 Navigate Business Intelligence Center > Data Connections image
26 Copy the entire URL up until just before /forms….. image
w7 Returning to the previous dialog box, past the URL you just harvested, then click the right-facing green arrow image
w8 Once the library is located, double-click English (United States). image
w9 Locate and highlight your ODC file in the list, then click the Open button. image
30 When the Workbook Connections dialog box returns, click the Properties. image

In the Connection Properties dialog box, do the following:

Uncheck the box for Enable background refresh.

Check the box for Refresh data when opening file.

IMPORTANT! Change the connection name to :Project Server – Simple Projects List. So basically removing the word (MPN). We do this because this connection name is the Pivot Table reference for the workbook, and renaming it will break the connection to the Pivot Table.

Click OK.


Note your updated connection information. Click Close.


Now your PivotTable Field List should reflect the newly added fields

Go ahead and add the new field to the report by ticking the checkbox in front of it.

34 Note the fields now added to the report!  Now what we will do is save this report to our Sample Reports library. image
35 Navigate to your Sample Reports Library.  Again, harvest the URL up to the /forms… image

Back in Excel, select File > Save and Send > Save to SharePoint > and double-click Browse for a location


Paste the harvested URL and click the Go To URL arrow.

38 Double-click English (United States) image

Specify the following:

Name: (MPN) SimpleProjectsList

Save As Type: Excel Workbook

Open with Excel in browser checked

Click Save button.


When successfully saved the report should automatically open in Excel Services!



Got something to say? Join the discussion »
    Posted @ 2/19/2014 11:14 PM by rightarmman
    rightarmman's avatar

    Great Article!
    How do I expose the table that contains the location of an image from the "Images" Web Part?

    Any assistance greatly appreciated....

    Posted @ 12/12/2013 5:05 AM by SuperUser Account
    SuperUser Account's avatar

    Sorry for the Delay! Actually I've not ever seen this error. Have you resolved it?

    Posted @ 11/19/2013 3:42 AM by H.A.M. Ster
    H.A.M. Ster's avatar

    Hi Daniel,
    Does the error message ring any bell?

    Posted @ 11/13/2013 10:42 AM by H.A.M. Ster
    H.A.M. Ster's avatar

    First one:

    Line: 123
    Char: 1
    Error: 'OWSForm' is undefined
    Code: 0
    URL: http://<...>/PWA/ProjectBICenter/_vti_bin/owssvr.dll?location=Data Connections for PerformancePoint/English

    Posted @ 11/11/2013 10:39 AM by Daniel Bell
    Daniel Bell's avatar

    What error are you receiving?

    Posted @ 11/11/2013 10:38 AM by H.A.M. Ster
    H.A.M. Ster's avatar

    Hi all,
    At step 14, I get script errors for a lot of fields.
    Does anyone have a suggestion how to solve this?

leave a reply

 [Quick Submit with Ctrl+Enter]

Remember my details
Notify me of followup comments via e-mail