Adding Conditional Formatting to a Portfolio Milestone Report

After writing that last post, I was still vaguely dissatisfied with how the report turned out.  My goal in developing the report was to add conditional formatting to the PivotTable to increase the richness of the data available in the view.  I felt that I had failed to do that.

Now, my wife is fond of throwing Mongolian platitudes at me on occasion, and in this case, the one that kept coming to mind was “Davs heevol ustil; ajil heevol duustil,” which is one of those folksy sayings that translates roughly into “If you’re going to put salt in the water, make sure it dissolves – and if you’re going to do a job, make sure you complete it.” (And yes, it sounds much better in the original form.)

So, with thoughts of salty water swirling in my head, I went and played with the report for a couple more minutes, and was able to work out the conditional formatting mechanism in Excel 2010.  Note that this may/may not work in Excel 2007 or Excel Web Services, but I’d be curious for feedback if anyone tests this out.

image

…so when last we visited this topic, we had an Excel based report showing all of our portfolio milestones along with a couple of key icons based on the Finish Variance field.

image

Following the same instructions as in that article, I created a second PivotTable on a second worksheet.  This PivotTable includes TaskPercentComplete and a custom Finish Variance field (=TaskFinishVariance/8).

image

Don’t worry too much about formatting the table as nobody is going to see it.

Now we go back to our first PivotTable, click on a Finish Date and add Conditional Formatting.  Set the field to display green when the TaskPercentComplete cell equals 100%.

image

See how the Sheet2!B4 references the appropriate TaskPercentComplete cell for that Stage Gate?  Note that you’ll have to remove the “$” which are added by default to the equation to fix the reference, i.e. the default will be “=Sheet2!$B$4=100,” but you’ll change that to “=Sheet2!B4=100.”

Go ahead and add more rules.  Go into the Conditional Formatting dialog box to modify the order that the rules are applied in.

image

After working through all of that, in my example I have the following rules:

  1. All stage gates that have been completed appear as green.
  2. All stage gates with a finish variance > 10 are red.
  3. All stage gates with a finish variance between 0 and 10 are yellow.
  4. All stage gates with a finish variance < 0 are green.

Which appears as follows….

image

….much easier to read than what I had before, I think.

Adding Conditional Formatting to a Portfolio Milestone Report

Reporting on Portfolio Milestones with Excel

Well, I seem to be on a bit of a BI tear.  Not sure why, but based on past experience, once I start playing with reporting ideas, that generally leads to other ideas, and then I end up with a half dozen random reporting posts all in a row.  Oddly enough, that seems to happen annually right around the Discovery Channel Shark Week promotion.  Maybe there’s something about sharks that prompts me to ruminate on project data representation.

I haven’t yet done much with Excel based reporting on this blog, so I figured it was about time to set my sights on it and to see if I could generate a portfolio milestone report.  This report is a common request that I’ve seen from customers and on the InterWebs, i.e. how to create a simple milestone report for a project program or portfolio. 

The goal of the endeavor is to display the dates for predefined key milestones or stage gates, and then to provide some indication as to whether or not that stage gate is on schedule.  Essentially, the report will give a quick view at a glance of key milestones within a specific portfolio of projects.  Building off of this post I did a couple of weeks ago on developing a portfolio timeline, here’s a quick and easy report to display key milestones in Excel.

image

The assumption is that the organization has several main stage gates.  In this case, I am basing the report off of four stage gates, but it wouldn’t be difficult to accommodate more.

Enterprise Custom Fields

After playing with a number of different options, I realized that the easiest and most elegant solution simply requires a single enterprise custom field attached to a lookup table. 

First, I create the lookup table with the stage gate names.

image

Then I create a custom task level text field married to the lookup table.  Set the default value to blank.

image

That’s pretty much it for the Project Server side of things.  To create some sample data, I created a couple of projects and flagged the stage gates in each schedule.  The idea is that you would do this with your own stage gate milestones.

image

(And props to Victor for pointing out that my project illustration is all wrong and that Stage Gate 1 really should come before Phase 1, with Stage Gate 2 authorizing Phase 2 and so on and so forth.  I’m still too lazy to actually fix the illustration, but please note the procedural point.)

Generate the SQL Query

Rather than add a whole slew of custom fields to roll these values up to the Project Center, we can just use one of the Task tables in the Reporting database to pull all of the tasks identified as stage gates.

Thus, our next step is to generate the SQL query that we will use to pull data into Excel.  Since I am not much of a SQL hand, I create a new custom view in the SQL Management Studio to generate the query, which I then add to an ODC file.  Here’s a previous post on creating custom SQL views.  Note that you don’t have to save the view, but just use it to create the query.

In this case, I’ll be combining two tables: the EPMTask_UserView and the EPMProject_UserView.  I add a join on the ProjectUID field, and then make sure to include the new custom Stage Gate field as well as the TaskFinishVariance and the TaskFinishDate.  From the Project table, I include the ProjectName field.

image

At the bottom of the screen, you’ll see the SQL query generated, which in my example looks something like this…

SELECT     dbo.MSP_EpmProject_UserView.ProjectName, dbo.MSP_EpmTask_UserView.TaskName, dbo.MSP_EpmTask_UserView.TaskFinishDate,
                      dbo.MSP_EpmTask_UserView.TaskPercentCompleted, dbo.MSP_EpmTask_UserView.TaskFinishVariance, dbo.MSP_EpmTask_UserView.[Stage Gate]
FROM         dbo.MSP_EpmProject_UserView RIGHT OUTER JOIN
                      dbo.MSP_EpmTask_UserView ON dbo.MSP_EpmProject_UserView.ProjectUID = dbo.MSP_EpmTask_UserView.ProjectUID
