Share via

Analyzing Microsoft Project 2002 Data with Microsoft Office XP Web Components and OLE DB

  • Article

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

This article demonstrates how to bind Microsoft® Office XP Web Components (OWC) to Microsoft Project through OLE DB to provide customized, advanced reporting and analysis of project data. The examples use two of the controls to display data from a project file and provide high-level information about the summary tasks. If the project file is a master project, high-level information about the subprojects is displayed.

Microsoft Office XP Web Components

The controls used in this article are the PivotTable® and the Chart. When bound to an online analytical processing (OLAP) cube, the PivotTable control can provide a flexible, high-performing analysis surface. The Chart control is comparable to a small version of Excel charting, supporting most of the two-dimensional chart types in Excel 2002, as well as a Polar chart type.

The components will function in Internet Explorer 4.01 and later, although they won't function in any earlier versions (4.0, 3.x, 2.x, and so on).

Deployment

The Office Web Components enable a no-installation deployment and automatic upgrade mechanism by using the codebase feature of Internet Explorer. The components also include a sophisticated Web-installing control that makes the download experience much more attractive.

Additional Information

For more information about OWC, including issues with Netscape Navigator, see the article Introducing the Office Web Components. This information was excerpted from Dave Stearn's book Programming Microsoft Office 2000 Web Components (Microsoft Press, 1999), ISBN 0-7356-0794-X, and can be ordered online from Microsoft Press.

Microsoft Project 2002 OLE DB Provider

OLE DB consists of a collection of component object model (COM) interfaces to various database management system services. OLE DB provides access to a particular data source by using a COM component called a data provider, which is often referred to as an OLE DB provider. If the system that the Microsoft Project solution runs on has the appropriate OLE DB provider installed, as well as the core ActiveX® Data Objects (ADO) and OLE DB components that you install with Microsoft Project, that solution can use ADO code to work with the data exposed by that provider.

You can use the Microsoft Project OLE DB providers to access project data. If the data is stored in a Microsoft Access or Microsoft SQL Server™ database, you can also use the Jet 4.0 OLE DB provider. If the data is stored in an SQL Server or Microsoft Data Engine (MSDE) database, it can be accessed using the SQL OLE DB provider.

Specifics

A few aspects of the OLE DB provider for Microsoft Project are unique. Please note the following to prevent unexpected results:

  • There are four data types: text, number, Boolean, and date (as it appears in the interface, for example, 12/27/2002 10:30 AM).
  • Duration fields return minutes ?10 (for example, 8 hours is 4800) and Work fields return minutes ?1000 (for example, 8 hours is 480000).
  • Work values for material resources are returned in the units defined in the interface, rather than minutes ?1000.
  • A formula in a custom field that would display #ERROR in the interface returns the default value for the field.
  • Custom fields where no value has been set return the default value.
  • Custom field indicator fields where no indicator has been set return -1.
  • Dates that would be "NA" in the interface return as 0.
  • Using SELECT without WHERE returns null resource and task rows. Supplying WHERE does not return these same rows, even if they would otherwise qualify.
Limitations

The current implementation of the Microsoft Project OLE DB provider has a few limitations, which include the following:

  • Read/write access is not supported.
  • Access provided with forward-only cursors: Forward-only recordsets don't support methods such as MovePrevious, MoveFirst, or MoveLast. In addition, forward-only recordsets don't support the use of the RecordCount property.
  • Joins are not supported. However, shaped recordsets can provide similar functionality by making relationships that had not previously existed between keys, fields, or rowsets. It is also possible to create hierarchical recordsets from a tabular format. For more information, see "Microsoft Data Shaping Service for OLE DB" in ActiveX Data Objects Help.
  • The ANY, LIKE, and IS NOT operators are not supported.
  • When bound to the PivotTable control that shipped with Microsoft Office XP, text fields can only be grouped into blank and nonblank.
Additional Information

For more information on how to access Microsoft Project data through the Microsoft Project 2002 OLE DB provider, including the full list of limitations and the table structures exposed through OLE DB, see the file Prjoledb.htm located on the Microsoft Project 2002 CD in the folder \FILES\PFILES\MSOFFICE\OFFICE10\1033\. You may also find the article Accessing Data with ADO useful when developing Web-based data access applications.

Examples

The files that make up the examples consist of the following:

  1. A Microsoft Project document (SoftDev.mpp).
  1. An HTML document (PivotTableXP.htm) showing task name, duration, and work information with the PivotTable control.
  1. An HTML document (PivotTableXPAndChartXP.htm) showing task name, actual cost, and baseline cost information with the PivotTable and Chart controls.

