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
4

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

clip_image003

5

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

clip_image005

6

Excel launches and the report generates

image
7

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.

clip_image008

8

Highlight the existing connection and click the Properties button.

clip_image010

9

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]

image
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

Program

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

Now copy the entire text from NotePad
image
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
12

Now click the Export Connection File button.

image
13

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.

image
14

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.

image
15    
16

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.

image
17

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

Click the Save button.

image
18

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

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

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

Click the Cancel Button

image
21

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

image
22

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

image
23

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

image
24

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

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

image
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
31

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.

image
32

Note your updated connection information. Click Close.

image
33

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.

image
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
36

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

image
37

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

image
38 Double-click English (United States) image
39

Specify the following:

Name: (MPN) SimpleProjectsList

Save As Type: Excel Workbook

Open with Excel in browser checked

Click Save button.

image
40

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

image