Microsoft Project Server Data Reporting

Article
06/14/2014

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

Summary

The Microsoft® Project Server database is the repository for an enterprise's project management data. The new view tables that are included in the Microsoft Project Server database serve as an excellent source for reporting data about the entire portfolio of projects in the enterprise. These tables present stable snapshots of each project plan based on the last time a project was published to Microsoft Project Server and include a multitude of de-normalized timephased data, allowing for advanced reporting capabilities outside the Microsoft Project client.

Microsoft Project Server Tables

How View Tables are Populated

A project's view tables are refreshed after the project is published. Microsoft Project Professional automatically publishes projects when the Microsoft Project file is closed. Resource availability in the MSP_VIEW_RES_TP_BY_DAY table only generates across a date range, which is controlled by the administrator on the OLAP cube and resource availability page in Microsoft Project Web Access. The cube creation process is dependent upon the view tables for building the cube tables, and the fact tables underlying the cubes are utilized for Portfolio Analyzer.

Advantages of View Tables

View tables provide a consistent snapshot of approved data and de-normalized storage of timephased data. Because data automatically updates when a project is published from Microsoft Project, you do not need to use the OLE-DB provider. This means that cross-project reporting of timephased data is now significantly easier, enabling:

Synchronization of reports and timephased data with other systems (for example, time card systems, billing systems).

Creation of customized reports, including custom layout and content selection (for example, a robust three-dimensional report that includes resources, hours, and costs).

View tables simplify the creation of reports containing actual reported hours synched with actual reported hours contained in another system is simplified by the use of the view tables, and the previous OLE-DB based reports work with modifications and can be expanded to work across projects.

Designed for read-only access and reporting purposes, the Microsoft Project Server view tables and their fields are similar to OLE DB tables and fields. For more information, see the article "Microsoft Project 2002 OLE DB Provider Information" (oledb.htm) on your Microsoft Project Server CD.

Using the View Tables to Create Reports

SQL queries can be written to use the view tables to create custom reports from the entire portfolio of project data contained in the Microsoft Project Server database from the entire portfolio of projects. Types of reports that can be created using the view tables include:

An aggregation of task data from all projects, including total work for all tasks that include a certain code in the name or a certain value for an enterprise custom field.

A basic search for specific tasks from all projects sorted by work, duration, number of assignments, no assignments, and so forth.

A basic statistical data collection such as number of tasks, assignments, resources, work, or duration per project.

Any timephased data reports, such as planned or actual timephased work for a resource from all projects in the portfolio.

Project Statistics

The following script extracts a list of all projects that are currently stored in the Microsoft Project Server database, the project ID and title, the total number of tasks (assignments) for each project, and the number of enterprise resources assigned to the project. The script accesses the following Microsoft Project Server database tables:

Database Table	Information
MSP_VIEW_PROJ_TASKS_STD	Project names and project task names from the project IDs.
MSP_VIEW_PROJ_RES_STD	Enterprise resources from the enterprise resource IDs.
MSP_VIEW_PROJ_ASSN_STD	Count of project assignments from the project IDs.

  select

     p.ProjectUniqueID,

     p.ProjectTitle,

     (select count(*) from MSP_VIEW_PROJ_TASKS_STD t

        where t.ProjectUniqueID = p.ProjectUniqueID

        and t.TaskType < 4 and t.TaskID > 0) as TasksCount,

     (select count(*) from MSP_VIEW_PROJ_RES_STD t

        where t.ProjectUniqueID = p.ProjectUniqueID) as ResourcesCount,

     (select count(*) from MSP_VIEW_PROJ_RES_STD t

        where t.ProjectUniqueID = p.ProjectUniqueID

        and t.ResourceEnterpriseUniqueID > 0) as EnterpriseResourcesCount,

     (select count(*) from MSP_VIEW_PROJ_RES_STD t

        where t.ProjectUniqueID = p.ProjectUniqueID

        and t.ResourceEnterpriseUniqueID < 0) as LocalResourcesCount,

     (select count(*) from MSP_VIEW_PROJ_ASSN_STD t

        where t.ProjectUniqueID = p.ProjectUniqueID) as AssignmentsCount

  from

     MSP_VIEW_PROJ_PROJECTS_STD p

  order by p.ProjectTitle ASC

Basic Project Information

The following script extracts a list of all the project's tasks that are currently stored in the Microsoft Project Server database, including the project start and finish dates, the project duration, the project percent complete, the project work remaining, and the project costs for each project. This script could be used to create a report similar to the Project Center view in Microsoft Project Web Access. The script accesses the following Microsoft Project Server database tables:

