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.

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. 

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.

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.

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…