Adding Filtered Project Dashboards to PDPs (Part 1)

I guess a combination of that last post on “fun with InfoPath” and my upcoming TechEd BI presentation prompted me to get in a bit of an InfoPath/BI rut this week….or maybe it’s just the fact that I can feel productive by blogging even when I am procrastinating my presentation prep….

Anyway, with today’s post, I wanted to discuss how we could leverage InfoPath to pull values out of the URL and then use them to filter PerformancePoint dashboards.   (See this post on filtering reports based on the active user.)


Why would we want to do this, you ask?  Great question.  Here’re a couple scenarios:

  1. Adding a filtered project dashboard on the Project Center PDPs.
  2. Adding a filtered project dashboard on the project workspace – without having to configure each workspace individually.  (Note that MVP Alex Burton released a customized solution that supports this for SSRS)

More specifically, let’s examine the challenge of adding a custom project dashboard to a PDP.  The main challenge here is that our primary key is the Project UID, which resides within the PDP URL, and can easily be consumed by the SharePoint Query Filter Webpart.  Unfortunately, as PerformancePoint reports do not have the Project UID as an available dimension, we can’t filter based on it.  Instead, we have to translate the Project UID from the URL into the official name of the project from the Project Server reporting database.

The workspace has a similar issue, as the workspace URL typically includes the name of the project when the project was created….which may not be the current name of the project if the scheduler or project manager has changed it.  Hence, in the workspace, we would need to map the URL to the official project name to get the parameters required to automatically filter a report.

The InfoPath solution I’ll show you today will do that.  Essentially, it runs a SQL query that takes parameters from the URL and translates them into a usable value from the SQL database.  It then passes those parameters into a PerformancePoint report.

Before we go much further, we probably also need to address potential alternatives to this approach:

  1. Custom Webpart development – as in Alex’s example.
  2. Add VBA to populate the Project UID as a custom enterprise field in Project Professional.  Add that field to the OLAP cubes as a selectable dimension.
  3. Add a custom workflow or event handler to populate the Project UID as a custom enterprise field in Project Server.  Add that field to the OLAP cubes as a selectable dimension.
  4. Use SSRS and configure it such that the Project UID is a reporting parameter.

So yes, this technique is probably a bit contrived….but it seems to work….and it involves no code, so therefore it seems worthy of discussion.

1) Create the InfoPath Infrastructure

Your first step is to create the required InfoPath libraries and to ensure the InfoPath service is configured properly in Central Administration.  I’ll go ahead and create a form and data connection library in the main PWA site – but set them so they don’t appear on the Quick Launch bar.

For more information on getting the infrastructure set up, I’ll refer you to this post.  In fact, I’d recommend that you read through that post prior to proceeding as we’ll be using many of the same techniques.

Below you’ll see the two libraries I created, creatively named “Connections” and “Forms.”


2) Create The Data Connection

Next, just like in that previous post, we create the data connection.  In this case, I’ll just point the connection at the EPM_ProjectUserView and grab all of the fields.  Since I documented the screenshots in that post, I skipped a number of steps to show you the screenshot at the end.


Convert the connection to a file and save it to the Connections library.


Make sure to approve the connection – as by default it’s loaded with a status of “Pending.”

3) Create a New InfoPath Form

Using the connection I just posted, I’ll go ahead and create a new InfoPath form using the Data Connection template.


Note that the available fields appear both as QueryFields and DataFields.  If you don’t see that, go ahead and click the Show Details option at the bottom.  If you still don’t see that, my guess is that you may have picked the wrong form template.

The way InfoPath works – or so has been explained to me – is that we populate the query fields, trigger the query, and then based on those values will return the results in the data fields.  In this case, that makes things complicated because we could, in theory, have multiple results.  Hence, the data fields typically appear in the repeating section of the form at the bottom.

Since we only ever expect a single result, we will need to implement a workaround to solve this issue.

First off, I’ll simplify the form.  Nobody will ever see it, so it doesn’t have to be pretty.


Next, I create two new fields by right clicking on the MyFields label and selecting the Add Option.  I’ll call them TempUID and TempProjName.  I drag the relevant fields onto the form to make the following…. (And actually, I could probably leave the form blank as we’ll never look at it, but this helps frame the discussion, I suppose.)


Now we need to create a set of three rules:

  1. Rule #1: Convert the value entered into the TempUID field into a format that can be used in the SQL query.
  2. Rule #2: Run the query.
  3. Rule #3: Take the resulting value in the ProjectName field, format appropriately, and copy into the TempProjName field.

Those rules will be triggered when the TempUID field is populated.  Hence, we can put all of those rules on the single field.

Rule #1: Manipulate the ProjUID field.

As I discussed in that other post, we need to change the value entered in the TempUID field to something more usable and copy it into the queryfield ProjectUID.


We’re going to use the following formula in InfoPath:

concat(“{“, translate(., “abcdefghijklmnopqrstuvwxyz”, “ABCDEFGHIJKLMNOPQRSTUVWXYZ”), “}”)

…where the “.” represents the TempUID field.  This formula does two things:

  • Adds round brackets around the TempUID entry, i.e. {abcdef}
  • Converts the lower case letters to upper case, i.e. {ABCDEF}

When completed, the calculation should look something like this (noting again that the field name appears as a “.”):


Rule #2: Run the Query

That’s pretty straightforward.  You should now have something that looks like this:


Rule #3: Convert the ProjectName Field to a PerformancePoint Parameter

In this case, we’re going to convert the value that shows up in the first repeating datafield within the InfoPath form.  We need to do this for two reasons: to allow PerformancePoint to consume the data and because Webpart connections cannot use fields in repeating sections.

Since PerformancePoint is looking for the project name in the format of “[Project List].[Project Name],” we also need to convert the result set into that format.

Our third rule then will run and populate the TempProjName field.


The formula will look like this.

concat(“[Project List].[“, @ProjectName, “]”)


You should now have these three rules in this order.


Publish the form to your Forms library.  When Publishing, make sure you promote the two custom fields so that they’re available for use in Webpart connections.


…and that’s the heavy lifting.  Next up….creating the PerformancePoint report and adding all of the pieces onto the PDP.

Adding Filtered Project Dashboards to PDPs (Part 1)