Creating a Project Velocity Crystal Report using Microsoft Project Server
page 3 of 5
by Eric Landes
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 24218/ 58

Explanation of SQL Design

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'
      , 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.

View Entire Article

User Comments

No comments posted yet.

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

©Copyright 1998-2021  |  Page Processed at 2021-12-04 10:10:17 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search