WHERE     (dbo.MSP_EpmTask_UserView.[Stage Gate] <> N'””‘)

…don’t forget to filter on the Stage Gate field to exclude all tasks where Stage Gate is null or blank.

Create the ODC File

In order to get this data into Excel, we’ll need to generate an Office Data Connection file.  We could create one from scratch in Excel, but I kept running into error messages when doing so.  Rather than bothering to troubleshoot these issues, I just grabbed one from the BI Center and downloaded it to my desktop.

image

Double click on the ODC file to launch Excel.  From the Data tab, select the Connections option.  Then select the Properties button to edit the connection.

image

Give the connection a more descriptive name.  Then click on the Definition tab.

image

In the Definition tab, delete the Command Text, and swap it out with your SQL query.

image

Lastly, click the option to Export the Connection File at the bottom to export the new ODC to your desktop.  Either leave it on your local machine or upload it to the BI Center for use in future reports.

Create the Excel Report

Open a new Excel sheet.  Insert a PivotTable and choose your new ODC as the data source.

image

Configure the PivotTable as in the illustration below…

image

Now let’s add the Finish Date field to the mix.  Drag that field to the Values section.  You’ll see that it defaults to a count of the finish dates.  Click on the drop down next to the field and select the option to edit the Value Field Settings.

image

Change the field to display the maximum value and give it a more user friendly name.

image

Format the cells to display a date, and you should have something that looks like this…

image

Now let’s add the Finish Variance data to the mix.  The field is stored in SQL in terms of hours, so we’ll need to divide by 8 to get the appropriate number of days.  To do this, click on the PivotTable, and from the Options tab, select the option to create a new calculated field.

image

Configure the field as below and click on the Add button.

image

That results in a table that looks like this…

image

Format the Variance cells as integers.  (Tip: Hold down Ctrl and select all of the cells with your mouse).  Remove the Grand Total and Subtotals while you’re at it.

image

Highlight the Variance cells again and add Conditional Formatting.  In this case, I’ll choose an icon set.  Edit the rule so it looks as follows:

image

Go back to your Excel table and it should look like this:

image

…still not quite beautiful enough to put in front of an executive, but at this point, it’s just a matter of finagling the report in Excel to get it to look the way you would like it to.

Add a new project?  Simply right click on the PivotTable and select the Refresh option to update it with the latest and greatest information.

Reporting on Portfolio Milestones with Excel

Creating a Secure Enterprise Project Type

Here’s an interesting question that popped up recently…how does one secure a specific Enterprise Project Type within Project Server 2010 so that only specific people may be able to create it?

Process Overview

Let’s start off by reviewing the specific business scenario.  We have an organization that has small, medium, and large projects.  Each of those projects is mapped to various initiation processes and has been assigned a specific enterprise project type (EPT).

image

All large projects must go through an executive approval process, which for now is a manual application.  The application process happens through the use of a form, which then gets signed by the relevant executives.  Once the appropriate signatures have been received, the PMO administrative assistant creates the project record based on the form.

The other path is that users may create a Medium project, then apply to get that project promoted to a Large project.  The application process is the same as defined above, and once the approvals are in place, the PMO administrative assistant will promote the Medium project to a Large project using the Change Workflow button on the Project Center ribbon.

The challenge was that the users were getting confused and would go ahead and create a Large project – even when the approval hadn’t yet been completed.  We initially attempted to train this behavior away, but found a need for more effective enforcement mechanisms.

Honestly, my first thought was to implement some sort of workflow on the backend to ensure that only authorized users would create the restricted project type.  Of course, that would imply that I would have to actually get up and talk to a developer….something I try to avoid at all costs.  (Besides, that’s what the TFS Integration Pack is for, so I never again have to actually talk to a developer.)  Smile  Then, I realized there’s a pretty simple no code solution.

Modifying the Resource Department Field

Enter the workaround.  First off, we need to look at the Resource Department field.  By default, this is a single-select field, i.e. only one value may be entered.  If you’re not really using this field, great.  Leave it as it is and continue following the instructions below.

image

If you are using this field, then you may wish to consider changing it to a multivalue field.  Before you do that though, a couple caveats….

  1. Changing a field from single value to multivalue is irreversible.  You may not change it back.
  2. Changing a field to multivalue makes it much harder to bring into the Office Data Connection files you may be using to support your BI reporting.  Review the SDK for guidance on incorporating multivalue fields in the ODC queries.
  3. Changing a field to multivalue may potentially break your OLAP cubes.  See this post from Brian Smith for a nice synopsis.  That being said, in my preliminary testing, new cubes built after the field change seemed to work just fine.
  4. Even if you don’t break your cube, adding resources to multiple departments may cause odd numbers to appear in your enterprise OLAP cubes, i.e. a resource in “HR” will show in a different department than a resource flagged to “HR, Admin.”  Of course in this case, we’ll simply change the values for our administrative assistants or PMO members – who may not be under the same scrutiny for remaining capacity as a more technical resource.

That being said, if you have already changed the field to a multivalue, then ignore all of those caveats, and feel free to continue.

The next step is to add a new department to the Department lookup table referenced by the Resource Departments field.  I will call this new department “PMO Administration.”

image

Mapping the EPT and Resources

Map the Large project EPT to the PMO Administration department.

image

One more step remaining.  Pick the individuals who will be allowed to create the Large projects and add them to the PMO Administration department.  In this case, I’ll use Niraj Shah as an example.  I modify his record to include the right department.

image

Reviewing the Results

Now let’s test it out.  I navigate to the Project Center and select the option to create a new project.  I don’t see the option to create a Large project. (Good)

image

I try to change the project type by selecting a project, then clicking on the Change button on the ribbon.  Again, there is no option to promote a project to a Large project. (Good)

image

Now, I log in as Niraj’s delegate to see what his experience would be like.  In Project Center, I now have the option to create a new Large project. (Good)

image

…and in the Change Workflow dialog box, I have the option of promoting a Medium project to a Large project. (Good)

image

There are probably still ways to get around this obstacle, but overall, it’s an easy and elegant method to make it harder for users to accidentally stumble upon process exceptions.

Creating a Secure Enterprise Project Type

Selecting Time Tracking Options for TFS Integration

Well, it’s been an interesting couple of weeks professionally and on this blog.  During that time, I’ve set my sights on Microsoft Project Professional, SharePoint 2010, Visio Professional, and taken a brief excursion into the world of Ideation.  Spinning the Wheel of Required EPM Consultant Skills this week, I land on the popular TFS-Project Server integration package.

If you’re not familiar with the integration package, it’s an add in that comes as part of MSDN Ultimate that may be installed on your Project Server farm.  Once installed, the integration package essentially marries Project Server to your TFS 2010 SP1 installation – thus allowing bidirectional transfer of tasks and task updates from one to another.

I took a brief look look at the package here and here.  Since then however, I’ve had a number of discussions with clients both during demonstrations and as part of production installations.  This specific post is written to touch on one of those discussions that seems to invariably happen as part of the education process….a review the options available for time tracking.  To date, that discussion usually has revolved around whether the organization would like to track time within TFS or within the Project Server My Tasks/Timesheet interface.

Let’s take a look at the available options…after doing that, let’s take a look at the relevant decision factors.

Default Package Behavior

First off, let’s examine the default behavior of the package.  I am using the demo TFS image downloaded from Microsoft, but have created a new Team Collection and PWA instance to review what happens out of the box and to ensure that everything is set to the default options.

To start out, I create two new tasks, Task 1 and Task 2, then assign those task to the Contoso Administrator.  Each task is 5 days long, which translates into 40 hours (5d X 8h = 40h).

image

I navigate to the Resource Usage View within Microsoft Project Professional to see how those hours are distributed.  See how 8 hours are planned per day, starting on Thursday.  This is important to note because when the time is updated from TFS, the hours simply get booked from the first day on which Planned Work exceeds Actual Work, and then fill in the gaps until the task has been marked completed.  Here, those hours will begin being booked on Thursday and then proceed through the week.

image

We look at Visual Studio to confirm those tasks have successfully transferred from Project Server.

image

Clicking on Task 1, we navigate to the Project Server  tab to see the hours.  In this case, the hours have been pulled correctly from Microsoft Project and populated both in the Project Plan (Project Server) field and in the Work Item field (TFS).

image

I change the hours within TFS to indicate that I have worked 16 hours and have 24 remaining.  Note that I cannot edit the Project Server fields as they are greyed out.  These fields serve as a reference for what is actually in the project schedule.

image

Going back into Project Server, I look at the Approval Center page and the hours have been booked to the earliest dates available for the task.

image

After approving the changes, I look at the schedule in Microsoft Project Professional.  Everything looks good.  The task has been updated correctly.

image

Again, I review the task in the Resource Usage View to confirm that everything has updated correctly.  See how the hours are allocated against the first couple days of the task?

Picture the task as a series of glasses being filled with water, each glass representing one of the days of the task.  Once the first glass is filled, I move on to the second, then the third, and so on.

image

The moral of the story is that hours may be updated against the item in TFS, and those hours then get booked against the default hours for the task.  If the task was scheduled to start on Monday, then those hours are booked on Monday….and then any succeeding day on which the task is scheduled.

Note that this could create a potential issue where a task scheduled in the future is completed ahead of schedule.  If the data was updated within TFS, the task will be marked as complete in the future – generally a bad practice in the scheduling world.

Using Project Server My Tasks

So what happens if instead of simply booking time against a task, I desire to track when the work actually happened?  Instead of simply assuming that 8 hours were booked each day, I want the developer to book 4 hours on Monday, 2 hours on Tuesday, and 10 hours on Wednesday. 

Let’s look at the mechanics of this process first, then let’s back up and look at the implications.

Project Server allows the administrator to either set a standard project update mechanism or for each project manager to define the mechanism for their own projects.  In the following screenshot, I am setting the project to allow users to submit Actual Work per Day and then publishing the project.  If that option is greyed out, you will want to discuss with your server administrator.

image

I publish the project and navigate to the My Tasks screen using my browser.  Within this interface, I can update the actual hours worked per day (or per week).

image

I can also update the Remaining Work on the task.

image

Accepting those updates in Microsoft Project yields the following view.  Note that instead of simply booking a default 8 hours to the first two days of the task, I have booked 4 hours to each of the first two days.

image

Publishing the schedule pushes those task updates into TFS.  Here things start to look a bit strange.  I see that the Project Plan hours have updated correctly, but the TFS fields have not been updated, and still read Actual Work = 0 and Remaining Work = 40.

image

This gets a bit confusing admittedly.  What appears to be happening is that changes to the hour estimates in Project Server are communicated into TFS, but not transferred into the Work Item fields.  Fair enough.  I can see where that would make sense, i.e. I want to communicate to the developer what the schedule says, but I also want the developer to be able to make his own estimates – in the Work Item fields.

So I go ahead and make those changes in the Work Item fields.  I update TFS to read Actual Work = 10 and Remaining Work = 10.  I save the item.

image

The change in the TFS record then forces the change in Project Server.  Below, that change is visible in the PWA Approval Center.

image

…which then updates the project schedule.  My task in Project has now been overwritten by the TFS data.

image

If I want my users to update their hours in Project Server, this could be troublesome behavior – i.e. that the user can always update the hours within TFS and override the PWA timesheet interface.

Modifying the Default Behavior

But what if I want to change the default behavior?  What happens if I decide to make a commitment to put all of my TFS tasks into Project Server for tracking, and more importantly for my developers to track their time in a full featured timephased timesheet?

Well, after playing with the settings it would seem that this may be resolved with some minor changes to how the mapped fields are configured.

Take a look at the default field mapping.  When downloaded and opened as an Excel file, it appears as follows.  Note the DisplayTFSField and DisplayTFSMirror properties for the Completed Work and Remaining Work fields.

image

I set those two properties to “False” in the XML source file and re-upload the mapping.

image

Navigate to the Project Server tab for a TFS item.  See how the TFS values are not even displayed.  Two sets of books are no longer displayed.  More importantly, developers are forced to navigate to the PWA timesheet interface to update their tasks.

image

Note that since this is a hypothetical scenario, I admit that I haven’t worked through all of the implications of hiding the TFS fields for the Project Server fields, but I’d be curious if anyone has any feedback.

Decision Factors

For the most part, my conclusion is that the default behavior is structured to support an organization where tasks are updated primarily through TFS and then passed back into Microsoft Project.  Changes to TFS override Project Server.  Changes to Project Server get passed into TFS for reference.

Based on my discussions with clients to date, I would agree with this bias in the configuration of the default options.  Most of the groups I’ve discussed this functionality with have definitely leaned in that direction – especially as using TFS to update tasks means that they could leverage TFS to manage much of the detailed complexity of individual project schedules.

That being said, the discussion usually touches on a number of factors:

  1. Does the organization desire to keep a daily/weekly view of actual work performed?
  2. Is TFS being used to keep extraneous details about tasks out of project schedules, thereby allowing the project manager to focus on the high level details?
  3. Are developers expected to use any tool other than TFS to track their own hours?

Pending the resolution of any of those questions, the administrator may wish to look at the specific configuration of the integration package.

Selecting Time Tracking Options for TFS Integration

10 Things Project Server Admins Should Know About SharePoint

A couple of days ago, I posted on 10 Things SharePoint Admins should know about Project Server. That prompted SharePoint consultant Shane O’Hanlon (@GetOffMyPatch) to challenge me to list 10 Things Project Server Admins Should Know About SharePoint. Now, I would hardly claim to be much of a SharePoint expert…despite playing one at various SharePoint Saturdays, attending the local SharePoint User Groups, and having worked with some of the finest SharePoint consultants out there…but I figured it was worth a shot.

So here goes…..10 Things Project Server Admins Should Know About SharePoint (and for a wider post on Things Project Server Admins Should Just Know Anyway, please see here).

First, a bit of a caveat. These items may only be relevant to Project Server Admins who fit a specific profile. Will the Project Server Admin be the person installing the SharePoint farm on which it sits? (I certainly hope not.) Will the Project Server Admin be the person who is in charge of the disaster recovery solution? (Maybe/maybe not). So take these specific recommendations in the general sense in which they are proffered and filter them to your own specific roles and responsibilities.

1) Know How to Build Your Own Environment. Even if your organization gives you a shiny new hosted DEV environment to play with, nothing beats having your own Hyper-V image under your own control that you can break to your heart’s content and use to test out various data recovery and configuration scenarios. It’s far easier to restore to a previous snapshot than to submit an application to the server admins to get your DEV server refreshed every week – and it will save your doughnut and/or bagel budget when you have to start bribing them to do what you ask. Building your own farm is also a fantastic learning experience.

