Bringing Workflow Tasks to the PDP

Well, it’s been quite a summer this year.  Had a great vacation, started an exciting project, and everything was going gangbusters until my left knee unilaterally declared a work stoppage and decided to go on strike until further notice.  (No worries, the doctor tells me it’s fixable).  Being on crutches and somewhat immobile has inspired me to get back into the blogging again.

Since it’s been a workflow kind of month, I figured I’d kick off this blogging with a nice easy post where I took the same old trick I’ve been using for the last year, and apply it to the Workflow Task List.


In this case, I realized that it was getting annoying to validate a solution in development when I would push a project into an approval stage, and then have to close the project, navigate to the workflow list, approve the item, and then navigate back to the project via the Project Center to resume where I was.

The other option, of course, was to have two browser tabs open, one to the Workflow Task List and one to the project in question – but I found that kind of annoying to keep having to do that.

So, on the theory that what is annoying to me is probably annoying to the end user, I decided to see if I could just extend the Workflow Task List to a PDP, meaning I wouldn’t have to leave the project record itself to get to the list of relevant tasks.

Turns out, it was quite easy.  Here’s the run down:

1) Take a look at the Project Workflow Tasks List.  You’ll see that each task has the Project UID embedded in it.


2) Knowing that, all we have to do is add this list to a PDP and filter on the project, and we have a customized list of workflow tasks for the specific project.  Create the PDP and add both the Project Server Workflow Tasks list and the Query String filter.  Configure the Query String filter to filter on the Project UID and pass the value to the Workflow Task List.


3) The next trick would be to identify which view you want the PDP to display.  My preference is just to show all tasks for the project, as this may be relevant to anyone looking for information about the project.


4) Add the PDP to your project and you’re good to go…


You no longer have to navigate out of the project context to review or approve workflow tasks.

Bringing Workflow Tasks to the PDP

Creating a Project Resource List in a PDP

This question comes up fairly frequently….how do I create a list of project team members on a Project Server PDP?  Well, the answer is, you can’t – well sort of.  You see much of that data is in the ProjectResource table – which is only available in the Publish database.  Since we all know that querying the Publish database is forbidden, we can’t really access that table.

The Reporting database on the other hand contains the assignment data.  Hence, if your resource is actually assigned a task, they will appear there.  If your resource is not assigned a task, they will not appear in the Reporting database.

Got it?

So with that background, assuming that you decide where to query from, here’re a couple of SQL queries to help you get started.

From Published – which picks up all resources on the project team.  Just providing this for reference.  You shouldn’t be using this….

FROM         ProjectResource INNER JOIN 
                      MSP_PROJECTS ON ProjectResource.PROJ_UID = MSP_PROJECTS.PROJ_UID

And from Reporting – which only picks up resources with assignments.  This is the approved approach.

SELECT     CAST(MSP_EpmAssignment_UserView.ProjectUID AS VARCHAR(36)) AS UID, MSP_EpmProject_UserView.ProjectName, MSP_EpmResource_UserView.ResourceName, 
FROM         MSP_EpmAssignment_UserView LEFT OUTER JOIN 
                      MSP_EpmProject_UserView ON MSP_EpmAssignment_UserView.ProjectUID = MSP_EpmProject_UserView.ProjectUID LEFT OUTER JOIN 
                      MSP_EpmResource_UserView ON MSP_EpmAssignment_UserView.ResourceUID = MSP_EpmResource_UserView.ResourceUID

Note that I modified the ProjectUID field in both instances so it will appear in Excel properly.

From there, it’s just a matter of following the instructions in this post to lay out a filtered Excel or SSRS report that displays the data.

Here’s the report…  Note that I am using the UID as the filter.


I promote it into SharePoint, create a PerformancePoint report, validate the filter parameter….and from there, it’s just a matter of inserting it into the PDP and linking it to the Query String Filter.  I didn’t have to resort to the InfoPath query hack as I used the UID in the report itself.


