Project Online and Office 365
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”
Then choose my Projects table (the only one showing as my Url was direct to my Projects)
And click Finish on the following dialog:
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.
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.
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!)
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…)
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!
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:
I then have a POWERPIVOT tab and its own ribbon commands.
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
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.
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.
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.
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.
I am very pleased to announce the release of the August 2013 Cumulative Update (CU) for Project, Project Server and SharePoint for 2010 and 2013. Feel free to open a support case if you have any questions around this or need assistance getting these patches deployed. Remember that this release for the 2010 products, like all CUs since the August 2012 Cumulative Update, has a hard requirement on Service Pack 1 – see notes below. In most of the KB articles the term hotfix is used in place of Cumulative Update. They tend to be interchangeable terms – a Cumulative Update is just a hotfix built to a schedule. I should also point out that the individual Project Server packages are only ‘individual’ in the sense that they do not include the SharePoint patches – they are still cumulative and the August CU will contain all previous CU releases (at least back to the applicable baseline Service Pack (SP1 for 2010 and March PU in the case of Project Server 2013).
An important point for Project Server 2013, as was the case also for the April CU, is the requirement to load the March 2013 Public Update. Another important point to add here is that there was an issue with running the SharePoint Configuration Wizard on a server with Project Server 2013 installed – this is fixed by applying the April or June CU – so a good practice would be to load the March PU, then the August CU and then run the configuration wizard (if you didn’t already load the April CU).
Finally. the August 2013 CU for Microsoft Project and Project Server 2010 is the first patch that can be installed with Microsoft Project and Project Server 2010 SP2.
This include a number of fixes, so Microsoft strongly recommends that you test this in a test environment based on your production environment before putting this fix live in production.
The article below provides information on how to deploy the Project Server Cumulative Update.
You can read about the fixes included in the Project and Project Server August CUs from the following articles:
Description of the Project Server 2013 cumulative update package (Project server-package): August 13, 2013
Description of the Project Server 2013 hotfix package (Projectserverwfe-x-none.msp): August 13, 2013
This updates the Project Server database to version 15.0.4525.1000 – and in 2013 with just one database the table to look in is dbo.Versions. The Versions tables in the other schemas are empty.
Description of the Project 2013 hotfix package (Project-x-none.msp): August 13, 2013
The client version number will be 15.0.4535.1000 if you want to restrict the client version that can connect to the server.
We are working on the specific CU installation options for 2013, but the process hasn’t changed from 2010 – so if you are familiar with 2010 patching or read the 2010 instructions below you should be good to go.
Also note that Click to Run installations will be automatically patched, usually within a month. Installations in Enterprise Environments that have been modified will be deployed based on the schedule determined by your Administrator.
Server Rollup Package(Recommended):
*** Update 9/13/2013 - Please see http://blogs.technet.com/b/projectsupport/archive/2013/09/13/project-server-2010-august-2013-cumulative-update-installation-latest.aspx ***
*** Update 9/12/2013 - There appears to be an issue - hold off installing for now - see http://blogs.technet.com/b/projectsupport/archive/2013/09/12/project-server-2010-service-pack2-and-august-2013-cumulative-update-installation-issues.aspx
Also note there is a re-release if you follow the link below***
Description of the Project Server 2010 cumulative update package (Project server-package): August 13, 2013
Individual Project Server Package (Cumulative – but only the Project Server patches):
Only required if you do not install the Server Rollup.
Description of the Project Server 2010 hotfix package (Pjsrvwfe-x-none.msp): August 13, 2013
The Project Server databases will be updated to version 14.0.7104.5000
Project 2010 Client Package:
Description of the Project 2010 hotfix package (Project-x-none.msp): August 13, 2013
The client version number is 14.0.7106.5000, in case you want to limit the connection of certain patched release to Project Server 2010.
More information on deploying the Cumulative Update:
Updates for Project Server 2010
As Project Server 2010 is now based on SharePoint Server 2010 we strongly recommend that you install the Project Server 2010 Server Rollup Package as there are a large number of individual server packages for SharePoint Server. The Project Server 2010 Server Rollup Package contains all the patches released in this Cumulative Update for SharePoint Foundation Server 2010, SharePoint Server 2010 and Project Server 2010.
As mentioned above, and at http://blogs.msdn.com/b/brismith/archive/2012/07/18/project-server-2010-from-august-2012-cumulative-update-onwards-you-will-also-need-to-load-sp1.aspx – the August Cumulative Update requires your client and server to already be at the Service Pack 1 (SP1) level – if you get a message saying the patch does not apply to your system then this may be the reason.
SP1 for the Project Server 2010 can be found at http://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=26636
SP1 for the Project Professional 2010 client can be found at http://www.microsoft.com/en-us/download/details.aspx?id=26635 for the 32-bit and http://www.microsoft.com/en-us/download/details.aspx?id=26627 for the 64-bit.
The instructions for installing the client patch are below.
NOTE: Microsoft strongly recommends testing within a NON-Production environment prior to rollout.
1. Download the hotfix from the link in the KB Article.
2. Extract the patch package by running the .exe file that you downloaded.
3. Run the extracted .exe file to apply the patch to your Project Professional/Standard client.
Reposting this from our Sustaining Engineering Blog since it applies to Project and Project Server as well. The Office 2010 June 2013 CUs will not install on Office 2010 SP2. You can, however, get the changes in the June 2013 CU in addition to the new ones in the Office 2010 August 2013 CUs. We are working towards a scheduled release date of August 12, 2013. That date could change should we find an issue we need to fix before we can release it. Of course, we work hard to make sure that it will release as scheduled as we realize the impact delays have on our customers.
Sustaining Engineering Update:
Service Pack 2 and the June Cumulative Update
Thanks to Anil for point this out!