Add a Power View Web Report to Your PPS Dashboards

RSS

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:

pv001pv002

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:

  1. <%@ Page Language="C#"%>
  2. <%
  3.         string sname = Request.Params[0].ToString();
  4.         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";
  5.         Response.Redirect(rsite);
  6. %>

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.

pps01

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.

pps02

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.

pps03

Discuss this Blog Entry 1

on Dec 31, 2013

Awesome things here …..really very happy to found a blog like this...hoping for more article from you ……you're cool!!!

Please or Register to post comments.

What's SQL Server BI Blog?

Derek Comingore’s, Mark Kromer's, and Jen Underwood's candid look at SQL Server’s Business Intelligence features.

Contributors

Mark Kromer

Mark Kromer has been a technical product manager & solution architect in business intelligence, data warheouse and Big Data world for over 20 years for Microsoft, Oracle and AT&T, currently...

Jen Underwood

  Jen Underwood, Microsoft Sr. Program Manager, has almost 20 years of experience in data warehousing, business intelligence, and predictive analytics. She was formerly a Microsoft Business...
Blog Archive

Sponsored Introduction Continue on to (or wait seconds) ×