Integrating Power View with PerformancePoint
With the rising popularity of Power View, you may need to integrate it with PerformancePoint to jazz up your dashboards and make them more interactive. Take a look at the following, admittedly unpolished, dashboard which combines a PerformancePoint scorecard and Power View report.
What’s interesting is that the Power View report is synchronized with the dashboard filter. For example, when the user changes the filter to 2009, the Power View report filters on the same year. Starting with SQL Server 2012 SP1 CU4, Power View supports passing URL parameters using the syntax rf=[Table].[Field] eq Value. In this case I use the following Power View URL:
http://<site>/_layouts/15/ReportServer/AdHocReportDesigner.aspx?RelativeReportUrl=/PowerPivot Gallery/Adventure Works Dashboard.rdlx&ViewMode=Presentation&PreviewMode=True&PreviewBar=False&[Date].[Calendar Year] eq 2009
As you would quickly discover, the Power View customization is rather basic. The only operator supported for now is the equal operator (eq) and multi-valued parameters are not supported. The PerformancePoint integration is achieved by using the Web Page report type. Because of the mechanics of how PerformancePoint passes a parameter to a web page report (it always appends the Endpoint_Url query string containing the filter value), you’ll need a wrapper ASP.NET or HTML page that extracts this parameter and constructs the correct Power View URL. I opted for an ASP.NET page. Due to the SharePoint limitation that ASP.NET pages cannot have code-behind files, the PowerViewWrapper.aspx page, which I added to the Pages folder, includes the server side code:
<!DOCTYPE html PUBLIC “-//W3C//DTD XHTML 1.0 Strict//EN” “http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd”>
<script runat=”server”>
protected void Page_Load(object sender, EventArgs e) {
System.Collections.Specialized.NameValueCollection nvc = HttpUtility.ParseQueryString(Request.Url.ToString());
string filterValue = nvc[“Endpoint_URL”];
if (filterValue == null) return;
string pvUrl = String.Format(“http://elitevm1/bi/_layouts/15/ReportServer/AdHocReportDesigner.aspx?RelativeReportUrl=/bi/PowerPivot Gallery/Adventure Works Dashboard.rdlx&ViewMode=Presentation&PreviewMode=True&PreviewBar=False&rf=[Date].[Calendar Year] eq {0}”, filterValue.Remove(0, 3)); // remove “FY “.
Response.Redirect(pvUrl); }
</script>
…
Note: By default, SharePoint disallows custom code and the page will error out with “Code blocks are not allowed in this file”. To fix, this open C:\inetpub\wwwroot\wss\VirtualDirectories\80\web.config and change the PageParserPaths section as follows (allowing all pages with /* was the only way I was able to get the page to execute successfully:
<PageParserPaths>
<PageParserPath VirtualPath=“/*“ CompilationMode=“Always“ AllowServerSideScript=“true“ IncludeSubFolders=“true“/>
</PageParserPaths>
The last part is to hook the Web Page report to the dashboard (no surprises here):
- In the PerformancePoint Designer, add a Web Page report with an URL pointing to the wrapper page, such as http://<site>/bi/Pages/PowerViewWrapper.aspx?Dummy=1 (the Dummy parameter was added so ParseQueryString could parse the query parameter in the wrapper page).
- Add the report to the dashboard.
- Drag the Display Value property of the filter and drop it onto the Web Page report zone.
- In the Connection dialog box, click the Connection Formula button and configure it as follows:
The <<SourceValue>> placeholder returns the filter value, which PerformancePoint passes to the wrapper page, which in turn calls the Power View report.
