Microsoft Project Support Blog

The place to come for Microsoft Project, Microsoft Project Server and Project Online support topics

Project Online: Updating OData connections in Excel for different PWA instances

Project Online: Updating OData connections in Excel for different PWA instances

  • Comments 8
  • Likes

Interesting question came in from one of our good partners, Campana and Schott, concerning the update of the connection string for Excel reports based on OData when moving the Excel books between different Project Online tenants.  This works just fine when just using the normal Excel features such as PivotTables – but you run into issues when you use add-ins such as the PowerPivot add-in for Excel 2013 – ( See http://office.microsoft.com/en-us/excel-help/whats-new-in-powerpivot-in-excel-2013-HA102893837.aspx for details of this interesting add-in ).

I’ll walk through the steps with a few screenshots just so it makes it clear what I’m talking about.

First I open Excel 2013, then on the DATA tab I select the From Other Sources option on the Get External Data section – and choose the OData option.  In the dialog I enter a link to my OData feed of “https://blogfodder.sharepoint.com/sites/pwa/_api/ProjectData/Projects

Showing the Url used for the OData feed - https://blogfodder.sharepoint.com/sites/pwa/_api/ProjectData/Projects

Then choose my Projects table (the only one showing as my Url was direct to my Projects)

Showing the Select tables option and the Projects table selected

And click Finish on the following dialog:

Showing the file name and friendly name of the odc file

At this point I could just create a connection but I’ll choose a PivotTable report, as I don’t have much data so it won’t take long.

Showing that I have slected a PivotTable report

Selecting Project Name and Project Work gives me my simple list of names and total of work – and show how imaginative I am with my project naming on my test server.

At this point I could save the Excel workblook to the document library of my site – but I’ll just save locally as this step doesn’t really matter.

I now wanted to use this against a different Project Online PWA instance – so I can just open the Connections option on the DATA tab,  select the datafeed and click Properties.

I have selected the DataFeed by name and can click the properties option to the right

Under the Definition tab of the Connection Properties I can then change the Url, both in the Source section, and in the Base Url section and all will be good!  I might also want to change the name – as it picks up the name from the original Url – but it doesn’t matter if you leave it the same (it will just confuse you and your users – but not Excel!)

Higlighting the position of the Url that can be changed in the Connection string

I get a message when I click OK telling me that the connection in this workbook will no longer be identical to the connection defined in the external file located in my …\Documents\My Data Sources\DataFeed_1_blogfodder-sharepoint-com.odc file – and it will be removing the link to the file.  I’m ok with that – so click Yes, and then close the Workbook connections dialog (when it has stopped thinking…)

Telling me that the connection in this workbook will no longer be identical to the connection defined in the external file

The page refreshes and I see the list of Projects from the new instance I am referencing – though the Project names are just as unimaginative!

Showing my name and work columns and the PivotTable fields pane to the right

So no problem to change the data connection in this simple case.  However, lets introduce PowerPivot into the mix.

I’ll follow the same steps – but this time I have activated the PowerPivot add-in for Excel 2013 – through the COM add-ins option in FILE, Options, Add-ins and Manage – selecting COM add-ins from the drop down:

Showing that I have Microsoft Office PowerPivot for Excel 2013 selected

I then have a POWERPIVOT tab and its own ribbon commands. 

Showing the POWERPIVOT tab and ribbon

First I’ll follow the steps above to create the data connection, which I can then use for PowerPivot.  I’ll not create the PivotTable this time.  Once I have this data connection I can click the Manage option which opens the PowerPivot window, and I can see it has pulled in my data

Showing the grid populated with columns from my Projects list

 

I am going to make a simple edit – just to simulate some customization of my data – and change the heading EnterpriseProjectTypeDescription to EPTDesc (Right-click column heading – Rename Column) then save my workbook.

Showing the grid populated with columns from my Projects list with my column renamed

If I now want that PowerPivot to point somewhere else I go back to the sheet and the original connection and it has a message for me – Some properties cannot be changed because this connection was modified using PowerPivot add-in – the connection string area is greyed out.

Highlighting the message - Some properties cannot be changed because this connection was modified using PowerPivot add-in:

And if I try and change in PowerPivot itself then all ways to change either directly in the feed Url or in the Advanced section just gives the same error – Cannot connect to the specified feed. Verify the connection and try again.  Reason: The remote server returned an error: (500) Internal Server Error.

Highlighting the error - Cannot connect to the specified feed. Verify the connection and try again.  Reason: The remote server returned an error: (500) Internal Server Error

The PowerPivot folks have confirmed this is a limitation when using PowerPivot and are putting together some documentation.

There are possible alternatives – Trutz Stephani at Campana and Schott used a combination of tables and additional calculated columns to make it easier to have a re-deployable workbook.  I took a look at Power Query too – part of the latest set of Power BI tools (Find out more at http://blogs.msdn.com/b/powerbi/) – but the connection string is not in any form where you’d know what to change to move between instances – so no help there.

Thanks to Trutz from Campana and Schott, and to Jules from our support team for suggesting this might make a useful blog posting.

Comments
  • This is a very inconvenient problem... where can I keep updated on the resolution?

    thanks for the post, I was going crazy with this..

  • Another shameful limitation of oData and hence Project online

  • All this to tell us you have a bug...and confirm you will document it but not fix it! This is causing me major pains! Unbelievable!

  • I feel your pain guys and sorry we don't have a better story. Anonymous - I hope this gives some ideas for workarounds - better you have some information and know what we are doing than let you struggle in the dark. Best regards, Brian.

  • Any updates ?

  • Thanks Brian for sharing.
    We've encountered this problem several times whilst migrating between a test environment and the production environment. Now we just rebuild the reports, but do you know of any workarounds? Or if there are improvements made by Microsoft to solve this problem?

  • Brian,

    In Sharepoint 2010, we created excel 2010 documents with powerpivot and published it on Sharepoint 2010 and I used the scheduling tool on Sharepoint 2010 that refreshed daily.

    Then we migrated to Excel 2013 and Sharepoint 2013.

    I took all the existing documents in Excel 2010, created TabularAS, created ODC connection and changed the connection from powerpivot to Tabular AS, no problem. There were only one powerpivot connection.

    The problem now is doing the same with a report build in Excel 2013. As soon as I want to replace the excel connections with the TabularAS connection, the connection string under the definition tab has been greyed out. In Excel 2010 there were one powerpivot connection, in Excel 2013, there is mutiple connections that is greyed out. We can't rebuild all the reports using new ODC connections because there's to much.

    Any suggestions.

  • I have successfully used PowerPivot to report on Project Online data using Fiddler custom rules. See the "authenticate" section of the following post: http://mikesnotebook.wordpress.com/2014/06/03/reporting-with-project-online-attempt-2/

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment