Project Portfolio Trend 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 portfolio trend report is a simple list that enables the project management office (PMO) to see the time-phased status of a project in Microsoft Office Project Server 2007. The report includes key indicators based on custom field data that is manually updated. You can extend the report with custom programming to automate updating the key indicator data.
The project portfolio trend report (Figure 1) requires two project-level custom fields (outline codes) named Schedule Health Trend and Cost Health Trend. The custom fields share the Health Trends lookup table. As each reporting period is reached, the report manager selects a subordinate value for the custom fields. For information about creating the lookup table and custom fields, see Report Pack Setup.
Figure 1. Project portfolio trend report
.gif "Project Portfolio Trend report")
The project portfolio trend report shows how to use the following new features of Project Server 2007:
Multivalue project-level outline codes
A report document query to access and parse multivalue fields
Dataset for the Project Portfolio Trend Report
The project portfolio trend report uses a SQL query of the Reporting database (RDB). The Project_Data_Source report dataset includes the project name, report dates, and schedule and cost status based on custom field data for the report dates.
.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 Portfolio.rdl. The design view of the report opens to the Layout tab.
To see the fields in the dataset, click Datasets on the View menu, and then expand all of the nodes in the Datasets pane. To see the query, click the Data tab in the report design view to show the Project_Data_Source query. The sample report uses the graphical query designer.
Query for Portfolio Trend
On the Data tab, select Project_Data_Source in the Dataset drop-down list. The query returns the following fields:
ProjectName
ScheduleStatus
ScheduleReportDate
ResourceName
CostStatus
CostReportDate
The tables in the report get their data from the query fields. For example, on the Layout tab, right-click the text box in the top row of the right column, and then click Edit Group. The Grouping and Sorting Properties dialog box shows the group name is matrix1_ReportDate, and the value is =Fields!ScheduleReportDate.Value. The matrix control has a dynamic number of columns for the group, so the column repeats for each report date. Under the Sched text box, the text box named textbox2 has the value =first(Fields!ScheduleStatus.Value); therefore the value is the first value of the ScheduleStatus field for the schedule report date in the column.
To run the Project_Data_Source query within the query designer, click Run on the Data tab toolbar. The query does not contain any parameters, so it runs without user input. If any of the projects include the required custom field values for the reporting periods, the Results pane shows a table with six columns for the query fields.
When the MSP_EpmProject_UserView.ProjectUID field matches the [MSPCFPRJ_Cost Health Trend_AssociationView].EntityUID for a project, and the LookupMemberUID of the Cost Health Trend custom field is the same as the LookupMemberUID of the Health Trends lookup table, the value of the CostStatus field of the dataset is the value for that report date; that is, the value is Red, Yellow, Green, or Completed.
To understand the CAST and SUBSTRING functions in the SELECT statement of the Project_Data_Source query, it helps to create a simpler query that shows a breakdown of the compound statement. In Microsoft SQL Server Management Studio, select the RDB your report uses, and then design a query such as the following.
SELECT HealthTrends.MemberValue AS 'Member Value',
HealthTrends.MemberFullValue AS 'Member Full Value',
LEN(HealthTrends.MemberValue) - 1 AS 'Length Value',
LEN(CAST(HealthTrends.MemberFullValue AS char)) AS 'Length Full Value',
LEN(CAST(HealthTrends.MemberFullValue AS char))
- (LEN(HealthTrends.MemberValue) - 1) AS Diff,
SUBSTRING(CAST(HealthTrends.MemberFullValue AS char), 1,
(LEN(CAST(HealthTrends.MemberFullValue AS char))
- LEN(HealthTrends.MemberValue) - 1)) AS 'Date Substring',
CAST(SUBSTRING(CAST(HealthTrends.MemberFullValue AS char), 1,
LEN(CAST(HealthTrends.MemberFullValue AS char))
- LEN(HealthTrends.MemberValue) - 1) AS datetime)
AS 'Schedule Report Date'
FROM [MSPLT_Health Trends_UserView] AS HealthTrends INNER JOIN
[MSPCFPRJ_Cost Health Trend_AssociationView] ON
HealthTrends.LookupMemberUID
= [MSPCFPRJ_Cost Health Trend_AssociationView].LookupMemberUID
The simplified query returns fields that show intermediate values. For example, if there are two projects for a report date where the LookupMemberUID fields match in the Health Trends lookup table and the Cost Health Trend custom field, the Results pane shows the following table. The simplified query does not return a field for the project name.
Member Value |
Member Full Value |
Length Value |
Length Full Value |
Diff |
Date Substring |
Schedule Report Date |
|---|---|---|---|---|---|---|
Green |
2/9/2007.Green |
4 |
14 |
10 |
2/9/2007 |
2007-02-09 00:00:00.000 |
Yellow |
2/9/2007.Yellow |
5 |
15 |
10 |
2/9/2007 |
2007-02-09 00:00:00.000 |
The Project_Data_Source query uses the following tables and views in the RDB:
MSP_EpmProject_UserView
MSP_EpmResource
MSPCFPRJ_Schedule Health Trend_AssociationView
MSPCFPRJ_Cost Health Trend_AssociationView
MSPLT_Health Trends_UserView
| Note |
|---|
Project Server creates MSPLT_Health Trends_UserView in the RDB when you create the Health Trends lookup table. The MSPCFPRJ_Schedule Health Trend_AssociationView and MSPCFPRJ_Cost Health Trend_AssociationView views are created when you create the multivalue custom fields. |
Following is the complete Project_Data_Source query for the project portfolio trends report.
SELECT MSP_EpmProject_UserView.ProjectName,
[MSPLT_Health Trends_UserView].MemberValue AS ScheduleStatus,
CAST(SUBSTRING(CAST([MSPLT_Health Trends_UserView].MemberFullValue AS char),
1,
LEN(CAST([MSPLT_Health Trends_UserView].MemberFullValue AS char))
- LEN([MSPLT_Health Trends_UserView].MemberValue) - 1) AS datetime)
AS ScheduleReportDate,
MSP_EpmResource.ResourceName,
HealthTrends.MemberValue AS CostStatus,
CAST(SUBSTRING(CAST(HealthTrends.MemberFullValue AS char),
1,
LEN(CAST(HealthTrends.MemberFullValue AS char))
- LEN(HealthTrends.MemberValue) - 1) AS datetime)
AS CostReportDate
FROM [MSPLT_Health Trends_UserView]
INNER JOIN [MSPCFPRJ_Schedule Health Trend_AssociationView]
ON [MSPLT_Health Trends_UserView].LookupMemberUID
= [MSPCFPRJ_Schedule Health Trend_AssociationView].LookupMemberUID
INNER JOIN MSP_EpmProject_UserView
ON [MSPCFPRJ_Schedule Health Trend_AssociationView].EntityUID
= MSP_EpmProject_UserView.ProjectUID
INNER JOIN MSP_EpmResource
ON MSP_EpmProject_UserView.ProjectOwnerResourceUID
= MSP_EpmResource.ResourceUID
INNER JOIN [MSPCFPRJ_Cost Health Trend_AssociationView]
ON MSP_EpmProject_UserView.ProjectUID
= [MSPCFPRJ_Cost Health Trend_AssociationView].EntityUID
INNER JOIN [MSPLT_Health Trends_UserView] AS HealthTrends
ON [MSPCFPRJ_Cost Health Trend_AssociationView].LookupMemberUID
= HealthTrends.LookupMemberUID
ORDER BY ScheduleReportDate,
MSP_EpmProject_UserView.ProjectName
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.