Build your own Pivotviewer app using the PivotViewer for SSRS on top of PowerPivot

With the release of the Pivotviewer for SSRS application we finally can build our own PivotViewer app on top of our data. In this blog we are going to build an app on top of a PowerPivot sheet.

I have a PowerPivot file that i use for presentations where i loaded data from the contoso database. The scenario is that we use this PowerPivot application to determine which stores have little products on stock.

The PowerPivot sheet looks like this:

To gain even more insight into this scenario, we want to see if there is a relationship between the stores that have little products on stock per employee. We are going to build a PivotViewer app on top of this PowerPivot file. First thing we have to do is of course install the PivotViewer Extension for Reporting Services as I described in a previous blog post.

To create a PivotViewer application we have to do two things:

• Create the images that will be the representation for each store. We create this image by using a SSRS report to be used in the PivotViewer BI Report Crawler to automatically create images for each store that will feed the PivotViewer.
• Next we have to configure the PivotViewer application using the BI Collection Administration SharePoint List. Here we configure what data will be used to navigate through the Pivotviewer data and show data for each store.

We start by creating the SSRS report. As datasource we use the PowerPivot app that is published to SharePoint.

As we can see here i have created a report which gives me the details for a store. This is very important because the report will be used to generate thumbnail for each store. We need a parameter that can be passed through for each store, i just dragged in the Store dimension at filter part of the query designer and selected parameter.

Secondly we  need to remember this report will be used to show in the PivotViewer, we need to be able to identify which store this is and what the state of this store is. In the demo done by Amir Netz at the MS Bi Conference keynote and also at the included PivotViewer demo they have added a background color so we can quickly identify the state of the a store.
So we go to the report Body and set the backgroundcolor dynamically depending the results:

=iif(first(Fields!Sum_of_DaysInStock.Value, “DataSet1”) / first(Fields!EmployeeCount.Value, “DataSet1″) > 70000,”Red”,
iif(first(Fields!Sum_of_DaysInStock.Value, “DataSet1”) / first(Fields!EmployeeCount.Value, “DataSet1″) > 50000,”Orange”,
iif(first(Fields!Sum_of_DaysInStock.Value, “DataSet1”) / first(Fields!EmployeeCount.Value, “DataSet1″) > 30000,”Yellow”,
“Green”)))

When i run this i get the following report for a specific store:

We save the report in the PowerPivot gallery on sharepoint at the same location as the PowerPivot file:

Now we have to go and create the collection of items for the Pivotviewer. We have to configure the BI collection administration to create a image for each item and determine what data we want to show in the PivotViewer for each item.

In Inventory we need to supply a unique name, we call it StoreInventory.

Next we have to configure the report, first we have to point to the location of our report with a URL that will render a PNG file and is able to pass a parameter to it, we can use .net like parameter syntax to give the location of the parameter in the string, in my case this was:
http://powerpivot/ReportServer_SQL?http://powerpivot/storedemo/viewerreport.rdl&rs:Command=Render&rs:Format=image&RC:outputformat=PNG&DimStoreStoreName={0}

And as description i used “store report”.

I’m going to skip the Dataset for a second because i want to finish the report parameters.  These parameters will determine what parameters are going to be passed to the report while generating a thumbnail for all the stores. As Report Parameter Datasource we select the location to the PowerPivot file: “data source=http://powerpivot/StoreDemo/bigdemo.xlsx”.

At the Report Parameter Datasource Type we use “Adomd.net”. As Report Parameter Query I have created a simple MDX that returns all the stores:

select
[DimStore].[StoreName].[StoreName].members
on 0 from [Sandbox]

At the Dataset part of the collection admin tool we are going to configure what data has to be shown for each items of the PivotViewer. So in our case we want to show data for each store, I want a store to have these properties:

• StoreDescription
• ContinentName
• RegionCountryName
• Stateprovincename
• EmployeeCount
• DaysInStock
• AverageDaysinStock

As Dataset Parameter Datasource we  again select the location to the PowerPivot file: “data source=http://powerpivot/StoreDemo/bigdemo.xlsx”.  At the DatasetParameter Datasource Type we use “Adomd.net”. As some of the requested properties are not in the dimension Store I altered my PowerPivot file and added the columns as calculated column to the Store table. Now all we have to do is add a simple MDX Query:

select
[DimStore].[StoreName].[StoreName].members *
[DimStore].[StoreDescription].[StoreDescription].members *
[DimStore].[ContinentName].[ContinentName].members *
[DimStore].[RegionCountryName].[RegionCountryName].members  *
[DimStore].[Stateprovincename].[Stateprovincename].members *
[DimStore].[EmployeeCount].[EmployeeCount].members *
[DimStore].[DaysInStock].[DaysInStock].members *
[DimStore].[AverageDaysinStock].[AverageDaysinStock].members
dimension properties
[DimStore].[DaysInStock].[DaysInStock].key,
[DimStore].[AverageDaysinStock].[AverageDaysinStock].key
on 0 from [Sandbox]

a few things are interesting to notice: first of all the order of items is importent, the order you put in here is the order that you can drill down into the data when using Pivot Viewer:

A second thing that is importent is how you want to show your numerical data. By default a numerical value will be displayed as an string like this:

But a numerical field contains a lot of different values a choice of all of them is not very userfriendly. You can make the PivotViewer aware that this is a numerica value by adding a dimension property to the dataset. In my case i added :

dimension properties
[DimStore].[DaysInStock].[DaysInStock].key,
[DimStore].[AverageDaysinStock].[AverageDaysinStock].key

This will make sure we have a value slider for the DaysInStock and the AverageDaysinStock like this:

Ok now we have filled in all the properties for this PivotViewer app.

We now can start preparing the data by running the crawler for our newly added item:

The database is now filled with information about the stores that will be used in the PivotViewer app:

Since the data is available we now can add our PivotViewer BI Data Browser to a new page in sharepoint. We just add this webpart to your page:

And configure it, all we have to is point the webpart to our newly added item by typing the exact unique name in the Inventory field:

When you press OK the control should be succesfully loaded and you are ready to go: