Cost Center Availability Report

  • Article

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 cost center availability report enables a cost center manager to view resource usage across projects in Microsoft Office Project Server 2007. The cost center availability report is intended for online use because it requires the user to select a cost center and provides two different views of the data in one report. 

The first page of the report shows in graphical and tabular form how cost center resources are being used by month, the amount of admin time, and the resource capacity and current availability for work (Figure 1). To see the initial view of the report, select a cost center for analysis, and then click View Report.

Figure 1. First page of the cost center availability report

First page of Cost Center Availability report

Subsequent pages of the report provide detailed tabular information including which projects are underway for a resource during the selected time frame (Figure 2). The project table is defined in the resource assignment details subreport. To see the subreport, click the plus symbol next to the resource name in the cost center availability report.

Figure 2. Project information page in the cost center availability report

Project information in Cost Center Availability

For the report to be meaningful, all enterprise resources—with the exception of team resources and generic resources—should include a value for the Cost Center field. 

The cost center availability report shows the following new features of Project Server 2007:

  • Resource availability calculated from the new resource plan data as well as from assignments.

  • A report document query to use to calculate resource availability and capacity.

  • Cost center data to use.

  • Online reports to use to augment Project Web Access.

Datasets for Availability by Cost Center

The core of the cost center availability report is a SQL query of the Reporting database (RDB) for resource availability and capacity. The Project_Data_Source query uses the ResourceCostCenter field in the CostCenterList query as a parameter. You can modify the selection criteria in the queries; however, modification of the queries can significantly impact performance.

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 Cost Center Availability Report.rdl. The design view of the report opens to the Layout tab.

To see the fields in the two 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.

Query for List of Cost Centers

On the Data tab, select CostCenterList in the Dataset drop-down list. Because the query for cost centers is simple, the report uses the graphical query designer. The query returns a list of all cost centers from the MSP_EpmResource table in the RDB. Cost centers are valid if the resource is not a team or a generic resource.

SELECT    ResourceCostCenter
FROM      MSP_EpmResource
GROUP BY  ResourceCostCenter, ResourceIsTeam, ResourceIsGeneric
HAVING    (ResourceIsTeam = 0) AND (ResourceIsGeneric = 0)
ORDER BY  ResourceCostCenter

Run the query to see the list of cost centers defined for your enterprise resources. When you run the report, SSRS populates the drop-down list for cost centers using the ResourceCostCenter field (Figure 1).

The cost center is a parameter for the Project_Data_Source query. To see the report parameter definition for the cost center list, click in the Report Designer pane, and then on the Report menu, click Report Parameters. To see the report definition language (RDL) code for the CostCenter report parameter, right-click the report name in Solution Explorer, and then click View Code. The following code in the RDL file defines the CostCenter parameter.

  <ReportParameters>
    <ReportParameter Name="CostCenter">
      <DataType>String</DataType>
      <Prompt>Please select a Cost Center</Prompt>
      <ValidValues>
        <DataSetReference>
          <DataSetName>CostCenterList</DataSetName>
          <ValueField>ResourceCostCenter</ValueField>
        </DataSetReference>
      </ValidValues>
    </ReportParameter>
  </ReportParameters>

Query for Resource Availability and Capacity

On the Data tab, select Project_Data_Source in the Dataset drop-down list. Because the query for resource availability and capacity is complex, the report uses the generic query designer where you can type in only the Transact-SQL statements. (The Generic Query Designer button on the toolbar of the Data tab is selected.)

The Project_Data_Source query returns the following fields:

  • ResAvailability

  • ResourceUID

  • ResCapacity

  • ResAssignedWork

  • ResAdminTime

  • Mth (month)

  • Yr (year)

  • ResourceName

  • ResourceCostCenter

  • AllocatedTime

  • StartDate

  • FinishDate

The chart and tables of 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 chart, and then click Properties. The text box name is YR, and the value is =Fields!Yr.Value.

To run the Project_Data_Source query within the query designer, click Run (the red exclamation point) on the Data tab toolbar. Because the query contains the @CostCenter parameter, the Define Query Parameters dialog box asks for a parameter value. Type a known cost center for the value, and then click OK. The Results pane shows a table with twelve columns for the query fields.

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

  • MSP_EpmAssignment

  • MSP_EpmAssignmentByDay

  • MSP_EpmMetadataResourceTimeRange

  • MSP_EpmProject

  • MSP_EpmResource_UserView

  • MSP_EpmResourceByDay_UserView

  • MSP_Timesheet

  • MSP_TimesheetActual

  • MSP_TimesheetClass

  • MSP_TimesheetLine

  • MSP_TimesheetResource

The Reporting Database Schema reference (pj12ReportingDB.chm in the Project 2007 SDK download) shows that the MSP_EpmProject.ResourcePlanUtilizationType field specifies how the resource plan is used in a project. For more information, see Microsoft.Office.Project.Server.Library.ResourcePlan.UtilizationType.

The CASE statement of the Project_Data_Source query determines the assigned work for a resource based on the ResourcePlanUtilizationType field of each project, as follows:

  • If the ResourcePlanUtilizationType = 0, then the assigned work is based on the project plan.

  • If the ResourcePlanUtilizationType = 1, then the assigned work is based on the resource plan.

  • If the ResourcePlanUtilizationType = 2, then the assigned work uses the project plan up to the date of the resource plan and the resource plan assignments after that date.

Following is the complete Project_Data_Source query for the cost center availability report.

SELECT  SUM(MSP_EpmResourceByDay_UserView.Capacity) 
            - SUM(ISNULL(Work1.AssignedWork, 0)) 
            + SUM(ISNULL(Work1.AdminTime, 0)) AS ResAvailability, 
        MSP_EpmResource_UserView.ResourceUID, 
        SUM(MSP_EpmResourceByDay_UserView.Capacity) AS ResCapacity, 
        SUM(ISNULL(Work1.AssignedWork, 0)) AS ResAssignedWork, 
        SUM(ISNULL(Work1.AdminTime, 0)) AS ResAdminTime, 
        Month(MSP_EpmResourceByDay_UserView.TimebyDay) AS Mth, 
        Year(MSP_EpmResourceByDay_UserView.TimebyDay) AS Yr, 
        MSP_EpmResource_UserView.[ResourceName], 
        MSP_EpmResource_UserView.ResourceCostCenter, 
        (SUM(ISNULL(Work1.AssignedWork, 0)) 
            + SUM(ISNULL(Work1.AdminTime, 0))) AS AllocatedTime, 
        min(MSP_EpmResourceByDay_UserView.TimebyDay) as StartDate, 
        max(MSP_EpmResourceByDay_UserView.TimebyDay) as FinishDate
        
