You’re on your way to PowerPivot for SharePoint functionality – you’ve uploaded your PowerPivot for Excel workbook to your SharePoint PowerPivot Gallery.  You view the thumbnails of your report and they look nice.

 

From the thumbnail, you click on the report you want to see, and the report renders nicely.

 

But then you click on a slicer, and then all of a sudden you get an error like the one below.

 

What can you do?  Well, for starters, the key thing is to

%commonprogramfiles%\Microsoft Shared\Web Server Extensions\14\LOGS\

folder and review the ULS logs which contain a lot of information.  It will be the first place that will indicate to you what the potential problems are.  Later, I’m sure Dave or I will have a blog on that topic all by itself.

 

Back to the PowerPivot and Excel Services connectivity issue, if often has to do with firewalls and configurations.  First and foremost, please ensure that you have followed the instructions provided with the CTP downloads.  For example, the CTP3 installation of PowerPivot is slightly different than CTP2 so you will want to get the updated documentation.

 

If this doesn’t work, some options to try include:

• Ensure that the services like “SQL Browser” service is running under LOCAL SERVICE account.  If the domain controller is on the same box as your server, then you may need to have the browser running under the same account as your PowerPivot service accounts (i.e.. Analysis Services)
• Open up the firewall ports for Excel Services – which is 32843.  Often the connections failing due to data refresh are due to the firewall inadvertently being closed (or not opened).
• Allow access within the firewall for the %Program Files%\\Microsoft SQL Server\MSAS10_50.MSSQLSERVER\OLAP\bin\msmdsrv.exe. For all of you Analysis Services folks, you know its the Analysis Services engine.
• Note, even at this point, you may not be able to get SSMS to connect to the Analysis Services service to determine if the database (extracted from the workbook) was ever attached to the Analysis Services (IMBI) instance.  To do this, make sure you open ports 2382 and 2383.

Note, this is one solution to one particular connectivity problem – if it does not solve yours, first of all – sorry! and second of all – start looking at those ULS logs, eh?!

Hope this helps!