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:
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:
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!
