Project Deliverable Gives/Gets 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 deliverable gives/gets report enables a project manager to view related deliverables and projects in Microsoft Office Project Server 2007. The report includes deliverables that a project depends on (gets), deliverables that originate from the project (gives), and current status of the deliverables.
The project deliverables gives/gets report is intended to be executed by the project manager. The report requires the use of deliverables that are linked to a task. The report consumes the task data to show current complete or incomplete status and whether the deliverable is currently late.
Figure 1. Project deliverable gives/gets report
.gif "Project Deliverable Gives and Gets report")
The project deliverable gives/gets report shows ways to use the following new features of Project Server 2007:
Project deliverables
A document query for interaction with deliverables
Datasets for the Deliverables Report
The project deliverables gives/gets report is based on SQL queries of the Reporting database (RDB) for project deliverables. The ProjectList query provides the ProjectName field for use as a parameter in the IAmDependentOn and WhoIsDependentOnMe queries.
.gif "Note") 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 Solution Explorer, and then double-click 1.0 Project Give and Get Report.rdl. The design view of the report opens to the Layout tab.
To see the fields in the three datasets used in the report, on the View menu, click Datasets, 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 three queries.
Query for List of Projects
Select ProjectList in the Dataset drop-down list on the Data tab. The query simply returns the ProjectName field with a list of all projects from the MSP_EpmProject table in the RDB.
SELECT ProjectName
FROM MSP_EpmProject
ORDER BY ProjectName
Run the query to see the list of enterprise projects. When you run the report, SSRS populates the drop-down list of projects using the ProjectName field.
The project is a parameter for the IAmDependentOn and WhoIsDependentOnMe queries. To see the parameter definition, click in the Report Designer pane, and then click Report Parameters on the Report menu. To see the report definition language (RDL) code for the ProjectSelect parameter, right-click the report name in Solution Explorer, and then click View Code. The following code in the RDL file defines the ProjectSelect parameter.
<ReportParameters>
<ReportParameter Name="ProjectSelect">
<DataType>String</DataType>
<Prompt>Select Project for Report</Prompt>
<ValidValues>
<DataSetReference>
<DataSetName>ProjectList</DataSetName>
<ValueField>ProjectName</ValueField>
<LabelField>ProjectName</LabelField>
</DataSetReference>
</ValidValues>
</ReportParameter>
</ReportParameters>
Query for Deliverables from Other Projects
On the Data tab, select IAmDependentOn in the Dataset drop-down list. The IAmDependentOn query returns the following fields:
SelectedProject
ProjectName
Title
StartDate
FinishDate
The tables in the report get their data from the query fields. For example, on the Layout tab, right-click the text box that is directly below the Project Gives text box, and then click Properties. The text box name is SelectedProject, and the value is =Fields!SelectedProject.Value.
To run the IAmDependentOn query within the query designer, click Run (the red exclamation point) on the Data tab toolbar. Because the query contains the @ProjectSelect parameter, the Define Query Parameters dialog box asks for a parameter value. Type a known project name for the value, and then click OK. If the project subscribes to any dependencies from other projects, the Results pane shows a table with five columns for the query fields.
| Note |
|---|
If the project does not subscribe to any dependencies in other projects, the Results pane shows only the field names in the table header. The table contains no data rows. |
The IAmDependentOn query uses the following tables and views in the RDB:
MSP_EpmProject_UserView
MSP_WssDeliverable
MSP_WssListItemAssociation
When the MSP_WssListItemAssociation.ListItemUID field matches the MSP_WssDeliverable.DeliverableUniqueID for a related project, and the MSP_WssListItemAssociation.ProjectUID is the same as the MSP_EpmProject_UserView.ProjectUID, the selected project subscribes to a deliverable in the related project.
SELECT DependencyProj.ProjectName AS SelectedProject,
DeliverableProj.ProjectName,
DeliverableLinks.Title,
DeliverableLinks.StartDate,
DeliverableLinks.FinishDate
FROM MSP_WssListItemAssociation
INNER JOIN MSP_EpmProject_UserView AS DependencyProj
ON MSP_WssListItemAssociation.RelatedProjectUID
= DependencyProj.ProjectUID
INNER JOIN MSP_EpmProject_UserView AS DeliverableProj
ON MSP_WssListItemAssociation.ProjectUID
= DeliverableProj.ProjectUID
INNER JOIN MSP_WssDeliverable AS DeliverableLinks
ON MSP_WssListItemAssociation.ListItemUID
= DeliverableLinks.DeliverableUniqueID
WHERE (MSP_WssListItemAssociation.RelatedProjectUID
<> MSP_WssListItemAssociation.ProjectUID)
AND (DependencyProj.ProjectName = @ProjectSelect)
Query for Deliverables Given to Other Projects
On the Data tab, select WhoIsDependentOnMe in the Dataset drop-down list. The WhoIsDependentOnMe query returns the following fields:
SelectedProject
DependentProject
Title
StartDate
FinishDate
Run the WhoIsDependentOnMe query within the query designer. Because the query contains the @ProjectSelect parameter, the Define Query Parameters dialog box asks for a parameter value. Type a known project name for the value, and then click OK. If the project publishes any dependencies that other projects use, the Results pane shows a table with five columns for the query fields.
| Note |
|---|
If other projects do not subscribe to any dependencies from the selected project, the Results pane shows only the field names in the table header. The table contains no data rows. |
The WhoIsDependentOnMe query uses the same tables and views in the RDB that the IAmDependentOn query uses.
MSP_EpmProject_UserView
MSP_WssDeliverable
MSP_WssListItemAssociation
When the MSP_WssListItemAssociation.ListItemUID field matches the MSP_WssDeliverable.DeliverableUniqueID for a related project, and the MSP_WssListItemAssociation.RelatedProjectUID is the same as the MSP_EpmProject_UserView.ProjectUID, the related project subscribes to a deliverable in the selected project.
SELECT DeliverableProj.ProjectName AS SelectedProject,
DependencyProj.ProjectName AS DependentProject,
DeliverableLinks.Title,
DeliverableLinks.StartDate,
DeliverableLinks.FinishDate
FROM MSP_EpmProject_UserView AS DeliverableProj
INNER JOIN MSP_WssListItemAssociation
ON DeliverableProj.ProjectUID
= MSP_WssListItemAssociation.ProjectUID
INNER JOIN MSP_EpmProject_UserView AS DependencyProj
ON MSP_WssListItemAssociation.RelatedProjectUID
= DependencyProj.ProjectUID
INNER JOIN MSP_WssDeliverable AS DeliverableLinks
ON MSP_WssListItemAssociation.ListItemUID
= DeliverableLinks.DeliverableUniqueID
WHERE (MSP_WssListItemAssociation.ProjectUID
<> MSP_WssListItemAssociation.RelatedProjectUID)
AND (DeliverableProj.ProjectName = @ProjectSelect)
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.