2) Understand the SharePoint Architectural Hierarchy. If you don’t know the difference between a web application and a site collection, go learn. Figure out how PWA and the BI Center fit into that schema. If you have the chance to catch the Shane Young/Todd Klindt roadshow, take a snapshot of “The Best SharePoint Slide Ever Made” and that should simplify everything. (And if anyone knows where that would be available online, please mention so in the comments list.)

3) It’s All About The Governance. Remember that projects are temporary. That means they eventually end. Lots of stuff is produced in the making of that project, not all of it valuable. Figure out what you’re going to do with that content. Project Server by default likes to create a site for every project, often resulting in thousands of abandoned project sites scattered through the farm. Implement a monitoring approach to flag unused or orphan project sites. Last but not least, make sure you have change and release management procedures in place – and just as important, make sure the folks in charge of patching the servers are in tune with your organizational reporting schedule. Don’t go deploying new patches and changes on the evening before your quarterly PMO report is due.

4) Size Does Matter. Often overlooked in the initial planning stages, make some rough assessments as to how much data will be stored on each project site. That will impact the fundamental architecture of your Project Server farm.

5) Plan Your Project Sites. Perhaps repeating the point about governance, but don’t simply start provisioning your farm with all sorts of project sites. Any changes to the content types or available metadata will have to get pushed out to each of those sites individually. Consider all of the changes you will need to make and build your template accordingly. Leverage content types to cascade changes out to already provisioned sites. Don’t know what content types are? See point #9.

