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.

image

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.

image

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. 

image

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.

image

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.

image

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.

image

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.

image

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

Don’t Get Burned By Your Security Templates

image.png

Problem If you’ve ever tried to use the built-in security templates in Project Web App, you may have accidentally messed up your security model without realizing it. This problem applies to Project Server 2003-2013 versions. Security templates … [Continue reading]

Missed Project Conference?

image.png

Good news! All session recordings are now available online!  View them at http://channel9.msdn.com/Events/Project/2014/ Technorati Tags: Project Conference 2014,Project Server 2013,Project Online,Project Lite … [Continue reading]

Take Control of Your PWA Home Page with 4 Clicks

image.png

Have you ever looked at the Project Web App home page and felt it was simply too plain for Jane? I understand the need for design simplicity but sometimes the PWA home page simply feels like underutilized real estate. The Need Your manager wants … [Continue reading]

Project 2013 SP1 is now available!

Please use the following links to download the Service Pack 1 bits. Also, ensure you patch SharePoint and Project Server together. Project 32-bit     http://www.microsoft.com/en-us/download/details.aspx?id=42013 Project 64-bit     … [Continue reading]

New Project Portfolio Management (PPM) Guide Available

Microsoft has released the 2013 version of the Project Portfolio Management (PPM) guide for Project Server 2013 and Project Online. You can download it here. http://www.microsoft.com/en-us/download/details.aspx?id=41549   … [Continue reading]

Querying Multi-Value Custom Fields

thumbsup.jpg

Scenario You have a report where the need is to show multiple values for a given custom field. For example, you have a multi-value Project custom field for Impacted Business Organizations. You want to see your values as a comma delimited list so … [Continue reading]

Database Diagrams–Project Server Reporting Database

image.png

These high level entity relationship diagrams were first published in the deck for my Project Conference Hands On Lab deck. I’ve had a number of requests for this information so here it is. These diagrams are based on the 2010 RDB but the 2013 RDB … [Continue reading]

Retrieve Fiscal Year Dates Dynamically

MP9004423751.jpg

At some point, you will be asked for information by Fiscal Year. The Fiscal calendar was set up in Project Server so it should be accessible for reporting, right? A fair number of companies have fiscal calendars which don’t exactly line up with the … [Continue reading]

The Social Nature of Project Management

clip_image002.jpg

A successful configuration of Project Server is one that supports the conversations within the organization. Users have to go beyond use of the system and have concerns over the validity of the data entered. The Project instance captures the … [Continue reading]

%d bloggers like this: