For many developers, after years of coding they may move up to working on project management tasks in their career.  After years of software development, the project management track makes sense, as experienced developers know the tasks associated with custom software development. This experience helps with estimating tasks and leading groups of developers.

Those project managers, in most cases, utilize Microsoft Project software to help track their projects.  Those in large enterprises may utilize Microsoft Project Server.  Project Server has many great features for the Enterprise project manager, including resource Management, customization ability and more.

I have found myself in this camp, with a lot of experience with Microsoft Project Server.  This version of Microsoft Project is the Enterprise Project Management offering from Microsoft.  This article aims to show how to create a report from Project Server.  Specifically, we will be creating a report that displays tasks without actual compete dates.  We will have parameters that will allow the users to filter the reports by different criteria.

Crystal Reports XI

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.

Microsoft Project Server has a couple of places to view the entire schema of the databases.  In the SDK, if you look for projdb.htm and servrdb.htm, these documents contain a lot of schema information.  In this article we will do a deep dive on 3 tables.

For the setup of this report, we will be using tables msp_projects, msp_tasks and msp_text_fields.  If you are familiar with the backend of Project Server you know that msp_projects and msp_tasks contain the field proj_id.  MSP_tasks include tasks_id and tasks_uid.  Tasks_uid is the unique identifier that, when combined with proj_id, creates the primary key for the msp_tasks table.  Task_id is the number in the plan of the task; it is the number the user sees in the Project Client.

The The msp_text_Fields table is not a straight 3rd form normalized table.  It is more a key value type of table that contains values for custom fields for both the msp_projects and msp_tasks fields.  The structure contains 5 fields.  Proj_id relates directly to proj_id in both msp_projects and msp_Task.  Text_ref_uid relates to task_uid in msp_task (if we were using the resource table, then the ref_uid in msp_resources would map to text_ref_uid).  For msp_projects, the Text_Ref_uid is 0.  Text_value is of course, the value for the key. 

Finally, Text_Field_ID is the unique ID of 9 digits that signifies what custom field in project server this value is for.  For instance, I have created a custom field for a task called the element number.  This field has a task_field_id of 188744479.  If you need to find this number, you need to know what enterprise field this is and use the Visual Basic editor in the Project client.  This explanation is similar for the other key-value tables, msp_date_fields, msp_flag_fields and msp_number_fields.

In this instance, the element number field is Enterprise Text 1.  First, open the Visual Basic Editor then click on the object browser icon.   Under the classes section, find the PjCustomField entry.  With that highlighted, you can find pjCustomTaskEnterpriseText1.  With pjCustomTaskEnterpriseText1 highlighted (see Figure 1), the field id displays as 188744479.  

Figure 1

Now that we have an idea of the Project Database layout (specifically, about the fields we need), we are ready to create a report.  In the project we have created the custom field Element number.  This field groups project tasks across projects to help in our reporting.  Some people in Project circles may call this the WBS number. 

The idea is that for a task, say creating a Web Method, there is the same element number.  So if a department has 25 projects, and 14 projects have one or more tasks that create a web method, we can see actual numbers for how long it takes our group to create web methods.  You can also slice and dice tasks, like create Stored Procedure or get even more fine-grained.  You could have Create Insert Stored Procedure, Create Delete SP, or Create a complex select SP.  Assuming you keep the history, this can help with estimating.

For our report, we want to know which tasks that are standard elements- do not have actual start dates.  If you wanted to enforce your definition of Standard Elements, you should use a code table with those elements in them.  Then you would use that code table as the source for your element numbers.  This can be accomplished by utilizing the Project Wizard to create projects, or you could create a front-end web application utilizing PDS calls to create a project.

We could also specify other information using these custom fields.  For instance, if we had a custom Project field called Status, and Active was one of the values in Status, we could filter the report for Active projects.

For this report (elementsummary.rpt), we use a Cross tab in section 4.  For this cross-tab we will place the element number as the column across the top.  The first row is the project ID (could be the project name).  The major row (furthest to the left) is the project manager row.  So the report will display each major element for each project in the filter with the Planned Start and Finish Dates. 

You can add a parameter, like the Project Managers, in a drop down to make the filter more granular.  Making that a multiple value parameter would allow you to view a group of Project Managers, but not everyone.  If you had other custom fields at the project level, then you could add those as filters as well.  Options for that might include division, technology type, or customer.  It depends on how you slice and dice your projects.

We have gone through the database structure of Microsoft Project Server.  We did a deep dive on 3 tables and how they relate to each other.  For more details on Microsoft Project Server database schemas, see the files svrdb.htm and projdb.htm for the official word database schemas. 

If you are working with Project Server data and want to report on it, hopefully this article has helped walk you through how to present some of the data.  Keep managing projects and, as always, keep on coding!

Crystal Alliance - Crystal Reports Portal

Crystal Reports Forum

Crystal Reports Mailing List