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.
