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

By | July 21, 2010

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:

  • Amir Netz

    Very nice Kasper.

    I suggest you add images (perhaps store fronts?) to your report. The addition of the images is a huge cool factor. Try it.

  • Kasper de Jonge

    Hi Amir,
    Thx for the comment, i have been searching for some images but collecting a storefront for each store in the dataset took too long. But i agree the cool factor with images is way better 🙂

    I will find some images to add to my pivotviewer demo in the near future.

    Kasper

  • wsantos

    Excellent post! The only problem I have in implementing this is the 1000 rows limit. I understand the reason for it, but if I’m starting from a larger list, with filters to let me drill down to smaller sets, the initial load may require over 1000 rows. Is that a hard limit or something customizable? I implemented something similar in the past, but building a treemap. It could show thousands of objects, because you usually would drill into the few ‘interesting’ ones. In real life, almost all my scenarios would be of over 1000 rows.

    • Kasper de Jonge

      Hi @wsantos,

      From the documentation:
      The maximum number of cards that would possibly be put on the screen is 2000 (although the inventory size of the application is virtually unlimited). If a query that specifies the subset of items from an inventory that you want on the screen would return more than 2000 items, only the first 2000 encountered would show up). This is anyway in line with best practices as displaying more than 2000 items it’s visually not helpful. You typically want to focus your analysis on a subset, anyway (like the top 1000 account managers, for example, or products etc). Anyway, in the SharePoint solution, this limit is specified in the web.config file associated with the PivotCollectionService.svc (deployed under the ISAPI folder in SharePoint). Again, this limit is NOT for the size of the inventory of an application (which could be unlimited) but for the size of the items presented on the screen at a given moment.

      Kasper

  • Bernard

    Hi Kasper,

    Would like to know how we can pass multiple parameters? For example, Store and Time?

  • Chris

    Can you post a couple screen shots of this looks without the images? Thanks.

    • Kasper de Jonge

      Unfortunately I don’t have it running right now.

  • Can the crawler be scheduled?