PivotViewer and Reporting Services

Microsoft has released a cool new extension for Pivot that integrates SSRS reports: PivotViewer Extension for Reporting Services.

This extension, which does require SSRS 2008 R2, allows you to use SSRS to generate the collection of cards that the PivotViewer will display. There’s a fair amount of information out there to walk through the installation of the extension, but I found that there wasn’t quite as much robust information on actually using the extension for end-to-end creation of a collection. So, here’s an overview of what you’ll be doing!

Once the extension has been installed and the pivot site set up, the basic process is:

• Create a parameterized SSRS report
• Load the RDL for that report up to a SharePoint pivot gallery
• Set up a BI Collection definition
• Create a SharePoint page to host the BI Report Crawler
• Run the BI Report Crawler for your collection definition
• Create a SharePoint page to host the PivotViewer, add the PivotViewer web part and point it at your collection


Got all that? OK, let’s take a look at the details for each step:

• Create a parameterized SSRS report
o The SSRS report is used to create the tiles in the Pivot collection. It needs
to take a parameter (or parameters) that will create one unique card for every
parameter value – something like an employee ID, for example.
• Load the RDL for that report up to a SharePoint pivot gallery
o Since I was using a PowerPivot datasource, I put the RDL file and the datasource in the same folder for convenience, but you can put it wherever makes sense for you.
• Set up a collection definition
o The collection definition instructs Pivot to grab your report (specified in the Report Template field), run it with a specific list of parameters (Report Parameter Query), and associate a certain set of metadata (Dataset Query) for each instance of the report.
 Report Template: The URL for the Report Template should specify an image format, and include a placeholder for the parameter value. Use the following format:
• http://[servername]/reportserver?http://[servername]/[folder]/[reportname].rdl&rs:Command=Render&rs:Format=image&RC:outputformat=PNG&[ParameterName]={0}
o Remember that the parameter name must be the INTERNAL name, not the friendly one displayed to users
 Dataset Query needs to return data in a particular order.
• The very first field MUST be a unique ID; in general, this will be the same value used in the Report Parameter Query.
• The second field is the name; this is the value that will appear on the top-left of the pivot viewer when an individual card is selected. Consider using the field users are most likely to do a text search on, such as employee name.
• All subsequent records will be used as slicers on the left side of the pivot viewer.
 To change the way data is displayed in slicers, you must change the Dataset Query. To get a slider bar that will let you select a range of numeric values, include a ‘dimension properties’ clause in the MDX of your Dataset Query, and pull the .key for that dimension. The crawler will automatically format the results so the slider can be created.
 Report Parameter Query is a short query that will return the list of parameter values used to run each copy the report. It can include a WHERE clause to restrict the dataset.
• Create a SharePoint page to host the BI Report Crawlero The heart of this extension is a tool that will take snapshots of your SSRS report. It cycles through each parameter from the query you specified in the collection definition, runs the SSRS report for that parameter value, and stores the output of the report.
o The tool is packaged as a SharePoint web part, which means it needs a page to live on before it can be run. Create a page wherever you’d like on your sub-site, and drop the BI Report Crawler on the page. You will likely want to expand the height to view all the information returned by the tool.
• Run the BI Report Crawler for your collection definitiono Once the web part is all loaded up on the page, select the name of your collection definition from the Inventory drop-down, then click the ‘Crawl’ button on the right hand side.
o The bottom of the web part will now begin recording the results of each execution of the report.
• Create a SharePoint page to host the PivotViewer, insert the PivotViewer web part and point it at your collection
o The PivotViewer takes the form of a SharePoint web part, so we will again need some kind of page to host it on. Create a page in a suitable location, and add the PivotViewer BI DataBrowser (under the “Custom” folder). Edit the web part, and in the configuration section, enter the name of your collection definition under “Inventory”. Save the page and check in if needed.


Tips and tricks
o The dimensions of the SSRS report directly impact the shape and whitespace seen on the Pivot card. Whatever appears on page 1 of the report as it is printed will determine the Pivot card’s appearance. Use print preview to see where your report is at!
o I found very little in the way of troubleshooting help out there, so it was a struggle to figure out why my collection wasn’t rendering on the page. I finally broke out Fiddler to see where things were breaking, and spotted an error highlighted in red. Visiting that URL (http://[Server]/_vti_bin/BICollection/PivotCollectionService.svc/[Collection Name].cxml?q=
), I discovered that the Pivot service was telling me that I’d typed my MDX statement wrong. When in doubt, check the service for error messages!
o Keys MUST be unique within the first 20-25 characters! I could not find any documentation to support this, but my collection crawl kept failing with a unique key violation. I triple-checked and confirmed that the keys WERE unique. However, the original keys (concatenated from a series of columns) weren’t always unique until as late as the 28th character. We switched to a new key that was unique within the first 6-8 characters, and that worked like a charm.
o If the crawler does error out, results are cleared from the screen and only the error message is displayed. It’s often best to keep a close watch on the crawl so you can note which record prompted the error.


Troubleshooting
• If the Report Crawler crawls but nothing displays on the page...
--Close the browser and open the page in a new window
--Ensure the SQL Server Analysis Services service is running on the host machine
• If there is any kind of error…
--Check the strings for both the report (including parameter name) and the datasource
--Check the service at
http://[Server]/_vti_bin/BICollection/PivotCollectionService.svc/[Collection Name].cxml?q=
--Check the definition query; are you sure results are being returned?
--Run Fiddler on the crawler page to check for any errors