Information
The topic you requested is included in another documentation set. For convenience, it's displayed below. Choose Switch to see the topic in its original location.

Project Deliverable Gives/Gets Report

Office 2007

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

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

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.

NoteNote

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.

NoteNote

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.

NoteNote

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.

Show:
© 2015 Microsoft