I know I am way behind on my blog posts, but here is one that I wanted to share with you: PivotViewer Extension for Reporting Services CTP2 is released. You can download it here
PivotViewer Extension for Reporting Services CTP2 brings bug fixes as well as new features. This is a list of the changes:
3. Bugfix: PivotViewer Extension for Reporting Services does not work if the data contain null values. In CTP1 there was the workaround to change the data in order to avoid nulls (e.g. put a special value, like -1 in lieu of null). The bug was fixed in CTP2.
4. Bugfix: PivotViewer Extension for Reporting Services does not work if numeric values have too large or too small exponent. The issue was that numeric data was considered to be double. The default serialization of doubles will change to the scientific notation if the exponent is above 14 or below -4. The bug was fixed in CTP2 (by considering numeric data as decimal instead).
5. Feature: Support for arbitrary OleDb data. CTP2 supports now generic OleDb providers (e.g. Oracle or DB2) for building applications. This is in addition to the datasources supported in CTP2: PowerPivot (and Analysis Service) via Adomd.NET and SQL Server (via SqlClient). Please see more details in the “Supported Data Sources” chapter of the “PivotViewer Extension for Reporting Services.docx” document that is part of this distribution.
6. Feature: Support for links in facet data. CTP2 supports now hyperlinks in facet data. Please see more details in the “Dataset Query Specification” chapter of the “PivotViewer Extension for Reporting Services.docx” document that is part of this distribution.
7. Feature: Support for multivalued facet data. CTP2 supports now multivalued facet string data. Please see more details in the “Dataset Query Specification” chapter of the “PivotViewer Extension for Reporting Services.docx” document that is part of this distribution.
I recently ran across a SharePoint issue that prevented the generation of a thumbnail of workbooks in my PowerPivot gallery. First of all i use the blog post of Dave Wickert Troubleshooting Gallery specific issues to debug my problems. Using this I found a solution to the following problem:
In a few installations where I created a new web application using a hostheader, the PowerPivot thumbnails turned into red crosses.In my case the reason for this was that the GetSnapshot.exe is unable to reach the SharePoint gallery. In all these cases the Site was unreachable from within IE on the server console as well. Disabling the loopback check in SharePoint (http://support.microsoft.com/default.aspx?scid=kb;en-us;896861) resolved the issue and the Snaphots are generated ok again.
I love music, i listen to it a lot on my ipod and on my computer.I also love statistics, I send all the tracks I listen to the online music service last.fm where I have stored all the tracks i have played since 2006.
This is a list of my top artists (yes I am kind of a metal head):
Wouldn’t it be great if I could load all my played tracks into the PivotViewer? This blog posts describes how I got all my played track information into the PivotViewer. In a previous blog post is described how you we can make a PivotViewer application on top of PowerPivot. Today we are going to load data from Last.FM into PowerPivot and base a PivotViewer app 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:
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.
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:
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 :
We recently created a new web application on our SharePoint farm, we chose claims-based authentication as authentication option. All seemed ok until i wanted to create a report on top of the PowerPivot application in SSRS. I got a error “‘Too many automatic redirections were attempted” in the report builder 3. Searching on the Internet i can across a blog of Dave Wickert that explains that we need to run a PowerPivot web application in classic mode: Why PowerPivot requires ‘classic-mode’ web applications.
Unfortunately we cannot change back classic mode from a claims based web app. So we had to create a new webapplication and copy everything from one site to the other (or create a backup of your sitecollection and restore it in a new web application.
First of all the PivotViewer Extension for Reporting Services is ready for download here. The installation is described in an excellent installation guide that is included in the package, a must read for anyone who wants to start with the PivotViewer Extension for Reporting Services.
What do you have to do to install it into your SharePoint machine, in my case i have a single box. My sharepoint is at http://powerpivotand have integrated SSRS with the following service url http://powerpivot/ReportServer_SQL my SQL Server is installed at PowerPivot\powerpivot (machine name\instancename).
The following steps you need to do:
Open an command prompt with Administrator access and run:
BICollectionDBProvision.exe -sPowerPivot\powerpivot -op
This will create a database on a local database in my case “PowerPivot\powerpivot”
Open the SharePoint 2010 Management Shell command prompt with Admin access:
.\installBIPivot.ps1 “-whttp://powerpivot” -oi -y
This will install SharePoint specific features for the Pivotviewer, like the Crawler and viewer control on my SharePoint machine “http://powerpivo”. This syntax is different than from the intallation guide, i had to run it with .\ in front of it because my location wasn’t a certified location.
This is all you have to do. Remember this is not a replacement for the installation guide just gives you what i did.
Next up is installing the Demo app. Again open the command prompt with admin privilages and run
Now all you have to do is create a page in sharepoint and add a “PivotViewer BI Report Crawler” webpart to your page. Select the Accountmanagers invetory and start crawling, the report is now rendererd for all options in the pivot viewer.
Finaly create a second page and add the “PivotViewer BI Data Browser” webpart to the new page and in the properties set the Inventory to “Accountmanagers”. Your Pivotviewer app will now be working:
Again make sure you read the manual, i had trouble as well because i didn’t read it as well as i should !
Ok folks it is here, the CTP1 of PivotViewer for Reporting Services is ready for download. Check out the blog post of Cristian Petculescu from the team.
Get your free download from here. It is a concept project. It’s not supported, and not a feature.
However, it is really cool! It allows you to easily create stunning visualizations on top of your BI data – and these visualizations are fully dynamic: you decide at runtime which subset of data you should see, it shows the actual live numbers, and you decide how much (meta)data you associate with each card. Also, all of the cards are generated by the service, so it is very convenient. Of course it comes with sample data, and a tutorial.
I installed it this morning, it’s not really hard. You need to have Sharepoint 2010, powerpivot server (AS for sharepoint), reporting services in sharepoint mode installed on your machine.
data sources that are supported:
Although, the typical scenario is for data to be in PowerPivot, and the PowerPivot server (Analysis Services 2008R2 in SharePoint mode) is a required prerequisite, the following data sources are supported:
Analysis Services 2008R2 (Adomd.NET provider)
SQL Server 2008 or later (SqlClient namespace)
This is what i made this morning using the sample app that is supplied:
Next up will be a short install tips document and then i will be creating my own PivotViewer app on top of PowerPivot.