Creating a Project Resource List in a PDP

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)

Adding Text Search to the Project Center Interface

Update 6/15/2012: See the comment at the bottom.

…well not really, but essentially I’ll be discussing how to create the functionality that does that.  My self-assigned goal in this post is to enable SharePoint search to perform a text search of Project Center content – and – based on that search allow the user to navigate directly to the appropriate project record or PDP within Project Server.

I will be showing you how to do this using External Content Types (ECT) and then modifying the results with InfoPath to render a dynamic URL from the text based results of the ECT.

This is a request that I’ve run into a couple of times both on client sites and on the online forums.  Generally, it’s driven by the fact that we have too many projects and it’s hard for someone to zero in on specifically the right project.  Hence, the user would like to simply type in the name or a relevant keyword into SharePoint search and go directly to the record.

As alternatives, note that Project Center does offer a custom filter option, so I could go create a onetime filter that filters on specific text in the project name.  In my experience, this is a bit slow and also the filter has to be removed and reset for the next project.  The other solution would be to create custom views that slice and dice the portfolio and make it easier to navigate (see this post on creating a My Projects view).  The functionality I’ll show you how to build today would augment that.

1) Reviewing the Requirements

Let’s start off by looking at how we will navigate to the PDPs when we finish this effort.  If you click on a project in Project Center, you should see a PDP such as the Project Information page.  Note that I am using the default Contoso demo image so my defualt page is actually the Proposal Workflow Status page.

Here’s Project Center – just to get us all on the same page….


…and the PDP….showing the current workflow status.


Take a look at the URL for the PDP….

In that URL, we can see the URL of Project Server:….

….the actual PDP: proposalstagestatus.aspx…

….and the unique ID for the project: projuid=012441e0-573e-48f7-a518-1169c28fcde8

What we’ll do then is create an ECT and manipulate the results within InfoPath to regenerate that target URL.

2) Create an External Content Type

I won’t spend a whole lot of time talking about creating ECTs, but basically they’re a method of bringing external data (in this case Project Server data stored in SQL) into the SharePoint fold.

To support this example, I will create an ECT with four fields: Project Name, UID, Project Description and Project Keywords.  I could, in theory, pull any field I wanted, which should then be subject to the SharePoint search indexing.

For more information on creating an ECT, I refer you to this post from last year.  Note that we will not need a filter as part of the ReadList operation.

3) Create an External List

I now create a site to throw the list on.  Nobody should actually have to navigate to the site.  We simply want it to be appear within the search results.  For this example, I am going to create a site called External under the BI Center.  As this site will need to be modified with SharePoint Designer, we may need to move it off of the main PWA site – which has been set to preclude any SharePoint Designer edits.

On this site, I create an external list and point it at the ECT we just created.


So far, so good.  Note that you can click on the project to show the default display form.  Note that we could also add metrics to this form, i.e. other values that typically appear within Project Center.


Next, we’ll modify the display form so that it will display the URL of the project record within Project Center.

4) Modify the Display Form

Modifying the default display form for external lists is a relatively simple exercise – provided you know one workaround.  I was able to find this in a blog post yesterday, although unfortunately, I didn’t make a note of which one.   Anyway, it’s usually quite easy to modify display forms for SharePoint lists.  All one has to do is click on the option in the Ribbon.

Unfortunately, that option doesn’t work in external lists.  The trick here, is to click on the button in the ribbon to modify the list in SharePoint Designer.


Once the list is open in SharePoint Designer, select the option to modify the form in InfoPath.


This will launch InfoPath.


Now, let’s create a new field that will store the calculated URL.  To do this, right click on MyFields in the Fields dialog box on the right and create a new field called URL.  Set it as a hyperlink field type.


Drag the new field over to the form.  I also tidied up the form a bit by removing the UniqueID and cleaning up some of the label names.  I also converted the Description text box to a multiline display.


