Project Review Report

This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.

The project review report is designed to provide a project management office (PMO) review meeting with a one-page or two-page summary of the current state of each project in Microsoft Office Project Server 2007. The report shows graphs of actual versus scheduled work and actual versus scheduled cost, project cost variance, and schedule finish variance, and tables of upcoming milestones, issues, deliverables, and risks.

The report assumes that each project has a baseline. For best illustration of the sample report, a project should begin life as a proposal, be approved, and then have added milestones, issues, deliverables, and risks.

Figure 1. Project review report

The project review report shows the following new features of Project Server 2007:

• Project deliverables

• Reporting database (RDB) access to all project data

• A document query to use for accessing project data

Datasets for the Project Review Report

The project review report uses SQL queries of the RDB. There are three report datasets:

• ProjectData includes a large set of fields from MSP_EpmProject_UserView and MSP_EpmTask_UserView as well as the RDB tables for issues, risks, and deliverables.

• DeliverableData includes a set of fields that combines data from MSP_EpmProject, MSP_EpmTask, MSP_WssDeliverable, and other RDB tables for list item associations and relationship types.

• Issues is a very simple dataset that includes all fields from the MSP_WssIssue table. The Issues query is Select * from MSP_WssIssue.

Note
A dataset in Microsoft SQL Server Reporting Services (SSRS) 2005 is created by a database query. An SSRS dataset includes fields used in the report; it is not the same as a Microsoft ADO.NET DataSet class. For more information, see Working with Data in Reporting Services.

To see the reporting datasets and queries, open the Project 2007 Report Pack solution with Microsoft Visual Studio 2005 (or Business Intelligence Development Studio for SQL Server Reporting Services), expand the Reports folder in the Solution Explorer, and then double-click 1.1 Project Review 2.rdl. The design view of the report opens to the Layout tab.

To see the fields in the three datasets used in the report, click Datasets on the View menu, and then expand all of the nodes in the Datasets pane. To see the queries, click the Data tab in the report design view, and then select one of the datasets. The report uses the graphical query designer for the ProjectData and DeliverableData queries, and the generic query designer for Issues.

ProjectData Query

On the Data tab, Select ProjectData in the Dataset drop-down list. The ProjectData query returns many fields, which you can see in the SELECT statement of the query. The report does not use all of the fields in the query; the sample ProjectData query provides extra fields to make the report easy to modify.

The charts and tables in the report get their data from the query fields. For example, on the Layout tab, right-click the Actual vs Scheduled Work chart, and then click Properties. The chart name is chart1. Click the Data tab in the Chart Properties dialog box, click Actual in the Values list, and then click Edit. The Actual series value is =Sum(Fields!ProjectActualWork.Value). You can also double-click the chart to add data and series fields or view their properties.

To run the ProjectData query within the query designer, click Run on the Data tab toolbar. The Results pane shows a table with 73 columns for the query fields.

The ProjectData query uses the following tables and views in the RDB:

• MSP_EpmProject_UserView

• MSP_EpmTask_UserView

• MSP_WssDeliverable

• MSP_WssIssue

• MSP_WssRisk

• MSP_WssListItemAssociation

Following is the complete ProjectData query for the project review report.

