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.)

image

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.”

image

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.

image

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

image

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.

image

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.

image

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.)

image

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.

image

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 “.”):

image

Rule #2: Run the Query

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

image

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.

image

The formula will look like this.

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

image

You should now have these three rules in this order.

image

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.

image

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

Advertisements
Adding Filtered Project Dashboards to PDPs (Part 1)

9 thoughts on “Adding Filtered Project Dashboards to PDPs (Part 1)

    1. Not sure I follow the question entirely, but essentially I create a new field for the Project Name and set the rule to copy from the repeating section down to that field. It seems to only copy the first value returned (there should be only one anyway), and then I use the new field to drive my WebPart connection.

  1. Deepa Chandramouli says:

    A very useful blog. I am facing teh exact same issue and this was very helpful. But we have restriction on using a PerformancePoint Dashboard. We have to publish the data from Excel Worksheets. Can we directly connect the InfoPath form fields to the Excel Worksheets through Excel Web Access? Is there a workaround for this?

    Thanks,
    Deepa

      1. Deepa Chandramouli says:

        I got it resolved. Thanks a lot for your guidance. Actually I am new to Project Server and have lot of limitations in accessing the client environment and need to incorporate lot of worarounds to compensate for the limitations. But I learnt a lot using your blog. Now I am able to connect a Query String Filter web part to Excel Web access so I can filter the Excel Reports based on the Project UID.
        But the reports fail to refresh when I update a custom field. These are simple reports queried from the Reporting Database. I have the same issue when I try to display Risks or Issues through Excel Services. First I use the odc and query the Risks for all the projects and store it in a worksheet. Then I am able to display the risks filtered based on the Project UID on a new worksheet but when I add a new issue in sharepoint and come back to the Excel Report, it doesn’t refresh to show the newly added issue. Any help/insight is greatly appreciated.

        Thanks,
        Deepa.

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