Reporting on Workflow Approval Status with PowerPivot

Playing with workflow a bit this month, and trying to come up with an easy way to develop a report on workflow tasks.  Now, if you’ve used workflow in Project Server, you know that the tasks are stored in the Project Workflow Task List.

image

…which includes the Project UID, but may not include other custom fields such as Department or Project Manager.

So my goal for this post is to merge this table with the EPMProject_UserView table within the database and then turn it into some sort of reportable format.  And what tool should we turn to when we need to combine multiple tables from disparate data sources?   PowerPivot – which is a free download in Office 2010, and out of the box in Office 2013.

In this case, I open up PowerPivot and point it at the Project Server Workflow Task List.

image

Then I open another table pulled from the EPMProject table within the Reporting Database.

image

Then I add a join using the Project UID.

image

From there, it’s just a matter of adding a PivotTable into Excel…

image

..and after some manipulation in Excel, I end up with the following:

image

Not the fanciest report, but it will suffice for my purposes.

Advertisements
Reporting on Workflow Approval Status with PowerPivot

Task Paths in Project 2013

Here’s probably one of the most interesting new features (well, other than reporting) added to the new Microsoft Project desktop client – the Task Path feature.  This feature allows you to easily review a schedule to assess the impact of a single task.

image

First off, I point out that fellow MVP Nenad Trajkovski has already posted on this topic.  I wanted to add to his post, and perhaps provide some additional information on how to consume this new feature in the updated Project VBA object model.

In the screenshot above, you see that I have selected a single task.  Each of the driving predecessors of the task are highlighted in orange.  In turn, the driven successors of the task are highlighted in purple.

This is all controlled through the Task Path interface, now found on the Gantt Chart Format tab.

image

Opening that dropdown, you see the options for the task path display.  Note that these options are not mutually exclusive, i.e. I can actually select all of the options to add informational depth to my Gantt Chart display.

image

As always, just mouse over the bar in the Gantt Chart to see what the color represents – or doubleclick on the Gantt Chart to see the legend.

image

So that’s great, but what about if I want to filter on only those tasks that are predecessors and successors?  There’s no field affiliated with that setting, so how can I determine which tasks are relevant?

Enter VBA.  The new setting may be consumed via the VBA object model using:

  • Task.PathDrivenSuccessor
  • Task.PathDrivingPredecessor
  • Task.PathPredecessor
  • Task.PathSuccessor

For example, the following VBA code will label each task within the schedule as a Predecessor or Successor in the Text1 field, and then filter the view on only those tasks which have been flagged.

Sub DefinePath()

    Dim T As Task
    Dim I As Integer

    'Clear the existing filter
    Application.FilterClear

    'Capture the selected task
    I = Application.ActiveCell.Task.ID

    'Flag the Predecessors and Successors
    For Each T In ActiveProject.Tasks
        T.Text1 = ""
        If T.ID = I Then
            T.Text1 = "Selected Task"
        End If
        If T.PathPredecessor = True Then
            T.Text1 = "Predecessor"
        End If
        If T.PathSuccessor = True Then
            T.Text1 = "Successor"
        End If
    Next T

    'Apply a filter on the flagged tasks
    SetAutoFilter FieldName:="Text1", FilterType:=pjAutoFilterIn, _
    Criteria1:="Predecessor" & Chr$(9) & "Successor" & Chr$(9) & "Selected Task"

End Sub

Here’s what the results look like…

image

Task Paths in Project 2013

UMT is Heading to Vegas

Looks like Las Vegas will be a PPM hotbed on 11/11-16.  Not only are the Project folks descending on the town for the big SharePoint blowout, but the PMO folks will be hanging out next door for their own annual PMO Symposium.

Between the two events and those Vegas-style buffets, there’s probably far more content than can be safely consumed in a couple of days.

Microsoft’s Mike McLean and I will be teaming up again to offer the next iteration of our crowd pleasing BI song and dance from the Project Conference – this time in 2013-icolor.

We’ll be presenting an Overview of Business Intelligence and Reporting using Project Online & Project Server 2013.

Hope to see y’all there!

UMT is Heading to Vegas

A Simple Archive Workflow Example

It’s a workflow kind of month, and I figured I’d post this quick article on how to leverage UMT’s Project Essentials 2012 with Project Server 2010 to generate a no-code archive process.

In this case, the goal is to close the project out, and then create a report that will notify the administrator to archive the project at a later date.  Now, exactly what that archive process looks like is kind of up to you, as I find many organizations vary widely on definitions of the archive process and when they decide to trigger it.

First, I see up a couple of stages to move the project between.

image

Then I create a security group containing the folks who are required to archive a project.

image

Now we go into Project Essentials and generate a simple workflow.  I won’t walk you through step by step, but it’s pretty self explanatory once you open up the wizard.

image

The only real nuance is that I am adding a manual submittal requirement and an approval step for the Closing step.  This will require the PM to submit the project to be archived, and will send a notification to the members of the Custom Archive Approvers group to do so.

image

I link the workflow back to a project type, and run a project through the process.

image