SELECT   MSP_EpmProject_UserView.ProjectStatusDate, 
         MSP_EpmProject_UserView.ProjectFinishDate, 
         MSP_EpmProject_UserView.ProjectStartDate, 
         MSP_EpmProject_UserView.ProjectName, 
         MSP_EpmProject_UserView.ProjectUID AS PrjUID, 
         MSP_EpmProject_UserView.ProjectWork, 
         MSP_EpmProject_UserView.ProjectCost, 
         MSP_EpmProject_UserView.ProjectActualCost, 
         MSP_EpmProject_UserView.ProjectActualWork, 
         MSP_EpmProject_UserView.ProjectStartVariance, 
         MSP_EpmProject_UserView.ProjectFinishVariance, 
         MSP_EpmProject_UserView.ProjectDurationVariance, 
         MSP_EpmProject_UserView.ProjectPercentCompleted, 
         MSP_EpmProject_UserView.ProjectOwnerName, 
         MSP_WssIssue.ProjectUID, 
         MSP_WssIssue.IssueUniqueID, 
         MSP_WssIssue.IssueID, 
         MSP_WssIssue.Title AS IssTitle, 
         MSP_WssIssue.AssignedToResource AS IssAssignedToResource, 
         MSP_WssIssue.NumberOfAttachments AS IssNumberofAttachments, 
         MSP_WssIssue.DueDate AS IssDueDate, 
         MSP_WssIssue.Category AS IssCategory, 
         MSP_WssIssue.Status AS IssStatus, 
         MSP_WssIssue.Priority AS IssPriority, 
         MSP_WssIssue.Owner AS IssOwner, 
         MSP_WssIssue.Discussion AS IssDiscussion, 
         MSP_WssIssue.Resolution AS IssResolution, 
         MSP_WssIssue.CreateByResource AS IssCreatedbyResource, 
         MSP_WssIssue.CreatedDate AS IssCreatedDate, 
         MSP_WssIssue.ModifiedByResource AS IssModifiedByResource, 
         MSP_WssIssue.ModifiedDate AS IssModifiedDate, 
         MSP_WssIssue.IsFolder AS IssIsFolder, 
         MSP_WssIssue.ItemRelativeUrlPath AS IssItemRelativeURLPath, 
         MSP_WssRisk.ProjectUID AS RiskProjUID, 
         MSP_WssRisk.RiskUniqueID, 
         MSP_WssRisk.RiskID, 
         MSP_WssRisk.Title AS RiskTitle, 
         MSP_WssRisk.Status AS RiskStatus, 
         MSP_WssRisk.AssignedToResource AS RiskAssignedToResource, 
         MSP_WssRisk.Owner AS RiskOwner, 
         MSP_WssRisk.DueDate AS RiskDueDate, 
         MSP_WssRisk.Probability AS RiskProbability, 
         MSP_WssRisk.Impact AS RiskImpact, 
         MSP_WssRisk.Exposure AS RiskExposure, 
         MSP_WssRisk.Cost AS RiskCost, 
         MSP_WssRisk.CostExposure AS RiskCostExposure, 
         MSP_WssRisk.Category AS RiskCategory, 
         MSP_WssRisk.Description AS RiskDescription, 
         MSP_WssRisk.MitigationPlan AS RiskMitigationPlan, 
         MSP_WssRisk.ContingencyPlan AS RiskContingencyPlan, 
         MSP_WssRisk.TriggerTask AS RiskTriggerTask, 
         MSP_WssRisk.TriggerDescription AS RiskTriggerDesc, 
         MSP_WssRisk.NumberOfAttachments AS RiskNumberofAttachments, 
         MSP_WssRisk.CreateByResource AS RiskCreatedbyResource, 
         MSP_WssRisk.CreatedDate AS RiskCreatedDate, 
         MSP_WssRisk.ModifiedByResource AS RiskModifiedByResource, 
         MSP_WssRisk.ModifiedDate AS RiskModifiedDate, 
         MSP_WssRisk.IsFolder AS RiskIsFolder, 
         MSP_WssRisk.ItemRelativeUrlPath AS RiskItemRelativeUrlPath, 
         MSP_EpmTask_UserView.TaskName, 
         MSP_EpmTask_UserView.TaskFinishDate, 
         MSP_EpmTask_UserView.TaskStartDate, 
         MSP_EpmTask_UserView.TaskDeadline, 
         MSP_WssDeliverable.Title AS DeliverableTitle, 
         MSP_WssDeliverable.StartDate AS [Deliverable Due Date], 
         MSP_WssDeliverable.DeliverableUniqueID, 
         MSP_WssListItemAssociation.RelationshipTypeID, 
         MSP_EpmProject_UserView.State, 
         MSP_EpmProject_UserView.ProjectCostVariance, 
         MSP_EpmProject_UserView.ProjectWorkVariance, 
         MSP_WssListItemAssociation.RelatedItemUID, 
         MSP_WssDeliverable.FinishDate AS Currently_Scheduled, 
         MSP_EpmTask_UserView.[TaskIsMilestone ]
         
FROM     MSP_WssDeliverable 
            INNER JOIN MSP_WssListItemAssociation 
               ON MSP_WssDeliverable.ProjectUID 
                  = MSP_WssListItemAssociation.ProjectUID 
                  
            FULL OUTER JOIN MSP_WssRisk 
            
            RIGHT OUTER JOIN MSP_EpmProject_UserView 
            
            INNER JOIN MSP_EpmTask_UserView 
               ON MSP_EpmProject_UserView.ProjectUID 
                  = MSP_EpmTask_UserView.ProjectUID 
               ON MSP_WssRisk.ProjectUID = MSP_EpmProject_UserView.ProjectUID 
               
            LEFT OUTER JOIN MSP_WssIssue 
               ON MSP_EpmProject_UserView.ProjectUID = MSP_WssIssue.ProjectUID 
               ON MSP_WssListItemAssociation.RelatedItemUID 
                     = MSP_EpmTask_UserView.TaskUID 
                  AND MSP_WssListItemAssociation.RelatedProjectUID 
                     = MSP_EpmProject_UserView.ProjectUID 
                  AND MSP_WssDeliverable.ProjectUID 
                     = MSP_EpmProject_UserView.ProjectUID
                     
