To the disappointment of many, it turns out that if you try to refresh data in Excel Services using an ODATA connection in Project Server 2013 it will error out and you may get a message like this one:
As we found out and as documented in the article “Project Server 2013 known issues” , Excel reports using ODATA connections can’t be refreshed in Excel Services which leaves users with the only option to refresh them in Excel using this workaround:
To refresh your data, open the file in Excel 2013, refresh the data, and then save the file back to the SharePoint document library where it resides. Next time the report is viewed using Excel Services, it will have the most recent data.
Unfortunately this is easier said than done since in a typical hosted scenario, unless you have access to Excel 2013 running in your Project Server farm or have a VPN between your desktop computer and your Project Server farm, you may be just out of luck. That also put you in a dead-end situation if you are trying to refresh Excel Services data from a Microsoft Project Online instance.
There is however a workaround or I should say a solution that is much more palatable. The solution designed by BeMo allows refreshing securely Excel data from various data source such as ODATA, OLEDB, ODBC and other without having to deal with the underlying implementation scenarios described earlier.
As a matter of fact, this implementation which is part of BeMo “Business Intelligence (BI) & SSRS subscription” can be activated in few minutes and enables our customers to perform a refresh directly in Excel for all data source type (ODATA feed included) and in Excel Services for all other non-ODATA data source types.
Regardless if you have a dedicated Project Server 2013 plan hosted by BeMo or are running on our cloud\multi-tenant platform BeMo Project Online, you are given the option to have our “Business Intelligence & SSRS” subscription which solves the short coming identified in this post. If you want to get a feel for it, all you have to do is to sign up for BeMo Project Online risk free and add the BI & SSRS option to your plan.
Please contact us at firstname.lastname@example.org or visit our website at www.bemopro.com at to learn more about our Project Portfolio Management (PPM) hosted solutions built on Microsoft SharePoint and Microsoft Project Server.