6) Embrace the Complexity. Just because you are now a Project Server administrator, don’t try to implement a full SharePoint farm on your own. People study years to get up to speed on SharePoint (and then get recruited by consulting firms due to the market shortage of qualified consultants). Money spent architecting your farm and deploying it properly is money very well spent. That being said, don’t make a junior admin mistake and assume that all SharePoint consultants are created equal. There’re folks who are good at development, infrastructure and/or architecture. Some focus just on search. Don’t just assume that because they have SharePoint on their resume, they actually know how to design a system with Project Server.

7) Test Your Disaster Recovery Process. The Project Server disaster recovery process is separate (but related) to the SharePoint content recovery. Trust your SQL and SharePoint admins to implement disaster recovery, but verify that you personally, with your skill set, can restore all of that data to a new target environment (see Point #1).

8) Don’t Do The Express Installation. I hesitate to get this far down into the weeds on a technical issue, but this is probably the one absolute “No No” in the Project Server world that pops up on the forums every now and then. Don’t do the Express Installation. Project Server, and more importantly, the BI features, do not work well with SQL Express. Honestly, I am not even sure this option is still available in 2010 because I just gloss over it so quickly, that I don’t even think about it. Deploy a full farm with SQL, even if you’re just doing that on a single server.

9) Get Trained. Seriously. Go find a SharePoint class or two and take it. Try to find something offered by a reputable instructor. You don’t have to be an expert, but if you can get the basics up to the level of say, Site Collection Owner, you’re in good shape.

