Project Proposals Listing 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 proposals listing report shows a list of all project proposals grouped by the current proposal state. The report also displays an indicator for any proposal created within the past seven days. An indicator for recent proposals can be useful for project management office (PMO) status meetings.

To work as designed, the report requires that proposals exist and have a value in the State field of MSP_EpmProject_UserView in the Reporting database (RDB). The value of the State field can be Proposed or Approved, and is used for grouping the project proposals (Figure 1).

Figure 1. Grouping in the project proposals listing report

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

• Project proposals

• A document query for accessing proposal data

Dataset for the Proposals Report

The project proposals listing report uses a SQL query of the RDB. The Proposals report dataset includes the project name, start and finish dates, state, total work, and resource plan utilization type and date.

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 dataset and query, 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 Proposals Listing.rdl. The design view of the report opens to the Layout tab.

To see the fields in the report dataset, click Datasets on the View menu, and then expand all of the nodes in the Datasets pane. To see the Proposals query, click the Data tab in the report design view. The sample report uses the graphical query designer.

Query for Project Proposals

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

• Project Name

• Start Date

• Finish Date

• Project Type (proposals have a value of 4)

• State

• Work

• Resource Work

• TotalWork (calculated field)

• ProjectCreatedDate

• ResourcePlanUtilizationType

• ResourcePlanUtilizationDate

• NewProposal (calculated field)

• ProjectOwnerName

Note
The query shows a field name within brackets if it contains a space, for example, [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, Project_Name.

The tables in the report get their data from the query fields. For example, on the Layout tab, right-click the text box with the red formula under the New column heading, and then click Properties. The Textbox Properties dialog box shows the text box name is textbox9, and the value is =iif(Fields!NewProposal.Value = 1,">>","" ). The query calculates the NewProposal field to have a value of 0 or 1. When you run the report, the New column in the table shows the red text ">>" if the field value is 1.

The Proposals query uses only the MSP_EpmProject_UserView view in the RDB. The TotalWork field is simply the sum of the ProjectResourcePlanWork and ProjectWork fields. The query calculates the NewProposal field as 1 if the difference between the ProjectCreatedDate and the current date is less than eight days.

SELECT   ProjectName AS [Project Name], 
         ProjectStartDate AS [Start Date], 
         ProjectFinishDate AS [Finish Date], 
         ProjectType AS [Project Type], 
         State, 
         ProjectWork AS [Work], 
         ProjectResourcePlanWork AS [Resource Work], 
         ProjectResourcePlanWork + ProjectWork AS TotalWork, 
         ProjectCreatedDate, 
         ResourcePlanUtilizationType, 
         ResourcePlanUtilizationDate, 
         CASE WHEN 
            datediff(day, ProjectCreatedDate, getdate()) < 8 
            THEN 1 ELSE 0 
         END 
            AS NewProposal,
         ProjectOwnerName
 
FROM     MSP_EpmProject_UserView

GROUP BY ProjectName, 
         ProjectStartDate, 
         ProjectFinishDate, 
         ProjectType, 
         State, 
         ProjectWork, 
         ProjectResourcePlanWork + ProjectWork, 
         ProjectResourcePlanWork, 
         ProjectCreatedDate, 
         ResourcePlanUtilizationType, 
         ResourcePlanUtilizationDate, 
         ProjectOwnerName
         
HAVING   (ProjectType = 4) AND (NOT (State IS NULL))

ORDER BY State, 
         [Start Date], 
         [Finish Date], 
         [Project Name]

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