FROM    MSP_EpmResource_UserView 
            INNER JOIN MSP_EpmResourceByDay_UserView 
                ON MSP_EpmResourceByDay_UserView.ResourceUID 
                    = MSP_EpmResource_UserView.ResourceUID 
                    
            LEFT JOIN
                (SELECT Assign.ResourceUID AS N'ResUID', 
                        AssnByDay.TimeByDay AS N'Day1', 
                        N'AdminTime' = 0, 
                        N'AssignedWork' = 
                            CASE 
                            WHEN Proj.ResourcePlanUtilizationType = 0 
                                THEN AssnByDay.AssignmentWork 
                            WHEN Proj.ResourcePlanUtilizationType = 1 
                                THEN AssnByDay.AssignmentResourcePlanWork 
                            WHEN Proj.ResourcePlanUtilizationType = 2  
                                AND Proj.ResourcePlanUtilizationDate >= AssnByDay.TimeByDay 
                                THEN AssnByDay.AssignmentResourcePlanWork 
                            WHEN Proj.ResourcePlanUtilizationType = 2  
                                AND Proj.ResourcePlanUtilizationDate < AssnByDay.TimeByDay 
                                THEN AssnByDay.AssignmentWork 
                            END
                            
                 FROM   MSP_EpmAssignment AS Assign 
                        INNER JOIN MSP_EpmProject AS Proj 
                            ON Proj.ProjectUID = Assign.ProjectUID 
                        INNER JOIN MSP_EpmResource_UserView 
                            ON Assign.ResourceUID = MSP_EpmResource_UserView.ResourceUID 
                        INNER JOIN MSP_EpmAssignmentByDay AS AssnByDay 
                            ON (Proj.ProjectUID = AssnByDay.ProjectUID 
                            AND Assign.TaskUID = AssnByDay.TaskUID 
                            AND Assign.AssignmentUID = AssnByDay.AssignmentUID)
                                
                 WHERE  (Assign.AssignmentType IN (0, 1, 101))
                            UNION ALL
                            SELECT  MSP_TimesheetResource.ResourceUID AS N'ResUID', 
                                    MSP_TimesheetActual.TimeByDay AS N'Day1', 
                                    MSP_TimesheetActual.ActualWorkBillable AS N'AdminTime',
                                    N'AssignedWork' = 0
                                    
                            FROM    MSP_TimesheetActual 
                                    INNER JOIN MSP_TimesheetLine 
                                        ON MSP_TimesheetActual.TimesheetLineUID 
                                            = MSP_TimesheetLine.TimesheetLineUID 
                                    INNER JOIN MSP_TimesheetClass 
                                        ON MSP_TimesheetLine.ClassUID 
                                            = MSP_TimesheetClass.ClassUID 
                                    INNER JOIN MSP_Timesheet 
                                        ON MSP_TimesheetLine.TimesheetUID 
                                            = MSP_Timesheet.TimesheetUID 
                                    INNER JOIN MSP_TimesheetResource 
                                        ON MSP_Timesheet.OwnerResourceNameUID 
                                            = MSP_TimesheetResource.ResourceNameUID
                                            
                            WHERE   MSP_TimesheetClass.Type = 2) AS Work1 
                            
            ON MSP_EpmResourceByDay_UserView.ResourceUID = Work1.ResUID 
            AND MSP_EpmResourceByDay_UserView.TimeByDay = Work1.Day1
                                        
WHERE   (MSP_EpmResourceByDay_UserView.TimeByDay 
        BETWEEN
            (SELECT MSP_EpmMetadataResourceTimeRange.ResourceCapacityStart
             FROM   MSP_EpmMetadataResourceTimeRange)
          
        AND
            (SELECT MSP_EpmMetadataResourceTimeRange.ResourceCapacityEnd
             FROM   MSP_EpmMetadataResourceTimeRange)
        ) 
         
        AND MSP_EpmResource_UserView.ResourceIsTeam = 0 
        AND MSP_EpmResource_UserView.ResourceIsActive = 1 
        AND MSP_EpmResource_UserView.ResourceIsGeneric = 0 
        AND ISNULL(MSP_EpmResource_UserView.ResourceCostCenter,0) 
            = ISNULL(@CostCenter, 
                     ISNULL(MSP_EpmResource_UserView.ResourceCostCenter,0))
        
GROUP BY    MSP_EpmResource_UserView.[ResourceName], 
            MSP_EpmResource_UserView.ResourceUID, 
            Year(MSP_EpmResourceByDay_UserView.TimebyDay), 
            Month(MSP_EpmResourceByDay_UserView.TimebyDay), 
            MSP_EpmResource_UserView.ResourceCostCenter
            
ORDER BY MSP_EpmResource_UserView.[ResourceName], Yr, Mth

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

Reference

Microsoft.Office.Project.Server.Library.ResourcePlan.UtilizationType

Concepts

Report Pack Setup

Other Resources

Working with Data in Reporting Services