One of the most exciting and talked-about updates to SQL Server 2012 is SSRS 2012 and the new Power View reporting tool. If you’ve built existing BI solutions on the current or previous Microsoft BI stack, which would include SSRS 2008, SharePoint 2010, PowerPivot, etc., then you may have put those different reporting pieces together into a nice dashboard using the PerformancePoint Services capability in SharePoint.
I created a couple of different simple reports in Power View, shown below, that are different views into project data based on resource utilization and billable hours.
And then I wanted to add them to my existing dashboard in PPS (PerformancePoint Services). To do this, I used the Web Page report type in PPS and pointed it to my Power View report hosted in SharePoint. Power View reports are integrated into SharePoint. There is not “native” stand-alone option for Power View. But I didn’t want to display both reports because there are different user requirements for viewing each report separately from the same dashboard.
To accomplish this, I built a simple redirect ASP.NET page from a simple text editor and stored it from a separate Web site in IIS on my SharePoint server. The code for the page is below:
<%@ Page Language="C#"%>
<%
string sname = Request.Params[0].ToString();
string rsite = "http://intranet.contoso.com/_layouts/ReportServer/AdHocReportDesigner.aspx?RelativeReportUrl=/SelfService%20BI/"+sname+".rdlx&ViewMode=Presentation&Source=http%3A%2F%2Fintranet%2Econtoso%2Ecom%2FSelfService%2520BI%2FForms%2FAllItems%2Easpx&DefaultItemOpen=1&DefaultItemOpen=1";
Response.Redirect(rsite);
%>
The long, convoluted URL is the link to the Power View reports. The only variable in the URL string is the name of the report as part of the RDLX file name. So what I did was to capture the perspective that the user wants to see from a filter (see screenshot below) and capture that parameter from the filter selection. The ASP page just redirects to the report that the user selected in the filter by dynamically building-out the URL.
In the PPS Dashboard Designer above, you’ll see that the key to make the interaction work between the PPS filter and the Web Page URL is to use the connections property by editing the connections between the Web Page URL and the Filter. This will create the connection such that when a user selects the value from the filter drop-down, the Power View report will change dynamically to the current context.
The report itself to display the Power View report is just a Web Page report. Since it is using the dynamic redirect ASP page, it won’t parse properly in preview mode. Only once the user selects the drop-down choice from the filter in run-time will this work properly by redirecting to the proper Power View report.
Now when users hit your PPS dashboard, you can include your interactive Silverlight self-exploration Power View reports with context through a single related filter on the dashboard (see below). This adds a lot of extra glamour to your PPS dashboards using all in-the-box SQL Server 2012 and SharePoint 2010 functionality. Users can interact with the animations right in the dashboard from the Web Part in Power View directly. Making interactive dashboard with context filtering that gives the business user the ability to change context on the fly is very important to any successful BI project.
