Call Us Today! 1-800-895-3254
[email protected]
Logo
Microsoft Project Services
Integent has specialized consulting and training services to handle all your project and resource management needs.
VIEW APPLICATIONS
Microsoft Training
Integent offers a comprehensive portfolio of training services, with an ideal fit for every organization. Ranging from general training courses to custom solutions, we have you covered.
See Full list of Courses
Learn About Training For
Microsoft Consulting
Integent provides consulting services for a variety of Microsoft applications including Microsoft Project, Project Online, Project for the Web, Dynamics 365, and Microsoft SharePoint.
Learn About Consulting For
Microsoft Support
Microsoft technologies set the standard for project portfolio management, business applications, collaboration, and more. Integent has flexible solutions to support every customer.
Learn About Support For
Microsoft Project
Integent has the knowledge and experience to help design and deploy a solution to help your organization manage projects and resources more effectively
Learn About Microsoft Project
Microsoft Dyamics 365
Integent can help you nsure business continuity with a custom cloud solution that connects sales, service, finance, and operations teams to deliver results.
Learn About Microsoft Dyamics 365
Microsoft SharePoint
SharePoint is Microsoft’s collaboration platform that is a place where team members can communicate, exchange data, share files and much more.
Learn About Microsoft SharePoint
Microsoft Power Platform
Consisting of Power BI, Power Apps, Power Automate and Power Virtual Agents, the Microsoft Power Platform helps streamline and improve business functions.
Learn About Microsoft Power Platform
About Integent
Integent was founded in 2009 on the principles of building long-lasting relationships with customers, exceeding expectations, and providing outstanding customer service. 

We’re dedicated to providing a customized solution backed by our knowledgeable team of Microsoft experts.
Discover What Makes Us Different
GSA Schedule
Integent is GSA Schedule Contract holder. A GSA schedule helps simplify selling our services to government customers.
Read More Here
News
Stay informed on Integent’s latest company updates and other important industry information. Our passion is customer success.
Read The Latest News
Videos
Learning new software doesn’t have to be difficult with our library of dozens of free training videos. Our video training always has something new.
Browse Our Training Video Library
Blog
Our industry experts share best practices and common pitfalls to avoid, in order to get the most out of your technology solutions.
Discover More

February 8, 2016

Reporting on Project Milestones–Tips and Tricks in Microsoft Project Online & Microsoft Project Server

One of the reports we’re frequently asked to create is a Milestones Report. Sometimes these are part of a project-by-project Status Report. Sometimes they are part of a portfolio report, where we might want to see the significant activities across all our projects.  The following tips can be used in any situation that involves reporting on task details.

1.  Create a custom field to identify which tasks should be included in the report.  I usually use a flag(Y/N) field with an easy to find field name like “Reporting Milestone”.

Sometimes you are asked to show details about the milestones, such as task health.  Once again, create a field to hold this value.

Now, here is the trick with these new fields.  You MUST create views in PWA schedules and Project Pro that display these fields.  The Project Managers must be trained on how to update them and that process must be included in their status update flow.

2. Be conscious of how the milestones are ordered. This can be tricky in an Excel report, so here’s one option.

Most of my clients like to see a list of project milestones ordered as they are in the project schedule.  This involves 2 fields, the TaskIndex and TaskName.

Depending on the report layout, I might be able to add the Task Index as the first column in my pivot report, causing the list of milestones to be ordered by that field.  The Task Index field is natively a number field, so it will sort properly.  However, in some situations I need to simplify the view by displaying the Task Index within the Task Name field. This might happen, for instance, if the first Excel column in my worksheet needs to be wide to accommodate other data in other tables.

I like the DAX formulas in PowerPivot, so I’m going to use a PowerPivot layer in my report. In this particular situation, I’m going to use the CONCATENATE command to join the fields. I use this formula:

=CONCATENATE([TaskIndex],CONCATENATE(" ",[TaskName]))

Notice I have to use “CONCATENATE” twice.  That is because, in DAX, the command can only work with 2 fields at a time. So, first I created a nested CONCATENATE for the last 2 items (the space between the Task Index and the Task Name, then the Task Name). Then I added the first, “master” CONCATENATE, which joined the Task Index to the other items.

My fields are now joined nicely into a new field which I will use in my reports. However, they have stopped sorting correctly.  Why?

When the Task Index field was by itself, it was a numeric field and sorted properly.  When I added it to the Task Name TEXT field, it started sorting oddly.  I needed to format the number in a different way to get it to sort. I needed to add leading zeroes so the text sorting would understand how to work with it.

I created another in-between field before using my CONCATENATE command:

=format([TaskIndex],"0000")

I chose a series of four zeroes because I wanted my list of project tasks to be able to go past 1,000. I wouldn’t, however, recommend having more than 9,999 tasks in a single project.  You can adapt the number of zeroes for your environment. Now my Task Index numbers look like this:

image

Notice they are now aligned to the left, indicating they are text. And look how nicely they sort!

Now I’m ready to add this column to Task Name, using the CONCATENATE command:

=CONCATENATE([Index-5Dig],CONCATENATE(" ",[TaskName]))

And this is my new column:

image

In addition to custom report development, Integent also offers reporting classes for Project Online and Project Server. If this is something you’d like to explore, contact us!

© Copyright 2022 | Integent.com | All Rights Reserved
linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram