Adding Filtered Project Dashboards to PDPs (Part 2)

In this post, we’ll take that InfoPath form we created in the last post, add it to a PDP, and link it to a PerformancePoint report to create an automatically filtered project dashboard. 

For what it’s worth, I know it kind of sounds complicated, but after you’ve done it a couple of times, it really only takes a couple of minutes to create and deploy this solution.

1) Create the PerformancePoint Report

I discussed this topic in this post.  Basically, you want to create a report, using the Project List as a background element.  Here’s a simple report that I created.

image

Make sure to right click on the graph and set it to filter the empty series.  That will force the graph to adjust the bar width for the project specifically.

2) Create the PDP

For this, I’ll create a PDP called “Project Dashboard.”  On this PDP, I’ll add three Webparts:

  1. Query String Filter – to pull the ProjUID value from the URL and pass to the InfoPath form.
  2. InfoPath Form – to pass the ProjUID value into SQL and return the Project Name – which is then passed into PerformancePoint.
  3. PerformancePoint – to display the data.

Query String Filter

I’ll add that to the page and configure it to use the ProjUID value.

image

The InfoPath Form

Next, add the InfoPath Form Webpart.  Set the Chrome to None.  Minimize the Webpart.

image

The PerformancePoint Report

Now add the PerformancePoint Report Webpart to the page.

image

Webpart Connections

Now add the appropriate connections:

  1. The Query String Filter will pass the ProjUID to the TempUID field in the InfoPath form.
  2. The Infopath form will pass the TemProjName field to the PerformancePoint Report.

Exit the edit page mode to confirm that it all works:

image

…and there you are, an automatically filtered PerformancePoint report embedded in a Project Server PDP.

Adding Filtered Project Dashboards to PDPs (Part 2)

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.

Adding Filtered Project Dashboards to PDPs (Part 1)

Customizing SQL Views to Provide Useful Datasets

In my last post, I decided to take a look at how to use External Content Types to surface Project Server Reporting database data on Risks and Issues.  In this post, I plan to talk about some quick and easy steps that are required to provide a useful dataset.

Now, before we get too far into this post, I should probably mention that working directly in the database is always a sensitive issue, but as far as I can tell, adding a custom view to the Reporting database will not cause supportability issues going forward.  Just make sure that everything is documented and backed up.  Now back to the narrative…

The challenge that I found when I first started playing with the risk tables in the Reporting database was that none of the risk tables seemed to include the Project name.  A couple include the Project ID field, but that won’t help when it’s surfaced in an External List.

image

…so I decided to create a custom SQL view.  To do that, I fired up SQL Management Studio and navigated to the Reporting database.  Once I found that, I right clicked on the Views option to create a new View.

image

From there, it’s a relatively simple matter to select the WSSRisk_Olapview and EPMProject_Userview tables to be included in my view.  I check the fields to be included, and then add a join at the ProjectUID field.  (If you’re not familiar with this interface, it works much like Access, you just grab the ProjectUID field from the table on the left and drag it to the one on the right to generate the join.)

image

Save the Custom View, and you can now use it for reporting purposes, whether that report is based on an External Content Type, an Office Data Connection file, or a direct connection back into SQL.

image

Customizing SQL Views to Provide Useful Datasets