Grouping projects by their month of finish

Microsoft Project Server offers users many different views to resume projects in the Project Center area. Each one of these views is dedicated to organize the information accordingly with its subject,  allowing users to understand their portfolio from different angles. In addition, Microsoft Project Server allows users to create their own customized views, in order to organize the information they need.

When talking about creating customized views, a good idea would be to create a view that groups projects by their month of finish. This view may help Project Portfolio Managers to understand the projects that need to be finished in the coming months, as well as the effort that will have to be made.

Trying to use the native functionality

Before creating the view, it is necessary to know that there is no way to create it without using a formula. If you try to use the customized option ‘Group by’ that Microsoft Project Server offers, using the Finish field as the first group level, you will see that the projects will be grouped by the day they finish – instead of the month:

Creating the formula and adding it to the view

As soon as we know that the native functionality does not meet our needs, we have to create a formula that extracts only the month and the year of the projects – based on their Finish. To start creating the formula, go to Server Settings > Enterprise Custom Fields and Lookup Tables and click the New Field button. In this example, I will give the name Month of Go Live to the field. It will be a Project Entity and its type will be Text. In the Custom Attributes session, change the option to Formula.

At first, I would create a simple formula to extract the year and then combine it with the month of the finish date. The formula would be:

Year([Finish])&”/”&Month([Finish]) – where:

• Year([Finish]) – extracts only the year of the Finish date;
• &”/”& – adds the / character between the year and the month;
• Month([Finish] – extracts only the month of the Finish date.

After creating the formula, all I have to do is create a new view (or edit an existing one), insert the Month of Go Live field, and group the view using that field. After publishing some projects, the result would be like this:

Note that we do have a problem: projects that finish from February to September are organized after those who finish from October to December. This happens because Microsoft Project Server uses the first number as a key to classify the information and, using this criteria, 1 (the first number for October, November and December) will always come before 2, 3, 4 and so on. To fix this issue, we have to edit the formula and use conditional criteria: when the size of the Month (in number of characters) is equal to 1, the formula needs to add the 0 (zero) before the character. If not, it can just use the previous expression. The combination will be like this:

IIf(Len(Month([Finish])) = 1; Year([Finish]) & “/” & 0 & Month([Finish]); Year([Finish]) & “/” & Month([Finish])) – where:

• IIf(Len(Month([Finish]))=1 – if the size of the Month (based on Finish date) is equal to one, than;
• Year([Finish])&”/”&0&Month([Finish]) – extracts the year of the Finish date, concatenate with  “/”, concatenate with 0 and concatenate with the Month of Finish date. Else;
• Year([Finish]) & “/” & Month([Finish])) – extracts the year of the Finish date, concatenate with “/” and concatenate with the Month of Finish date.

After publishing the projects, the result would be like this: