Microsoft Project

Analyzing Microsoft Project 2000 Data with Microsoft Office 2000 Web Components and OLE DB

This article dem onstrates how to bind Microsoft? Office 2000 Web Components (OWC) to Microsoft Project through OLE DB to provide custom ized, advanced reporting and analysis of project data. The exam ples use two of the controls to display data from a consolidated project (or m aster project) file and provide high-level information on inserted projects (or subprojects). In addition, this article explains how to convert a n HTML docum ent featuring OWC into an Active Server Page (ASP page) that can be used as a view in Microsoft Project Central.

Microsoft Office 2000 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 com parable to a sm all version of Excel charting, supporting most of the two-dim ensional chart types in Excel 2000 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 Com ponents enable a no-installation deploym ent and autom atic upgrade mechanism by using the codebase feature of Internet Explorer. The com ponents also include a sophisticated Web-installing control that m akes the download experience m uch more attractive. Furtherm ore, OWC does not depend on Office 2000 technology, so it can coexist in an Office 97 environment or be used on a machine that doesn't have any other part of Office installed.

Additional information

For m ore information about OWC, including issues with Netscape Navigator, see the article "Introducing the Office Web Com ponents." This information was excerpted from

(Microsoft Press, 1999), ISBN 0-7356-0794-X, and can be ordered online from Microsoft Press. Microsoft Project 2000 OLE DB provider

OLE DB consists of a collection of component object m odel (COM) interfaces to various database m anagement system services. OLE DB provides access to a particular data source by using a COM com ponent 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 com ponents, which you install with Microsoft Project), then 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 SQL database, you can also use the Jet 4.0 OLE DB provider. If the data is stored in an SQL or Microsoft Data Engine (MSDE) database, it can be accessed using the SQL OLE DB provider.

Microsoft Project Central data can be accessed using the SQL OLE DB provider if the project is stored in an SQL or MSDE database.

Specifics

A few aspects of the OLE D

B provider for Microsoft Project are unique. Please note the following to prevent unexpected results:

?There are four data types: text, num ber, Boolean, and date (as it appears in the interface, for example, 12/27/1999 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 m aterial 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 return 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 sam e 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. Also, forward-only

recordsets don't support the use of the RecordCount property.

?Joins are not supported. However, shaped recordsets can provide similar functionality by m aking relationships that had not previously existed between keys, fields, or rowsets. It is also possible to create hierarchical recordsets from a tabular form at. 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 2000, text fields can only be grouped into blank and nonblank.

Additional information

For m ore information on how to access Microsoft Project data through the Microsoft Project 2000 OLE DB provider, including the full list of limitations and the table structures exposed through OLE DB, see the file OLE_DB.ht m located on the Microsoft Project 2000 CD in the folder \Pfiles\MsOffice\Office\1033\. You m ay also find the article "Accessing a Data Source" useful when developing Web-based data access applications. Examples

The files that m ake up the examples consist of the following:

? A Microsoft Project docum ent (SoftDev.m pp).

?An HTML document (PivotTable.ht m) showing task name, duration, and work information with the PivotTable control. A version of this docum ent as an ASP page (PivotTable.asp) is also provided.

?An HTML document (PivotTableAndChart.ht m) showing task name, actual cost, and baseline cost inform ation with the PivotTable and Chart controls. A version of this docum ent as an ASP page (PivotTableAndChart.asp) is also provided.

Toolbox The files for the examples are available in the Microsoft Project 2000

Toolbox. Check the Toolbox for more information. Preparing your project

Before creating an HTML docum ent that will use OWC to display your project information, you m ay 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 it's bound to, but it m ay 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 form at 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 form at 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 minut es, although they are displayed as days by default.

2. Work fields are st ored by Microsoft Project as minutes, although they are displayed as hours by default.

3. Cost fields are stored as cents, so the formula for the cust om 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 docum ent consists of OBJECT tags for OWC and the code needed (in these examples, VBScript) to bind them to the project data provided through OLE DB. The m ost 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 form at both the results and the OWC.

PivotTable example

The PivotTable.ht m example uses the PivotTable control to display a three-colum n table consisting of task nam es, their duration, and the am ount 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 item s to note in Window_onLoad:

First, note that 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.9.0;Project Name=c:\My

Documents\SoftDev.mpp"

Second, 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.ht m is ConnectToTabular, which is used to bind the recordset to the PivotTable control.

PivotTable and Chart example

The PivotTableAndChart.ht m example uses the PivotTable control to display a three-column table of task nam es, their baseline costs, and their actual costs. Each colum n is given a descriptive heading, and the information for the two cost fields is formatted into dollars and cents. The exam ple also uses the Chart c ontrol to display a graphical representation of the sam e information.

There are two item s to note in Window_onLoad.

First, note that because the OWC is downloaded and run locally, the path specified for Project Nam e must be absolute rather than relative:

sConn = "Provider=Microsoft.Project.OLEDB.9.0;Project Name=c:\My

Documents\SoftDev.mpp"

Second, 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. (BindChartToRecordset was taken almost com plete 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. Converting HTML documents into ASP pages for Microsoft Project Central

To m ake an HTML docum ent featuring OWC available for viewing in Microsoft Project Central, you must change it to include specific lines of HTML code, as described in the following procedure.

1.Open the HTML document in a text editor.

2.Before any other text on the page, type the following line:

3.Insert the following code into your HTML docum ent after the tag but before

the