Database Table	Information
MSP_VIEW_PROJ_TASKS_STD	Project task names, start date, finish date, duration, remaining duration, percent complete, work, remaining work, percent work complete, cost, and remaining cost from the project IDs.

  select

     TaskName,

     TaskStart,

     TaskFinish,

     TaskDuration/600 as TaskDuration,

     TaskRemainingDuration/600 as TaskRemainingDuration,

     TaskPercentComplete,

     TaskWork/60000 as TaskWork,

     TaskRemainingWork/60000 as TaskRemainingWork,

     TaskPercentWorkComplete,

     TaskCost,

     TaskRemainingCost

  from

     MSP_VIEW_PROJ_TASKS_STD

  where

     TaskID = 0

  order by

     TaskName asc

Project Task List Including Inserted

The following script creates a stored procedure that extracts a list of all project tasks that are currently stored in the Microsoft Project Server database for the given Project ID, including the project title, task name, task duration, and description of work, its start and finish dates, as well as any task predecessors. This stored procedure will also run on any inserted projects and return the task information for all of the inserted projects as well. When initially calling this stored procedure, you should call it with the Project ID that you are interested in and set the value of the Recursive variable to zero (0). The script accesses the following Microsoft Project Server database tables:

Database Table	Information
MSP_VIEW_PROJ_PROJECTS_STD	Project names from the project IDs.
MSP_VIEW_PROJ_TASKS_STD	Project task names, duration, work, start, finish, predecessors, and project file from the project IDs.

  IF EXISTS (SELECT id FROM dbo.sysobjects WHERE id = OBJECT_ID(N'dbo.MSP_EXT_SP_PROJECTVIEW')AND OBJECTPROPERTY(id, N'IsProcedure') = 1)

  DROP PROCEDURE dbo.MSP_EXT_SP_PROJECTVIEW

GO

  --The initial call to this stored procedure should include the ProjID that the

  -- caller is interested in and Recursive = 0

  CREATE PROCEDURE dbo.MSP_EXT_SP_PROJECTVIEW

     @ProjID int,

     @Recursive int

AS

  -- if it is a recursive call, then skip creation of the temp table

  if (@Recursive = 1) goto recursive_function

  create table #td_data (

     ProjectTitle nvarchar(255),

     TaskID int,

     TaskName nvarchar(255),

     TaskDuration int,

     TaskWork int,

     TaskStart datetime,

     TaskFinish datetime,

     TaskPredecessors nvarchar(1024))

  recursive_function:

  -- NOTE: Recursion nest level (@@NESTLEVEL) cannot be greater than 32

  -- declare function variables

  declare @ProjectTitle as nvarchar(255)

  declare @TaskID as int

  declare @TaskName as nvarchar(255)

  declare @TaskDuration as int

  declare @TaskWork as int

  declare @TaskStart as datetime

  declare @TaskFinish as datetime

  -- the following 2 nvarchar variables will hold implicitly converted ntext fields

  -- assuming that the ntext fields will not be greater than 1024 characters

  declare @TaskPredecessors as nvarchar(1024)

  declare @TaskSubprojectFile as nvarchar(1024)

  declare @TaskCursor as cursor

  -- end function variables

  set @TaskCursor = cursor for

  select

     p.ProjectTitle,

     t.TaskID,

     t.TaskName,

     t.TaskDuration,

     t.TaskWork,

     t.TaskStart,

     t.TaskFinish,

     t.TaskPredecessors,

     t.TaskSubprojectFile

  from

     MSP_VIEW_PROJ_PROJECTS_STD p,

     MSP_VIEW_PROJ_TASKS_STD t

  where

        p.ProjectUniqueID = @ProjID

  and   t.ProjectUniqueID = @ProjID

  and   t.TaskExternalTask = 0

  order by

     t.TaskID

  open @TaskCursor

  fetch next from @TaskCursor into @ProjectTitle, @TaskID, @TaskName, @TaskDuration, @TaskWork, @TaskStart, @TaskFinish, @TaskPredecessors, @TaskSubprojectFile

  while @@fetch_status = 0

  begin

     insert into #td_data values (@ProjectTitle, @TaskID, @TaskName, @TaskDuration/600, @TaskWork/600, @TaskStart, @TaskFinish, @TaskPredecessors)

     if @TaskSubprojectFile <> ''

     begin

        -- first get the PROJ_ID from the sub-project name

        declare @SubProjID as int

        set @SubProjID = (select PROJ_ID from MSP_WEB_PROJECTS where WLOCAL_PATH = @TaskSubprojectFile)

        -- execute the SPROC recursively with the SubProjID

        -- using this format to avoid a harmless warning message during creation of the sproc

        exec('exec MSP_EXT_SP_PROJECTVIEW ' + @SubProjID +', 1')

end

     -- get next row from cursor

     fetch next from @TaskCursor into @ProjectTitle, @TaskID, @TaskName, @TaskDuration, @TaskWork, @TaskStart, @TaskFinish, @TaskPredecessors, @TaskSubprojectFile

end

  close @TaskCursor

  deallocate @TaskCursor

  -- if it is recursive, exit the sproc

  if (@Recursive = 1) goto exit_sproc

  -- otherwise select * from the temp table and clean up

  select * from #td_data

  drop table #td_data

  exit_sproc:

GO

  GRANT EXECUTE ON dbo.MSP_EXT_SP_PROJECTVIEW TO MSProjectServerRole

GO

Resource Remaining Availability

The following script extracts timephased data about resources and assignments from the Microsoft Project Server database, including start dates and end dates, assigned work, and remaining availability. The script accesses the following Microsoft Project Server database tables:

Database Table	Information
MSP_VIEW_RES_TP_BY_DAY	Timephased information for resources used to determine resource availability for Assignment and Resource cube generation.
MSP_VIEW_PROJ_ASSN_TP_BY_DAY	Timephased information for assignments.
MSP_VIEW_PROJ_ASSN_STD	Standard information for assignments.

  IF EXISTS (SELECT id FROM dbo.sysobjects WHERE id = OBJECT_ID(N'dbo.MSP_EXT_SP_RES_REMAININGAVAILABILITY')AND OBJECTPROPERTY(id, N'IsProcedure') = 1)

  DROP PROCEDURE dbo.MSP_EXT_SP_RES_REMAININGAVAILABILITY

GO

  CREATE PROCEDURE dbo.MSP_EXT_SP_RES_REMAININGAVAILABILITY

     @EUID int,

     @StartDate datetime,

     @EndDate datetime

AS

  create table #td_data (

     TPDate datetime,

     Availability decimal(25, 6),

     AssignedWork decimal(25, 6),

     RemainingAvailability decimal(25, 6)

  -- declare function variables

  declare @CurDate as datetime

  declare @DateTotalWork as decimal(25, 6)

  declare @Availability as decimal(25, 6)

  --loop through day by day

  set @CurDate = @StartDate

  while DATEDIFF(day, @CurDate, @EndDate) > 0

  begin

  -- collect the availability for the current date

  select

     @Availability = a.ResourceTimeWorkAvailability

  from

     MSP_VIEW_RES_TP_BY_DAY a

  where

        DATEPART(dayofyear,a.ResourceTimeStart) = DATEPART(dayofyear, @CurDate)

  and   a.ResourceUniqueID = @EUID

  --collect all of the work for the current date

  select

     @DateTotalWork = SUM(w.AssignmentTimeWork)

  from

     MSP_VIEW_PROJ_ASSN_TP_BY_DAY w,

     MSP_VIEW_PROJ_ASSN_STD a

  where

        a.ResourceEnterpriseUniqueID = @EUID

  and   w.AssignmentUniqueID = a.AssignmentUniqueID

  and   w.ProjectUniqueID = a.ProjectUniqueID

  and   DATEPART(dayofyear, w.AssignmentTimeStart) = DATEPART(dayofyear, @CurDate)

  -- write out the info to td_data

  insert into #td_data values (@CurDate, @Availability/60000, @DateTotalWork/60000, (@Availability - @DateTotalWork)/60000)

  -- move to the next date

  Set @CurDate = DATEADD(day, 1, @CurDate)

end

  -- output the data from the temp table and clean up the temp table

  select * from #td_data

  drop table #td_data

GO

  GRANT EXECUTE ON dbo.MSP_EXT_SP_RES_REMAININGAVAILABILITY TO MSProjectServerRole

GO

Additional Information

For more information about the Microsoft Project 2002 OLE DB Provider, see the file PrjOLEDB.htm in the \Docs\1033 folder on the Microsoft Project installation CD, or in the C:\Program Files\Microsoft Office\Office10\1033 folder (1033 is the locale ID [LCID] for U.S. English; the folder for localized versions will vary. For example, the LCID for Japanese is 1041).

For more information about the Microsoft Project Server database, see the file PjSvrDB.htm in the \Docs\1033 folder on the Microsoft Project Server installation CD, or in the C:\Program Files\Microsoft Project Server\1033 folder (1033 is the locale ID [LCID] for U.S. English; the folder for localized versions will vary. For example, the LCID for Japanese is 1041).