The Analyze Data with OWC Sample (apdsamples.exe) file is available from the Microsoft Download Center.

Preparing Your Project

Before creating an HTML document that will use OWC to display your project information, you may wish to preformat the data that will be provided through OLE DB. It is best to preformat the data because the data bound to OWC through the provider may not be useful as displayed. For example, the provider exposes Work fields (typically expressed in hours) as thousands of minutes. The PivotTable can apply only a format to the information that it's bound to, but it may prove more useful to apply both a formula (to convert to hours) and a format (to add the word "hours" to the display).

To apply both a format and a formula, you can create custom fields in your project for the data you intend to bind to OWC, and apply the necessary formula before binding to the OWC. Because the custom fields are simple number fields and are passed on "as is" by the provider, they avoid the OLE DB issue, and the OWC can format the data as desired.

The following table lists the custom field names and formulas in SoftDev.mpp that were used for this example.

Name

Formula

OLE DB Duration(days) (Number1)1

[Duration]/480

OLE DB Work(hours) (Number2)2

[Work]/60

OLE DB Baseline Cost (Number3)3

[Baseline Cost]

OLE DB Actual Cost (Number4)

[Actual Cost]
  1. Duration fields are stored by Microsoft Project as minutes, although they are displayed as days by default.
  1. Work fields are stored by Microsoft Project as minutes, although they are displayed as hours by default.
  1. Cost fields are stored as cents, so the formula for the custom field is the value of the cost field, which can then be formatted into dollars and cents by the PivotTable.
Creating the HTML

The source for each HTML document consists of OBJECT tags for OWC and the code needed (in these examples, Microsoft Visual Basic® Scripting Edition [VBScript]) to bind them to the project data provided through OLE DB. The most important subroutine in the source runs during the onLoad event of the Window object and contains all the code required for data access, as well as the code needed to format both the results and the OWC.

PivotTable Example

The PivotTableXP.htm example uses the PivotTable control to display a three-column table consisting of task names, their duration, and the amount of work needed for each. Each column is given a descriptive heading, and the information for the duration and work columns includes days or hours, respectively.

There are two items to note in Window_onLoad:

  • Because the OWC is downloaded and run locally, the path for the MPP file passed to the provider must be absolute, rather than relative:
  sConn = "Provider=Microsoft.Project.OLEDB.10.0;Project _

       Name=C:\Program Files\Microsoft Project 2002 SDK _

       \AnalyzeProjectData\SoftDev.mpp"
  • Note that the SQL query passed to the provider selects three fields from the provider's Tasks table, but only those where the Boolean TaskSummary field is 0 (False), that is, all nonsummary tasks:
  sSQL = "SELECT TaskName, TaskNumber1, TaskNumber2 FROM _ 

       Tasks WHERE TaskSummary=0"

The other subroutine in PivotTable.htm is ConnectToTabular, which is used to bind the recordset to the PivotTable control.

PivotTable and Chart Example

The PivotTableXPAndChartXP.htm example uses the PivotTable control to display a three-column table of task names, their baseline costs, and their actual costs. Each column is given a descriptive heading, and the information for the two cost fields is formatted into dollars and cents. The example also uses the Chart control to display a graphical representation of the same information.

There are two items to note in Window_onLoad.

  • Because the OWC is downloaded and run locally, the path specified for Project Name must be absolute rather than relative:
  sConn = "Provider=Microsoft.Project.OLEDB.10.0;Project _

       Name=C:\Program Files\Microsoft Project 2002 SDK _

       \AnalyzeProjectData\SoftDev.mpp"
  • Note that the SQL query passed to the provider selects three fields from the provider's Tasks table, but only those where the TaskOutlineLevel is 1, that is, only top-level tasks:
  sSQL = "SELECT TaskName, TaskNumber3, TaskNumber4 _

  FROM Tasks WHERE TaskOutlineLevel=1"

In addition to Window_onLoad, the code includes the following subroutines:

  • ConnectToTabular, which is used to bind the recordset to the PivotTable control.
  • BindChartToRecordset, which is used to bind a separate recordset to the Chart control. (The BindChartToRecordset subroutine was taken almost word-for-word from page 77 of the book Programming Microsoft Office 2000 Web Components.) It is also possible to bind the chart to the PivotTable and then bind the PivotTable to a recordset.