New Oil and Gas Case Study Published

Working primarily in O&G myself these days, it’s always good to see another industry specific case study.  Here’s an example that was just published on about UMT’s collaboration with Gran Tierra.

New Oil and Gas Case Study Published

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:

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…


Automatically Filtering Excel Reports in PerformancePoint Dashboards

Returning Reporting Database Values in the Project Workspace

Just a quick add on to yesterday’s post on leveraging InfoPath to create automatically filtered PerformancePoint reports on PDPs.  I figured that I’d extend the same approach to the workspace.

For the workspace, we can simply pull the URL of the workspace, then map it to the ProjectWorkspaceInternalHref field within the Reporting database.  Once that’s done, we can return any value linked to that project record for all of your filtering or field population needs.

You’ll need to create a Form library on the workspace, but can share the same connection string we created in yesterday’s post.  I’m not sure if you can share a single form across workspaces – but I bet it’s possible, and if so, that would ease some of the administrative effort around this solution.

Essentially, it’s pretty much the same approach I took yesterday…except with the following:

I created a TempURL field.  Instead of relying on the SharePoint Query String Filter to populate it, I just set the value to default to the site URL.  (Which in theory, I probably could have done to pull the ProjUID from the PDP URL as well).


The problem there is that the site URL appears like this:

…but it’s stored in the database like this… Portal Database Migration

…so when we copy it from the TempURL to the ProjectWorkspaceInternalHref queryfield, we need to set the rule to truncate the trailing “/” and swap out the “%20” with actual spaces.  This can be done using the following formula:

translate(substring(TempURL, 1, string-length(TempURL) – 1), "%20", " ")


From there, it’s just a matter of taking the same approach as in yesterday’s post.  Run the query, copy the results into a custom field, and leverage that custom field for all new Webpart connections.

Returning Reporting Database Values in the Project Workspace

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.


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.


The InfoPath Form

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


The PerformancePoint Report

Now add the PerformancePoint Report Webpart to the page.


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:


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


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)

TechEd 2012 Project Server BI Follow Up

Thanks to everyone for coming out to the TechEd presentation on Project Server BI that wrapped up just now.

For blow by blow descriptions and screenshots on reproducing everything I just presented, I refer you to the following links:

  1. Project Server BI Resources (Part 1)
  2. Project Server BI Resources (Part 2)
  3. Project Server BI Resources (Part 3)

The TechEd recording should be available shortly for TechEd participants.  Note also that Mike McLean and I presented a similar topic at the Project Conference in March.  At that conference, we actually presented two 75 minute sessions with additional examples and discussion.

For those recordings, please feel free to check out the following links:

  1. Session #1
  2. Session #2

Note that Session #1 more or less maps to the TechEd presentation.  Session #2 contains additional demos and content.

It’s been a great week in Orlando.  Thanks to everyone for coming and to the organizers for, well, organizing.

TechEd 2012 Project Server BI Follow Up

Break Out the Ripe Tomatoes…It’s MS TechEd (North America)

If you’re in the Orlando area, this week’s your chance to get back at me for all of those blog posts that probably ended up breaking your production environment over the past couple of years….

Come on by and chat.  I’d love to hear from you.  I’ll be staffing the Microsoft Project Server booth in the Learning Center during the following times:

  • Monday – 1730-1930
  • Tuesday – 1500-1700 & Ask The Experts in the evening.
  • Wednesday – 1230-1530
  • Thursday – 1030 – 1230

…and if you actually want real answers to your questions, come by when I’m not staffing the booth and talk to one of the real experts. 

For a list of the relevant presentations….

(Note that Giles is actually presenting at TechEd Europe – not North America.) 

If you’re into statistics, you’ll note the semi-useless fact that 66% of the Project Server content will be presented by Houstonians.  Perhaps this points to a nascent hotbed of EPM talent in SE Texas – or maybe it’s just something they put in the brisket.

Break Out the Ripe Tomatoes…It’s MS TechEd (North America)