How to show Implementation Milestone Dates in Project Center

This technique illustrates how to use formulas to extract dates from tagged milestone tasks within your project plans and show the dates in the Project Center.

This technique has been tested with Project Online and Project Server 2013 On Premises. However, I don’t see a reason why it wouldn’t work in Project Server 2010. The primary difference is that 2010 will require a round-trip of the data in Project Professional to calculate the formulas.

Scenario

I’ve had several clients ask “Can we show the next Implementation date in Project Center?”. The challenge is that the Implementation milestones are task level data. The Project Center only shows project level data, therefore you must extract and transform the data to show it appropriately.

Since you may have multiple Implementation milestones, this technique will show the next upcoming Implementation date. If there is no upcoming date, it will show the most recent past Implementation date. This way, the field should always show data.

Using Metadata Instead of Task Names

My first job in industry was converting old RPGII programs on the IBM System 38. I quickly learned the evils of using content as data as that was a very common technique in those days. This technique led to users accidentally breaking processes by inadvertently adding a space or changing a term. If you’ve told your users not to update a task name or the name of some other element, you too are using content as data.

Search engines later taught us to use metadata tags to separate functional data from content. Thus, this technique requires the creation of a task metadata field for tagging tasks with a specific reporting purposes. The illustrated design only allows one tag per task.

The benefit of this technique is that it is agnostic to the names of tasks and milestones, as long as they are tagged appropriately. PMs can change the task names and plan structure to meet their needs as long as they tag the tasks and milestones appropriately.

Metadata Lookup Table

The Reporting Purposes lookup table supports two levels of values. This enables multiple classes of tags, such as milestones and phases. This exercise focuses on the Milestone.Implementation value.

Metadata Custom Field

Create the Reporting Purpose task custom field and attach it to the Reporting Purposes lookup table. Specify that Only allow codes with no subordinate values is selected. This prevents the user from selecting Milestones without selecting a more specific purpose.

Extracting the Data

The technique takes advantage of the rollup capability within Project, that enables a task level value to be rolled up to Task 0 (Project Summary Task). Task 0 values can be retrieved and used in Project formulas, thus translating the task data to project data.

Task Custom Date Field Setup

Two task level formula fields are needed to determine which Implementation date is available as stated in the requirement above. Show me the next Implementation date or if not available, show me the most recent past Implementation date. These requirements constitute two separate conditions leading to the need for two separate task formula custom fields.

Next Implementation Task Date

The first task custom date field is Next Implementation Date. This field determines if there are one or more Implementation dates in the future. The future is defined as any date equal to or greater than today on any task marked with a Reporting Purpose value of Milestone.Implementation.

The rollup is used to retrieve the soonest Implementation date if there are multiples. When defining this field, specify the rollup behavior as Minimum so that the Implementation date closest to today will be shown. 

Formula

IIf([Reporting Purpose] = "Implementation" And [Finish] >= Now(), [Finish],

IIf([Reporting Purpose] = "Milestone.Implementation" And [Finish] >= Now(),

[Finish], ProjDateValue("NA")))

This formula may appear a bit odd when examined closely. It appears that there are duplicate conditions specified. This is done intentionally as a workaround to a difference between PWA and Project Professional in formula evaluation during the schedule edit.

The Reporting Purpose custom field is hierarchical so the tagged value is Milestone.Implementation. In PWA, the formula only sees the “Implementation” part of the value when you edit the schedule. The first check ensures that PWA evaluates the formula correctly. In Project Professional, the formula sees the whole value. The second check of the full value is added to ensure Project Professional evaluates the formula correctly. Both checks prevent data from being inadvertently deleted when editing in across both platforms. If neither value is found, the field is set to a special date value of NA, using ProjDateValue(“NA”).

Previous Implementation Task Date

This field helps determine if the most recent past Implementation date. This field determines if there are one or more Implementation dates in the past. The past is defined as any date less than or equal to today on any task marked with a Reporting Purpose value of Milestone.Implementation. The same double check of the Reporting Purpose is required to keep the editing behavior consistent.

Formula

IIf([Reporting Purpose] = "Implementation" And [Finish] <= Now(), [Finish],

IIf([Reporting Purpose] = "Milestone.Implementation" And [Finish] <= Now(),

[Finish], ProjDateValue("NA")))

Rollup to the Project Level

A Project level custom date field is created to perform the logic of determining the correct Implementation date to display in the Project Center. The formula checks the Next Implementation Task Date for a value of NA. If found, it assumes there is no future date and uses the most recent past date. If NA is not found, it uses the soonest future date.

Formula

IIf([Next Implementation Task Date] = ProjDateValue("NA"),

[Previous Implementation Task Date], [Next Implementation Task Date])

Add to the Task Summary View in PWA

I now add the Reporting Purpose, Previous Implementation Task Date and Next Implementation Task Date fields to the Project Task Summary view. This enables the PM to mark the appropriate tasks in PWA if desired. It also helps you see the effects of the formulas.

When I edit the project in PWA, I will see something like the following. Here, I’ve created a project with four Implementation milestones, two in the past and two in the future.

Today is April 16, 2014, so the next future Implementation date is 4/25, which is what should be shown in the Project Center, as seen at 1 below.

I hope you have found this technique to be useful. Please post any questions in the comments.