|Step ||Action ||Details |
|1 ||Launch Project Web App and navigate to the Business Intelligence Center || |
|2 ||Navigate to Business Intelligence Center > Sample Reports > English (United States) || |
|4 || |
Expose the contextual menu for the Simple Projects List > Edit in Microsoft Excel
|5 || |
If presented with a security warning, click the Enable Content button
|6 || |
Excel launches and the report generates
|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.
|8 || |
Highlight the existing connection and click the Properties button.
|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]
|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 || |
|12 || |
Now click the Export Connection File button.
|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.
|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.
|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.
|17 || |
Update the Title to Project Server – (MPN) Simple Projects List.
Click the Save button.
|18 || |
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. || |
|20 || |
Return to your Excel file. The Connection Properties dialog box should still be open.
Click the Cancel Button
|21 || |
In the Workbook Connections dialog, highlight the existing connection and click Remove.
|22 || |
Click OK at the Removing Connection(2)… warning.
|23 || |
Still in the Workbook Connections dialog, click the Add button.
|24 || |
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 || |
|26 ||Copy the entire URL up until just before /forms….. || |
|w7 ||Returning to the previous dialog box, past the URL you just harvested, then click the right-facing green arrow || |
|w8 ||Once the library is located, double-click English (United States). || |
|w9 ||Locate and highlight your ODC file in the list, then click the Open button. || |
|30 ||When the Workbook Connections dialog box returns, click the Properties. || |
|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.
|32 || |
Note your updated connection information. Click Close.
|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.
|34 ||Note the fields now added to the report! Now what we will do is save this report to our Sample Reports library. || |
|35 ||Navigate to your Sample Reports Library. Again, harvest the URL up to the /forms… || |
|36 || |
Back in Excel, select File > Save and Send > Save to SharePoint > and double-click Browse for a location
|37 || |
Paste the harvested URL and click the Go To URL arrow.
|38 ||Double-click English (United States) || |
|39 || |
Specify the following:
Name: (MPN) SimpleProjectsList
Save As Type: Excel Workbook
Open with Excel in browser checked
Click Save button.
|40 || |
When successfully saved the report should automatically open in Excel Services!