Now right click on the newly created field.  We’re going to modify the default value to display the appropriate URL.


Select the Properties option, then the button to edit the default calculation.


Once there, I’ll add the following custom formula:


Hit Ok, and publish the form back up into the InfoPath list.  You may have to save the template somewhere prior to publishing.  It doesn’t matter where you save it.


5) Reviewing the Results

Let’s take a look at the results.  To do that, navigate back to your external list.  Refresh it for good measure.  Click on one of the projects to review the results:


Click on the URL to confirm it navigates back to the appropriate place in Project Center.


You now have an extended version of the Project Center navigation structure accessible via enterprise search that allows users to easily find projects.  You may want to investigate reconfiguring your search and perhaps adding a custom scope to hit the external list, but that’s a topic for another blog.


Adding Text Search to the Project Center Interface

Prepopulating EPT Custom Fields in Project Server 2010

I figured that I’ve been focusing on issues of process and strategy for a while, and it was probably time to get back into the mundane world of technical delivery for a post or two.  Variety, after all, is the spice of life (which leads me to suspect that this idiom probably doesn’t translate well into some languages and cultures that don’t really respect spice or culinary diversity, but that’s a digression for another topic.)

Back to the point of this posting though, this is a Project Server 2010 question that I’ve seen come up quite frequently both in client discussions and on the newsgroups: “How do I prepopulate specific fields within an Enterprise Project Type?”

The answer is that it’s actually quite simple to do so.  Essentially, all we have to do is to populate the fields in a Microsoft Project Professional template and save the template to Project Server.  Associate the template to your EPT, and voila, when you create the project, the custom fields are already populated…..sort of.

Kicking it off….step 1: open a MPP file and populate the fields.  Even if you’re not ready to include tasks in a standardized template, that doesn’t matter.  You can still create a template with no tasks.


Save the project file as a template.


Now, go into PWA and associate the template with an Enterprise Project Type.


….and that’s pretty much it…with one relatively minor caveat:  the fields will appear blank within the PDP until the project record is saved for the first time.

For this reason, I’d recommend keeping only the basic minimum required fields on the first PDP created with the project, and then add any prepopulated custom fields to a subsequent PDP.  Honestly, that’s probably a best practice anyway.  When you save the project after completing the first PDP, the fields will be populated by the time you navigate to the second PDP.

If you add the prepopulated custom fields to the first PDP, they will appear blank until the project is saved – which needless to say is confusing to the end user and would probably result in them just filling out the field anyway, thereby defeating the purpose of the exercise.

Prepopulating EPT Custom Fields in Project Server 2010

Applying Security to a PDP Redux

A couple of weeks ago, I thought I’d figured out how to apply security to a PDP page and documented it in a post here.  Well, it turns out I was wrong.  A reader pointed out that she’d tried the same thing – which appeared to work fine when logged in as an Admin, but not as a PM.

That solution may still work in some limited scenarios – specifically where the role with the diminished permissions has the ability to review a project, but not edit or save it.  If the role with the diminished permissions needs to edit the project, then that solution will throw a “Value does not fall within the expected range” error on check-in.

Melodie also pointed out a better method to accomplish the same thing.  This blog post is intended to document that solution.  Thank you very much to Melodie for contributing to the community.

Essentially, we’re going to leverage the SharePoint personalization functionality.  This feature is turned on by default and allows each user to create their own personal view of a page.  In this case, we will need to log in as the user and create personalized views of the required PDPs.

Follow these steps to personalize the page:

1) Log in as the user.  You’ll probably need them to log you in and sit next to you while you do this.

2) Select the option to Personalize this Page from the menu in the top right.


3) Add Webparts to the page and configure appropriately.  Click on Stop Editing to, well, stop editing.


And now this is what the page looks like for the administrator.


When logging in as someone else, this is what I see.


Still a bit hard to administer but not bad for specific scenarios.

Applying Security to a PDP Redux