My previous article on a Task Summary report for Microsoft project server focused on introducing you to the structure of using Microsoft Project Server to create a project management report.  This second article will show you a project velocity report that may be utilized in an agile methodology.  This article will use the Microsoft Project Server MSF Agile template from a blog entry I made a month or so ago.

Project Velocity reports are used in many agile methodologies.  The idea is to monitor daily the Velocity of the project by measuring tasks, bugs, issues, Scenarios and other types of work you may be tracking.  Some may want to monitor other things like story points.  This report may help you in that method, depending on how you track those points in Project Server.  Feel free to make this report work for your methodology at work; do not let it dictate how you work. 

This article is not designed to show you a particular agile methodology or how you will manage from that.  For that, I would do some research using the following links, David Anderson's blog,  Martin Fowler's books,  Ron Jeffrie's books, the book Agile Management for Software Engineering.  All these resources have helped me understand how to manage a project in an agile way.

I have found that by viewing the Project Velocity report daily, you can keep your pulse on the project's work.  I try to meet with the team each day to show them the amount of work getting done.  This helps us see patterns developing as the project progresses.

I hope to show you how this report was developed.  Keep in mind that we use this for our particular blend of MSF agile methodology.  We have made modifications that fit the way we work.  Your organization may use a different methodology.  This article can still be used as a basis for your Project Velocity report.

·         Crystal Reports .NET (2005)

·         Visual Studio 2005

·         Microsoft Project Server 2003

·         Microsoft SQL Server 2000

This article assumes that you are familiar with Microsoft Project Server, Crystal Reports and SQL Server 2000.

For a detailed explanation of how the custom fields in Project Server work, please look at the Database section of the article "Creating a Task Summary Crystal Report from Microsoft Project Server."  There you will find an explanation of how to access the custom fields. 

The Agile Project Server template we utilize as the basis for this project contains a custom field at the task level called Task Type.  To view this template, check out my blog entry with a link for the template.  The options available for this custom field include Quality of Service (QOS), Task and Issue.  In the project plan we are flexible enough to capture issues, but not bugs.  That needs to be captured elsewhere.

The template has other custom fields, like Discipline and GYR (Green, Yellow and Red).  The GYR field compares baseline finish dates to actual finish dates; a formula determines whether the task is Green, Yellow or Red. 

To display the data in the report, we use the SQL query shown in Listing 1.

Listing 1

select      tasktype.text_value as 'TaskType'
      ,Discipline.text_value as 'Discipline'
      ,mst.Task_name
      ,mst.TASK_ACT_FINISH
      ,msp.proj_name
      , case when TaskType.text_value = 'QOS Requirement'
                  then 1
                  else 0
        end as 'QOS'
      , case when TaskType.text_value = 'Scenario'
            then 1
            else 0
        end as 'Scenario'
      , case when TaskType.text_value = 'Task'
            then 1
            else 0
        end as 'Task'
 
from msp_tasks mst
      inner join msp_projects msp on (msp.proj_id = mst.proj_id)
      left outer join msp_text_fields TaskType on
 (tasktype.PROJ_ID = mst.proj_id and tasktype.TEXT_FIELD_ID = 188744479 and
 tasktype.TEXT_REF_UID = mst.task_uid)
      left outer join msp_text_fields Discipline
 on (Discipline.PROJ_ID = mst.proj_id and Discipline.TEXT_FIELD_ID = 188744481
 and Discipline.TEXT_REF_UID = mst.task_uid)
 
where msp.proj_id =32

In this SQL statement we define the Tasktype with a Field ID of 188744479.  The Discipline Task field is defined with a Field ID of 188744481.  Both are from the msp_text_fields table and will use the Text_Ref_UID joined with the Task_UID from msp_tasks.  And of course, we need the unique project id (proj_id) to make the final filter for the join.

The report for Project Velocity contains a graph and a few details of what is being counted.  The grouping for the report is set by the "actual finish date" field.  This grouping is used for the detail band and the graph.

In the data section of the chart, one of the series for the chart is set for a count of the actual finish date.  The other series are sums of the virtual fields (QOS, Task and Issue).    

The QOS, Task and Issues series are sums since we created the virtual fields to be a 0 of 1, depending on the Task Type of the record.  The line graph then displays a line for each task type that we have a sum for.  This should show you the Velocity for each task type, as well as a total velocity count.

Below the graph we have created a detail banded report that is grouped by actual finish date.  This shows the project manager and other members of the team if the project is gaining or losing speed.  If it looks like the project is losing steam and it should not be, then the PM should investigate.  Investigation can be done utilizing other reports or checking the Looking for blockades to development.  For a sample view see Figure 1 below.

This is the basis of the report.  This can be changed to track things such as Story Points, assuming you make that the task type custom field.

Figure 1 

This is how a project Velocity report can be created utilizing Crystal Reports with Microsoft Project Server.  We showed the SQL statement used to get the data for this report and we showed how we created the report that shows project velocity.  For more information on Agile methodologies and Project Management techniques, check out the links shown in the introduction.  Keep on Project Managing!