|
|
|
SQL Query - Issues associated for each project:
The following query lists all issues from all the Project Workspaces. This query works for the Issue list which has been created by using Microsoft Project Workspace template. When you use the default Project Workspace template to create the Project Workspace, you will be having the following columns for Issue list:
Default columns for Issue list:
The following query lists the Issues associated for each project in Project Server 2007 and also lists the linked items (linked tasks, linked issues or linked risks), if any associated.
How to Run: Run the following query on Microsoft SQL Server Management Studio, by selecting Project Server Reporting Database.
SELECT MSP_EpmProject.ProjectName AS ProjectName,
MSP_WssIssue_1.IssueID AS IssueID,
MSP_WssIssue_1.Title AS IssueTitle,
MSP_WssIssue_1.Owner AS IssueOwner,
MSP_WssIssue_1.AssignedToResource AS AssignedTo,
MSP_WssIssue_1.Status AS IssueStatus,
MSP_WssIssue_1.Category AS Category,
MSP_WssIssue_1.Priority AS Priority,
MSP_WssIssue_1.DueDate AS DueDate,
MSP_WssIssue_1.Discussion AS Discussion,
MSP_WssIssue_1.Resolution AS Resolution,
MSP_WssIssue_1.CreateByResource AS CreatedBy,
MSP_WssIssue_1.ModifiedByResource AS ModifiedBy,
MSP_WssIssue_1.ModifiedDate AS ModifiedDate,
MSP_WssRelationshipType.Description AS LinkType,
MSP_EpmTask.TaskName AS LinkedTask,
MSP_WssIssue.Title AS LinkedIssue,
MSP_WssRisk.Title AS LinkedRisk
FROM MSP_EpmTask RIGHT OUTER JOIN
MSP_WssRisk RIGHT OUTER JOIN
MSP_WssListItemAssociation ON MSP_WssRisk.ProjectUID =
MSP_WssListItemAssociation.RelatedProjectUID AND
MSP_WssRisk.RiskUniqueID = MSP_WssListItemAssociation.RelatedItemUID LEFT OUTER JOIN
MSP_WssIssue ON MSP_WssListItemAssociation.ProjectUID = MSP_WssIssue.ProjectUID AND
MSP_WssListItemAssociation.RelatedItemUID = MSP_WssIssue.IssueUniqueID ON
MSP_EpmTask.TaskUID = MSP_WssListItemAssociation.RelatedItemUID LEFT OUTER JOIN
MSP_WssRelationshipType ON
MSP_WssListItemAssociation.RelationshipTypeID =
MSP_WssRelationshipType.RelationshipTypeID RIGHT OUTER JOIN
MSP_EpmProject RIGHT OUTER JOIN
MSP_WssIssue AS MSP_WssIssue_1 ON MSP_EpmProject.ProjectUID = MSP_WssIssue_1.ProjectUID ON
MSP_WssListItemAssociation.ProjectUID = MSP_WssIssue_1.ProjectUID AND
MSP_WssListItemAssociation.ListItemUID = MSP_WssIssue_1.IssueUniqueID
Sample Query Result:
|
| |
|