When I get to Closing, I require the PM to manually approve it – or perhaps require the PMO to submit it.  That kicks off a workflow request that gets logged in the Workflow Task List.

image

One more step and we’re there.  Let’s create a new view of the Workflow Task List and modify it to filter only on those tasks exiting Closing and entering Archived.

image

I now have a screen which time stamps the date the project was closed, and gives the administrator an overview of which projects need to be archived.  As a project typically hangs around for 6-12 months before being archived, the administrator can review this list on a quarterly basis to identify candidates for the archive process.

image

Even better, add a simple workflow to the Workflow Task List to send an e-mail to the administrator 6 months after the workflow task has been created.

A Simple Archive Workflow Example

Project 2013 On-Premises: The Missing Settings

For those of you just dipping your toes into the new world of Project Server, you might have noticed some key settings that are now missing from the main Server Settings screen.

The first thing you may notice are all of the missing security settings.  Here’s the default.

image

And here’s the same screen with all of the settings displayed.

image

The basic issue here is that Project Server now operates in two security modes: SharePoint and Project Server.  The default is SharePoint mode which leverages the SharePoint security model.  With some Powershell commands, we can enable the Project Server (or “Classic”) mode.

Here’s the technical reference: http://technet.microsoft.com/en-us/library/jj219486(v=office.15).aspx

Aside from that, you’ll also note that some of the settings have been moved to Central Admin – which makes them inaccessible to Project Online users.  For on-premises deployments, you’ll want to make sure that you include these on your configuration checklist.

Click on the PWA Service Application, highlight the PWA site, and select Manage.

image

From there you’ll see the available settings.

image

And just so the search engines will pick it up:

  • Queue and Database Administration
  • Manage Queue Jobs
  • Daily Schedule Backup
  • Administrative Backup
  • Administrative Restore
  • OLAP Database Management
  • Operational Policies
  • Alerts and Reminders
  • Additional Server Settings
  • Server Side Event Handlers
  • Project Site Provisioning Settings
  • Bulk Update Connected SharePoint Sites
  • Workflow and Project Detail Pages
  • Project Workflow Settings
Project 2013 On-Premises: The Missing Settings

The Evolution of Organizational PM Training

A couple of weeks ago, I had the pleasure of chatting with a PMO director who had just taken over a secondary role of training manager for her company.  I must admit, at first, I was bit surprised at this juxtaposition, but on further reflection it actually made a lot of sense.

First off, let’s look at the role that projects play in many companies.  At a high level, projects are what bring in new technology and processes.  In fact, when you look at some of the key drivers for developing new training: supporting new technology is probably right up there at the top – perhaps along with government regulations, and your safety incident management system.

Secondly, rolling out a new PMO is very much an exercise in training.  Typically, training plays a major role in rolling out a PMO in general, and an EPM tool to support the PMO in specific – although I often consider the two experiences to be pretty much one and the same thing, i.e. the tool implementation often provides the catalyst to drive PMO process maturity and the required training.

Hence, whether the training component of the tool deployment is limited only to scheduling best practices, or expands further to include process maturity and the development of an internal certification process, training is an integral part of what a PMO does.

Competency Models

If you’ve caught any of my presentations over the past couple years, you’ll know that I consider PMO development to be primarily an epistemological journey.  Likewise, the training required should map to where we are on that journey:

The first step is to identify what we don’t know, and only then can we focus on what we need to know.  How is that supported through training?  Often the first step in PM maturity is to go out and see what the industry best practices are – usually in the form of some generic PMP or PRINCE2 classes.

That gives a core group of individuals the ability to see a broad scope of what could be done, and then adapt that to the specific needs and maturity of the organization.  Hence, most PMOs typically start with sending a core group of individuals to PMP training.  As the PMO expands, this may or may not be a desirable goal – as PMP-certified project managers are often more mobile, and also end up spending time learning about things that may not be relevant to the specific organization.

At this point, the organization typically begins to resort to more targeted training specifically designed to support internal processes.  The training needs begin to merge with the project manager’s career development path.  It’s at this point that we begin to run directly into the organizational purview of the training management organization.

Considerations

Enter the corporate training office.  How can they be successfully integrated into the project management training delivery?  Or, how can project management training delivery be successfully integrated into what the training office is doing?

Here’re a couple of questions to ask early in the PMO development cycle:

  1. What training assets do we currently leverage as an organization?
  2. What approach to defining training needs to we currently leverage as an organization?
  3. Have we engaged both our training assets and our need definition methodology in developing our PMO roadmap?  If not, why not?
  4. When do we plan on engaging our training department to assist in supporting our PMO process deployment?
  5. How will this training program integrate with your employee incentives and internal PM career development plans?

As your training folks will probably agree, it’s never too early to engage them in a process.  Even if you’re just rolling out a pilot training program, at some point, that program will need to be merged into the overall corporate training system.  Any tool use that has been configured to support specific business processes will likewise require integration with the ongoing training system.

For more on items to think about when deploying a PPM training program, take a look at this post.

…and please feel free to contact the UMT Institute to discuss any of your project portfolio management training needs.

The Evolution of Organizational PM Training