Creating a Task Summary Crystal Report from Microsoft Project Server
page 3 of 6
by Eric Landes
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 28531/ 57

Database Layout and SQL Design

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

View Entire Article

User Comments

No comments posted yet.

Product Spotlight
Product Spotlight 

Community Advice: ASP | SQL | XML | Regular Expressions | Windows

©Copyright 1998-2024  |  Page Processed at 2024-06-15 3:06:45 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search