10) Learn to Love Your ULS Logs. ULS logs in 2010 are fantastic. Learn how to read them. Get into the habit of checking them on a daily basis or at the first sign of trouble. Even better, download the ULS log viewer utility from Codeplex and watch them in real time.

10 Things Project Server Admins Should Know About SharePoint

Great Project Server Content from the Product Team

Probably worthwhile to point you over to Robert Hoover’s post yesterday on some of the great content released this summer….

http://blogs.technet.com/b/epmcontent/archive/2011/08/11/new-and-updated-content-for-summer-2011.aspx

Big thanks to the folks responsible.  Make sure to check out the Pivot Viewer if you haven’t already.

Great Project Server Content from the Product Team

Creating a Portfolio Timeline Report in Visio (Part 3)

This post represents the third post in a series on creating a multiple timeline portfolio report within Visio. 

image_thumb1_thumb

For the previous two posts, please see Part 1 and Part 2.

Now that we’ve got this beautiful report, what do we do with it?  Well, the obvious answer would be to publish it to SharePoint using Visio Web Services.  This not only allows us to surface the data, but it provides an interactive interface so that users may click on projects to review more information in the Shape Data Window.

To publish, simply hit File > Save & Send, and select the option to publish to SharePoint as a Web Drawing.

image_thumb7

Note that one of the limitations (I suspect) of the Visio Web Service is that the data will get refreshed, and the shape data and data graphics will be updated on data refresh, but the x,y coordinates of an object will not get automatically recalculated until the Visio report is actually opened in Visio.  Once that’s done, the report will recalculate the locations of the projects, and may then be republished to SharePoint.

image_thumb9[1]

The report may now be used in a Visio WebPart on a PMO dashboard.  The best part, in my opinion, is that users can click on the individual projects and pull up a list of relevant fields directly from Project Server.

Creating a Portfolio Timeline Report in Visio (Part 3)