Automatically Filtering Excel Reports in PerformancePoint Dashboards

This post is essentially a follow up to a series of three posts I wrote last week on how to create a Webpart in InfoPath that pulls URL parameters, queries SQL and then filters PerformancePoint reports with the returned value.  For more information, please check these links:

  1. Adding a Filtered Dashboard to a PDP (Part 1)
  2. Adding a Filtered Dashboard to a PDP (Part 2)
  3. Adding a Filtered Dashboard to a Project Workspace

I realized that this technique worked with a basic PerformancePoint report, but still hadn’t been validated against an Excel report.  In this post, I’ll talk about how to modify the approach slightly to filter Excel reports within the PDP.

Before we go too much further, we probably should take a step back and review when I might use Excel (or SSRS) vs. native PerformancePoint reports.  As far as I can tell, PerformancePoint reports are limited to use with OLAP cubes as a data source.  Hence, if you need to generate a report that uses more tabular data – such as milestone reporting, you would need to use Excel or SSRS to generate the report.  Put simply, for cost and effort reporting, the OLAP cubes are probably the best source.  For schedule based reporting, you’ll need to tap into the traditional reporting database.

Preparing the Excel Report

I won’t go into too much detail, as this is well documented online – in the context of linking Excel reports to the PerformancePoint dashboard.  Boiled down, make sure that when you publish the Excel worksheet to SharePoint, you select the option to publish a parameter.

Here’s more information:

http://blogs.msdn.com/b/performancepoint/archive/2007/12/17/connecting-dashboard-filters-to-excel-services-pivot-table-report-filters.aspx

Here’s what it looks like when I publish my cumulative milestone report.

image

Create a new Excel report in PerformancePoint with the Dashboard Designer.  (Really, it’s just creating a record and pointing it at the Excel chart you just uploaded.)  To confirm that everything is working, you should see the parameter appearing in the report screen. 

image

If you don’t see that, revisit the previous steps to configure your Excel worksheet properly.

Preparing the InfoPath Form

If you read those previous couple posts on using InfoPath to filter reports, this shouldn’t be too difficult.  To support this model, I created a new field called “TempProject_Excel.”  I set that field to populate with the value in the Project Name data field.

image

You’ll have to modify the field to work with your reports.  In this example, I just need the project name with nothing fancy, so “Acquisition Target Analysis” without brackets or modifications will work just fine.

Publish the InfoPath form and make sure to promote the new field so it’s available for use in Webpart connections.

image

Configuring the PDP

This is where I had a little difficulty at first.  For a while, I couldn’t get my report to filter – then magically it started filtering after I changed something.  I am not sure exactly what I changed, but I suspect it’s related maybe to the order in which the Webparts are added to the page – or the order they’re loaded into the Webpart zones.  I am not entirely sure, but if you find it’s not working for you, start playing with those parameters to see if you can identify what’s causing it.

Needless to say, when I tried to recreate the issue on my environment, I couldn’t any more and it all works just fine.

From there, it’s a straightforward exercise in adding our Webparts:

  1. Query String Webpart set to filter on ProjUID
  2. The InfoPath form
  3. The PerformancePoint Report – configured to hit our Excel report

Add the appropriate connections:

  1. Query String WebPart > InfoPath
  2. InfoPath > PerformancePoint

Clean the page up a bit, and you’re left with something like this…

image

Advertisements
Automatically Filtering Excel Reports in PerformancePoint Dashboards

5 thoughts on “Automatically Filtering Excel Reports in PerformancePoint Dashboards

  1. Roland says:

    Thank’s for this post.
    I have some trouble with the refresh. I used the SSS and I received a error for the refresh all connections:”An error occurred while accessing application id ExcelServices from Secure Store Service. The following connections failed to refresh:…”
    I search on the web but no answer resolved this error.

  2. […] Following Mike McLean and my triumphant debut as Project Server 2013 BI masters at the recent SharePoint conference, I wanted to document one of the demos – specifically the one where we filtered Excel reports automatically based on external parameters.  I’ve more or less covered this before in Project Server 2010 and PerformancePoint, so if you’re looking for more information, please feel free to check out this link. […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s