Getting the PivotViewer Extension for Microsoft SQL Server Reporting Services working

At the SharePoint Conference this year, I watched … session on DIY BI in which the PivotViewer extension tweaked my curiosity. I’d used the PivotViewer in the past and created my own expamples of using it to analyse SharePoint User Profiles, the most painful thing about this experience was in generating the zoom images and XML that just seemed to cumbersome. I was hoping the extension from the BI Labs would take out the pain and demonstrate the value of this BI tool.

I decided to install this on my Windows 7 machine that is running a local copy of SharePoint although this isn’t listed as a supported operating system, it worked fine but not without some headaches along the way. In the process I stumbled upon the same problems as others and some new ones so thought I’d share these so you have a better experience.
If you want some more detail on this tool then visit Kasper de Jonge’s blog on the subject also provides further insites

Firstly, you need to download the project and unzip it to the SharePoint server or the laptop in my case. There is a word document detailing the steps that is really good but I struggled with the following in the setup and then example deployment:

• I didn’t have PowerPivot installed which is a pre-requisite. You’ll need to run the SQL Server setup to deploy this. Once you get PowerPivot installed, you need to deploy it to your web application as this is not done by default otherwise you’ll get Could not load type ‘Microsoft.AnalysisServices.SharePoint.Integration.ReportGalleryView’s.
• Don’t be tempted to install SQL Server 2012 Reporting Services rather than SQL 2008 SSRS. Although you can create a service and point the database to SQL Server 2008, when you go to the service configuration page, it pops up a Correlation ID warning that it’s not the correct version of SQL. And when you uninstall it, it also removes the SharePoint SSRS add-in which you will need to re-install by running the rsSharePoint.msi file from the SharePoint install.
• When you configure reporting services in SharePoint, it may complain that it must use a secure connection to fix this, you need to update the key <Add Key=”SecureConnectionLevel” Value=”0″/> in rsreportserver.config file from 2 to 0 and then restart the Report Services.

Now we can start deploying the example:

• The first step of the sample deployment is to run the BICollectionDBProvision.exe that uses an example parameter of -scristiandt\powerpivot. If you installed PowerPivot with the default settings, you would end up with an Instance of SQL Server Analysis called “POWERPIVOT”, do not be fooled by the example parameter as this is not referring to this analysis services instance but a SQL Server instance. So provide the Server Name and Instance of the SQL Server 2008 Database Engine.
• You’ll probably hit a connection issue at this point. To fix this you need to enable TCP/IP for Network and Client connections in the SQL Server configuration manager.
• The next step is to run the .\installBIPivot.ps1 which worked successfully and deployed the solutions
  
• The sample builder which deploys a sample report, people images and a PowerPivot spreadsheet all worked well but it didn’t add entries to the “BI Collection Administration” list so I had to add these manually by copying the values from the SampelData/application.xml file
  
• Now I could add the Silverlight application generator file; remember to set height to 800 otherwise you don’t see errors and, errors are exactly what I got “500 internal server error” as did Peter who commented on Cristian Petculescu blog about this project. The report worked fine and I checked the parameter configuration and spent a while trying to call the SSRS report in the browser with the AccountManager parameter but as this was in the form of an MDX value, you can use the querystring only a form submit due to the “&” character.
  Turns out to be an issue with the AccountManagerEx inventory as the AccountManager inventory entry in the SharePoint list worked fine

Hopefully you’ve now managed to configure everything as I did in which case you will see this:

I don’t know if the people images are meant to be there, but they are not in the sample report.

This diagram illustrates how the build process works for the sample:

What I haven’t figured out is where the properties are stored or whether these are generated on the fly by the data browser rather the application generator

And I thought this was going to be simple to do when compared to creating all the XML files and zoom images. Maybe it will for my next BI report.