Loading...
Quick Links

Grouping Projects by Month in Project Server 2010

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:

Grouping Projects by Month in Project Server 2010

Grouping Projects by Month in Project Server 2010

 

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:

Grouping Projects by Month in Project Server 2010

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:

Grouping Projects by Month in Project Server 2010

Raphael Santos
Written by Raphael Santos

Raphael Santos is a PPM enthusiast with expertise in providing Microsoft Project, Project Online, and Project Server solutions. He has worked in several projects to implement PPM tools, including projects located in Latin America (Brazil, Peru, and Argentina), in the United States, and in Africa. He is also a trainer with more than 10 years of experience teaching users how to use Project Management tools in a more productive way. In 2016, Raphael was awarded the MVP title by Microsoft in recognition of his contributions to the Project Management community. Raphael is a PPM Consultant at Sensei Project Solutions, a certified Microsoft partner specializing in project and portfolio management deployments. Sensei offers a complete set of services to help organizations succeed with their Microsoft PPM deployments. Services include full implementation and training as well as pre-configured solutions and report packs. Visit senseiprojectsolutions.com or contact info@senseiprojectsolutions.com for more information.

Share This Post
11 Comments
  1. Not having worked with Server but rather with MSP2010 Professional and earlier versions, Grouping by Month seems very simple.

    I also use Group by Finish. Difference is that I use the “Define Group Intervals…” button. Setting Define Group Intervals…” to Months and setting the “Start at:” date to the first of a month yields a clean grouping by month. Does server not offer this option? Regards.

    Reply
  2. When I enter then save the formula Year([Finish])&”/”&Month([Finish]) I recieved this error message “The custom field could not be saved due to the following reason(s):
    •This formula contains references to one or more fields that do not exist in the system. Correct the formula and try again”. There is a Finish field in our Project Server. Can you please help?

    Reply
  3. @Angelo Arcoleo – unfortunately, Project Server does not offer the possibility to group the information as Project Professional does. That is the reason we have to do this customizations.

    @Ken Trinh – this sounds weird. It is more likely for us to have this kind of issue when using custom fields we have created (and in this case we are using a Project Server´s field). Can you please try to use the Pick Field button and select the Finish field instead of typing it? Please let me know if it works.

    Reply
  4. I used the Pick Field button this time and the same error appears. By using the Pick Field Button the forumla is entered as Year[Finish]&”/”&Month[Finish], without the “()” around Finish. I’m not using the “=” sign to begin the forumla or spaces in between, regardless I’ve tried with and without “=” and/or spaces but the error still remains. Can you share a screenshot of what you see from your end?

    Reply
  5. Ken,

    I have tried to create the field again, and I had the same issue you have found. I think the problem is related to the quotation marks. It seems that Project Server is not understanding the quotation marks when you use ‘copy-paste’. Please try to delete it and insert the information by typing the quotation marks.

    You can find a link to the screenshot of my test:

    https://www.box.com/s/ktuu6in6e4czbe39pzzc

    Reply
  6. Thanks, the shorter forumla now works but the extended formula does not. I manually typed in: IIf(Len(Month([Finish])) = 1; Year([Finish]) & “/” & 0 & Month([Finish]); Year([Finish]) & “/” & Month([Finish])) but this error appears “The formula contains errors. Correct the formula and try again”. I tried using the Pick Field button but the error still appears. I’m typing this into the newly created field Month of Go Live.

    Reply
  7. Ken,

    Sorry, I think there is a little misunderstood. As my primary language is Portuguese, I have to use semicolon in my formula. Assuming that your language is English, you have to replace this item for the simple comma.

    Please let me know if it works.

    Reply
  8. Yes by using commas instead it now works perfectly. Thanks for the help.

    Reply
  9. Cool =D

    Reply
  10. Hi, I have added the field with the formula, but now I can not see it when creating the view. Any ideas?

    Reply
  11. Hi Milton

    After creating the field and adding it to the view, you´ll have to publish the projects so the formula will be calculated and the view will be available.

    Please let me know if this works.

    Raphael

    Reply

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Please complete this equation so we know you’re not a robot. *

Thanks for submitting your comment!
You must be logged in to comment.