Extracting Timephased Data from the Microsoft Office Project 2003 or Project Server 2003 Database

  • Article

This article describes three SQL Server™ script files you can use to extract information from:

  • The MSP_TIMEPHASED_DATA table in Microsoft Office Project 2003.
  • The MSP_WEB_WORK table or MSP _VIEW_PROJ_ASSN_TP_BY_DAY table in Microsoft Office Project Server 2003.

In addition, this article describes how to normalize the timephased data for display.

Download   Download the SQL Server™ script files, sp_pjactualsbyweek.sql, sp_psactualsbyweek.sql, and sp_psactualsbyday.sql from the Microsoft Office Project 2003 SDK (pj11SDK2003.exe) available from the Microsoft Download Center. The file contains all of the topics and source code samples for the Microsoft Office Project 2003 SDK, including the Extracting Timephased Data sample (Extract Timephased Data.exe).

Using Timephased Data

Timephased data is task, resource, or assignment information that is distributed over time. There are many reasons to extract timephased data from either the Project 2003 or Project Server 2003 database tables.

For example, you may want to access data from the MSP_TIMEPHASED_DATA table when Project is used, but not when Project Server 2003 is used. Or you might have Project Server 2003 installed, but progress is tracked directly in Project 2003 rather than in Project Server 2003.

The types of data that can be recorded in the MSP_TIMEPHASED_DATA table include:

  • Assignment actual work
  • Assignment actual overtime work
  • Assignment actual overtime cost
  • Assignment baseline work
  • Assignment baseline cost
  • Assignment actual cost
  • Resource baseline work
  • Resource baseline cost
  • Task baseline work
  • Task baseline cost
  • Task percent complete

The types of data that can be recorded in the MSP_WEB_WORK table include:

  • Scheduled work
  • Actual work
  • Actual overtime work

The types of data that can be recorded in the MSP_VIEW_PROJ_ASSN_TP_BY_DAY table include:

  • Assignment work
  • Assignment regular work
  • Assignment actual work
  • Assignment actual overtime work
  • Assignment baseline work
  • Assignment cost
  • Assignment actual cost
  • Assignment baseline cost
  • Assignment peak units

For information about how to find the data in the MSP_VIEW_PROJ_TASK_TP_BY_DAY and MSP_VIEW_RES_TP_BY_DAY tables, see the document PJDB.htm, located on the Microsoft Office Project 2003 CD in the DOCS folder.

Normalizing Timephased Data

Timephased data about tasks, resources, and assignments is stored in the MSP_TIMEPHASED_DATA table in Project 2003, and in the MSP_WEB_WORK table in Project Server 2003. Because of the way timephased data is stored for performance reasons, simply obtaining the values from one of the tables can make interpreting that data extremely difficult.

The SQL script files sp_pjactualsbyweek.sql (MSP_TIMEPHASED_DATA table), sp_psactualsbyweek.sql (MSP_WEB_WORK table), and sp_psactualsbyday.sql (MSP_VIEW_PROJ_ASSN_TP_BY_DAY table) extract this data and then assign one timescale unit per row, instead of up to seven timescale units per row (typically representing days), as is common in the Project database. The scripts also format these values to make them more readable, for example, by displaying the timescale unit and indicating whether the value is for time (hours), cost (dollars), or percentage of work complete.

Note  The SQL script files are included in the Extracting Timephased Data (TimePhased.exe) download, available from the Microsoft Download Center.

For more information about how timephased data is stored, including details about the columns (fields) referenced in the scripts and ways to safely modify the data in the Project or Project Server database, see the section "Managing Timephased Data" in the file PJDB.htm, located on the Microsoft Office Project 2003 CD in the DOCS folder.

Using the sp_pjactualsbyweek.sql Script

You can use the sp_pjactualsbyweek.sql script when you need to access timephased data from the MSP_TIMEPHASED_DATA table. Typically, you would use Project 2003 for this access, not Project Server 2003. You would also use Project 2003 instead of Project Server 2003 when Project Server 2003 is installed, but you track progress directly in Project 2003 .

When data is extracted from the MSP_TIMEPHASED_DATA table, it is not always stored in the database in the same way as it is presented in Project because Project handles timephased data while working with multiple calendars, constraints, and rules. As a result, extracted reports do not always agree with Project on when actual work was performed, although the total actual work performed should agree.

Before running the sp_pjactualsbyweek.sql script, you need to save timephased data in a readable format in the MSP_TIMEPHASED_DATA table.

To save timephased data:

  1. On the Tools menu, click Options.
  2. On the Save tab, select Expand timephased data in the database, which corresponds to the PROJ_OPT_EXPAND_TIMEPHASED column in the MSP_PROJECTS table.

The following figure shows the results of running this script from Microsoft SQL Server Query Analyzer using the Sample database (MPSSampleDatabase).

Timephased data from the MSP_TIMEPHASED_DATA table

Using the sp_psactualsbyweek.sql Script

You can use the sp_psactualsbyweek.sql script when you want to extract timephased data from the Project Server database in a way that is more usable than extracting data from the MSP_TIMEPHASED_DATA table in Project. You can also use this script when actual work values are recorded in Project Server.

The following figure shows the results of running this script from Microsoft SQL Server Query Analyzer using the Sample database (MPSSampleDatabase).

Timephased data from the Microsoft Office Project Server 2003 database

Using the sp_psactualsbyday.sql Script

You can extract data from the view tables in periods of one day, which makes this script more convenient for extracting data from the non-view tables. However, data in the timephased data view tables (MSP_VIEW_PROJ_ASSN_TP_BY_DAY, MSP_VIEW_PROJ_TASK_TP_BY_DAY, or MSP_VIEW_RES_TP_BY_DAY) are available only after the project has been updated with actual hours and published back to Project Server.

The following figure shows the results of running this script from Microsoft SQL Server Query Analyzer using the Sample database (MPSSampleDatabase).

Data from the view tables in periods of one day

Running the SQL Scripts

Although you can run the SQL scripts from an application like OSQL or Microsoft SQL Server Query Analyzer, they were designed to be implemented in SQL Server stored procedures. Stored procedures can be called from a script macro or from an application using an ActiveX® Data Objects (ADO) Command object, which may be more convenient for many users.

To run one of these script files, use either the command-line OSQL utility or Microsoft SQL Server Query Analyzer, available from the Tools menu of SQL Server Enterprise Manager. When you run one of these script files, you must specify the SQL Server computer and database name for the Project 2003 or Project Server 2003 database.

For more information about SQL scripts, see Documenting and Scripting Databases and Working with SQL Scripts.