Excel 2007 Pivot Table with SharePoint On Vista PC - works good after SP1

Re-published from here.

Our company is using Microsoft Excel 2007 with Microsoft Office SharePoint Server 2007 and Excel Services as the primary interface to access Analysis Services cube data. This works very well on Windows XP PC, but was almost unusable on Windows Vista PC. On Vista PC you would get just too many “Enter UserName/Password” dialogs.

Here is what would happen on Vista PC when you try to open Excel report with Pivot Table from the SharePoint:

• You would browse SharePoint document library and would find report with Pivot table(s) that you want to open in Excel.
• You would hit Open in Excel button and there would be message: “Do you want to open or save this file?”. So far no problems - hit Open.
• Then Excel would start, Pivot table would be loaded, then for a half of the seconds you would see “Enter UserName and Password” dialog. Then Excel would get a focus and would hide UserName/Password dialog and then another “Connecting” Dialog would pop up. After that you had to find that UserName/Password dialog and hit “Cancel”. There was no point entering UserName/Password and clicking on the “Save” check-box - that information was ignored. After hitting Cancel, if you have more than one pivot table in this Excel file, you would get another UserName/Password dialog for each Pivot Table. Just after that you could finally explore your pivot table.

Because of these annoying prompts we avoided using Vista OS.

But this week I saw interesting video about Windows Vista SP1 UAC improvements. So I decided to test if anything changes after installing SP1 on Vista PC.

It took me about 50 minutes to upgrade my Vista PC with SP1, and after that I noticed the difference right away - all these annoying prompts were gone. Now as soon as you hit Open button, Excel opens report and you can explore your Pivot Table data. Very nice, finally we can migrate to Windows Vista OS and recommend this to our clients. So if you are using SharePoint and Excel with Analysis Services, make sure you upgrade to Vista SP1.

There is also another warning that you can get after opening Excel 2007 with Pivot table:

Security warning: “Data Connection have been disabled”
(x) Help protect me from unknown content (recommended)
( ) Enable this content

You can get this warning on Windows Vista or Windows XP. To get rid of it you have to either add Excel file location to the “Trusted locations” or enable all data connections for external content in the Excel Trust Center. Step by step guide for this you can find in this post.