ORDER BY MSP_EpmProject_UserView.State DESC, 
         [Deliverable Due Date], 
         MSP_EpmProject_UserView.ProjectName

DeliverableData Query

On the Data tab, select DeliverableData in the Dataset drop-down list. The DeliverableData query returns the following fields:

• RelationshipTypeID

• Description

• Title

• StartDate

• FinishDate

• CreatedDate

• ProjectName

• Related Project Name

• TaskName

• TaskIsCritical

• TaskStartDate

• TaskFinishDate

• Related Task Name

Note
The query shows a field name within brackets if it contains a space, for example, [Related Project Name]. The Datasets pane and the Layout tab of the Report Designer show the same field with a low line character in place of the space, for example, Related_Project_Name.

The DeliverableData query uses the following tables and views in the RDB:

• MSP_WssListItemAssociation

• MSP_WssDeliverable

• MSP_WssRelationshipType

The DeliverableData query gets all of the list item associations and the associated deliverables for each task in a project that has a deliverable. The last statement in the query is WHERE (MSP_WssRelationshipType.RelationshipTypeID = 12). You can find the meanings of relationship types with the following simple query of the RDB:

SELECT RelationshipTypeID, Description FROM MSP_WssRelationshipType

The query results are shown in the following table.

Following is the complete DeliverableData query for the project review report.

SELECT   MSP_WssRelationshipType.RelationshipTypeID,
         MSP_WssRelationshipType.Description, 
         MSP_WssDeliverable.Title, 
         MSP_WssDeliverable.StartDate, 
         MSP_WssDeliverable.FinishDate, 
         MSP_WssDeliverable.CreatedDate, 
         MSP_EpmProject.ProjectName, 
         EpmProject.ProjectName AS [Related Project Name], 
         EpmTask.TaskName, 
         EpmTask.TaskIsCritical, 
         EpmTask.TaskStartDate, 
         EpmTask.TaskFinishDate, 
         MSP_EpmTask.TaskName AS [Related Task Name]
         
FROM     MSP_WssListItemAssociation 
            INNER JOIN MSP_WssRelationshipType 
               ON MSP_WssListItemAssociation.RelationshipTypeID 
                  = MSP_WssRelationshipType.RelationshipTypeID 
                  
            INNER JOIN MSP_WssDeliverable 
               ON MSP_WssListItemAssociation.ProjectUID 
                  = MSP_WssDeliverable.ProjectUID 
                  AND MSP_WssListItemAssociation.ListItemUID 
                      = MSP_WssDeliverable.DeliverableUniqueID 
            INNER JOIN MSP_EpmProject 
               ON MSP_WssListItemAssociation.ProjectUID 
                  = MSP_EpmProject.ProjectUID 
                  
            INNER JOIN MSP_EpmTask 
               ON MSP_WssListItemAssociation.RelatedItemUID 
                  = MSP_EpmTask.TaskUID 
                  
            LEFT OUTER JOIN MSP_EpmProject AS EpmProject 
               ON MSP_EpmTask.ProjectUID = EpmProject.ProjectUID 
               AND MSP_WssListItemAssociation.RelatedProjectUID 
                   = EpmProject.ProjectUID 
                   
            LEFT OUTER JOIN MSP_EpmTask AS EpmTask 
               ON MSP_EpmProject.ProjectUID = EpmTask.ProjectUID 
               AND MSP_WssListItemAssociation.RelatedItemUID 
                   = EpmTask.TaskUID
                   
WHERE        (MSP_WssRelationshipType.RelationshipTypeID = 12)

For more information about fields in the RDB tables and views, see the Reporting Database Schema reference (pj12ReportingDB.chm) in the Project 2007 SDK download. For E-R diagrams of the main RDB tables and views, the RDBSchema.zip file in the SDK download contains a Microsoft Office Visio diagram and related information.

See Also

Concepts

Report Pack Setup

Other Resources

Working with Data in Reporting Services