The Office XP Web Components, which ship as part of Office XP, provide functionality similar to traditional Office components, except that they work on Web pages. Some readers may recall Office Web Components from Office 2000. The Web components in Office XP offer enhancements over those available in Office 2000. The new components, however, are incompatible with their predecessors. Happily, you can use Office XP Web Components on computers running Office 2000 (and, of course, Office XP).

This first Daily Drill Down in a five-part series provides an overview of Office XP Web Components. If you passed on adopting Web components with Office 2000, you have new and improved reasons for learning about them with Office XP. Those of you already using Office 2000 Web Components will gain valuable information that will help you decide whether and how to upgrade. You will learn the kinds of tasks the Web components perform. Separate sections drill down on each of the four Office XP Web Components to give you a feel for their capabilities. You will also learn about the requirements for building solutions with Office XP Web Components.

Overview of Office XP Web Components
Office XP ships with four Web components. Each component has an ActiveX control that supports its functionality. Web authors can insert these controls on a Web page and graphically manipulate them to display interactive spreadsheets, charts, PivotTable reports, and data-bound forms and reports. Each component also has an object model that enables developers to program the Web components for custom dynamic and interactive behavior.

The Spreadsheet component provides two main features. First, it includes a recalculation engine based on the one that ships with Excel 2002. Second, it provides a user interface similar to Excel’s. Anyone who knows how to work a spreadsheet will feel right at home on your Web pages because the Spreadsheet component gives them the look and feel of a worksheet. The Spreadsheet component in Office XP Web Components supports multiple worksheets. This capability makes it easy to import data into one page and then display it in another. By tapping the recalculation engine, you can create custom calculators that perform all kinds of functions, such as those for net present value and various interest rates and payments.

The Chart component can operate independently to chart data typed directly into it or in coordination with other Web components. Users can change the data for a chart and get an instant update of the chart on your Web pages. The ability to link charts to other Web components makes it possible to update charts automatically as the contents of these other components change. You can display charts in 2-D and 3-D formats on your Web pages with the Chart component.

PivotTable reports are decision-support tools for aggregating and filtering data. The user interface for a PivotTable component enables a user to create cross-tab-style reports on a Web page dynamically. Web page users can rearrange row and column categories for aggregating data. In addition, users can readily filter data for individual row and column category variables, as well as a third category of page variables. Web page authors can empower users to build and analyze data with PivotTables by adding the PivotTable control to their Web pages. In addition, authors can specify a pool of fields for analysis by designating a record source for a PivotTable control on a Web page. A user can save custom representations of a PivotTable report on his or her local computer without affecting the Web-based version of a PivotTable report.

The Data Source control makes it easy to design Web pages that display data-bounds forms and interactive reports. The forms have the look and feel of Access forms, except that they reside on a Web page. The interactive reports based on the Data Source control permit users to add and edit data; this is an innovation with Office XP. As a Web page author, you will always design these forms and reports from within Access. Page authors and developers can use the Pages collection in the Access Database window to create new pages and to select existing ones for editing. The Pages collection corresponds to data access pages, which are standalone Web pages. However, the Pages collection members are just hyperlinks that point at the Web pages. Each of these Web pages is bound to an Access or SQL Server database.

Because the Office XP Web Components are a part of Office XP, page authors require an Office XP license to create and edit the pages. Workstations browsing and printing pages that contain Office XP Web components do not require an Office XP license. However, a license for Office XP is necessary to manipulate a Web component control on a Web page, such as to alter or to add data to a Spreadsheet component.

The Office XP Web Components install automatically with Office XP. You can distribute the Office XP Web Components to workstations that do not have a license for Office XP. In fact, this step is necessary to view Web pages with Office XP Web Components. See the Microsoft Office XP Resource Kit for further information about deploying Office XP Web Components.

In addition to the Web component library, browser workstations must also view pages with Internet Explorer 4.01+. To view the data access pages created with the Data Source control in Access, browsers must be Internet Explorer 5.0+. Selected designer and user features are available exclusively to those using Internet Explorer 5.5+. See the “Differences in page support between Internet Explorer 5 and 5.5” topic in Access Help for more detail on this topic.

Introduction to the Spreadsheet component
Excel and the Web: You cannot tell the difference
The Spreadsheet component has the look and feel of a workbook on a Web page. You can readily create a Web page with a Spreadsheet component from Excel by saving a workbook. In addition, you can also use menu commands in FrontPage and Access to add a Spreadsheet component to a Web page. Finally, you can add a Spreadsheet component directly to a Web page with the <OBJECT> tag and a classid setting that denotes the Spreadsheet component.

One of the easiest ways to start creating Web pages that contain a Spreadsheet component is from within Excel. Start by formatting a spreadsheet that performs some calculation, such as computing the payment for a loan. Next, choose File | Save As Web Page. Select the Add interactivity check box on the Save As dialog box. Use the File Name text box to assign a name for the Web page. Designate a storage location for the Web page file with the Save In drop-down box. This is typically a folder in an intranet site or a staging folder, one used for holding files meant for subsequent transport to a remote Web site. Instead of clicking Save, choose Publish on the Save As dialog box. Then, click Publish again on the Publish As Web Page dialog box. This creates a Web page in the designated folder that allows a user with an IE 4.0+ browser and the Office XP Web Components to interact with the spreadsheet. For example, a user can change the principal amount for a loan so the spreadsheet computes a new payment.

Another approach to creating a Web page with a Spreadsheet component is to use FrontPage’s Insert | Web Component command. FrontPage features a variety of components in addition to the Office XP Web Components. Therefore, select Spreadsheets And Charts in the Component Type list box on the Insert Web Component dialog box. This exposes selections for the Office Spreadsheet, Office Chart, and Office PivotTable components in the Choose A Control list box. Recall that creating data access pages with the Data Source control works exclusively from Access. To add the Spreadsheet component to the page, highlight Office Spreadsheet in the dialog box and click Finish. Next, add data and format the page.

Figure A  shows a pair of Web pages with Spreadsheet components. The page on the top is from Excel. The page on the bottom is from FrontPage. I created both pages based on the instructions described above. Despite some minor formatting differences, both pages contain a Spreadsheet component for computing a loan payment. The result is the same in both cases. Furthermore, users can interact with either Web page in an identical fashion and obtain identical results. For example, changing the term of the loan from 180 to 360 months reduces the payment from $887.97 to $644.04 in both Web pages.

Figure A
Two Web pages with Spreadsheet components created from Excel and FrontPage

Introduction to the Chart component
Dynamically update your charts
The Chart component lets you create charts in any of 12 popular styles, such as bar, pie, scatter, and area charts. Within each major style, you can select from two or more specific formats. You can also add and format chart elements, including a chart’s title and its legend. The series of values that a chart graphs can originate from manual entry, a database, or another Office XP Web Component.

One particularly interesting Chart component capability lets you tie a chart to a spreadsheet. By doing this, you can create a Web page that dynamically changes its chart as the page viewer changes the contents of a spreadsheet. This is one easy way to make charts interactive. With this approach, each browser workstation has its own version of the page. Users can make independent changes to the page in their browsers without affecting the views of other users or burdening the Web server.

Just as with the Spreadsheet component, you can insert a Chart control on a Web page in one of four ways. First, you can save a chart created from Excel. Next, you can base the series for a chart on a data access page. Third, you can insert a chart into a Web page from FrontPage. A wizard holds your hand through this process, so it is particularly easy to add a chart from FrontPage. Fourth, you can add a chart control using the <OBJECT> tag with the appropriate classid setting.

Figure B displays an HTML table on a Web page with two cells. In the table’s first column, a spreadsheet appears. A chart that ties to the spreadsheet is in the second column. The spreadsheet does various loan computations. The chart is a pie chart that contrasts the percentage of payments going toward principal vs. toward interest over the life of a loan. Web page viewers can interact with the spreadsheet to change these percentages by varying the number of years for a loan in cell B1. The settings for the chart designate cells A9 and A10 to contain the category labels. These labels serve as the legend values. The chart’s value settings in cells B9 and B10 denote the series that the pie chart plots. Each value represents a pie slice.

Figure B
A Chart control that dynamically adjusts its pie slices in response to spreadsheet entries

You can start to lay out a Web page in FrontPage like the one in Figure B by adding an HTML table to the page. Insert the spreadsheet in the left cell and complete its design with techniques described in the preceding section. Then, move the focus to the right cell in the HTML table. Choose Insert | Web Component. Highlight Spreadsheets And Charts in the Component Type list box and select Office Chart in the Choose A Control list box. Then, click Finish to open the Commands And Options dialog box. Highlight the name of the Spreadsheet control on the page as the source for the chart and click Ranges. This adds a Data Range tab to the dialog box and gives that new tab the focus. Click Add. Then, enter the spreadsheet cells for the category labels and values settings (namely, Sheet1!$A$9:$A$10 and Sheet1!$B$9:$B$10). Next, select the Type tab. Highlight the pie chart type and select the first of five pie chart options. Then, close the Commands And Options dialog box. Right-click the chart in a blank area to reopen the Commands And Options dialog box. Select the General tab. Click the Add Legend control on the tab and close the dialog box again. Finish the Web page by resizing the chart and table cell. Drag the boundaries to resize them.

Introduction to the PivotTable component
Expanding the capabilities of Excel
A PivotTable report can summarize one or more database fields by other fields within the spreadsheet. A PivotTable report offers other capabilities as well, such as filtering, sorting, and subtotaling. In fact, it earns its name because users can pivot, or rotate, fields around the report’s areas. This mix of functions makes PivotTable controls a popular decision-support tool. Business analysts first started using this type of control with Excel. Then, Microsoft introduced PivotTable functionality for Access 95. The PivotTable List control was introduced with the Office 2000 Web Components and is now one of the four Office XP Web Components.

Database developers who author Web pages can derive value from the PivotTable List control. The developer can specify a data source for the report that eliminates the need for end users to understand data access methods. The developer specifies the recordset for a PivotTable report. Then, users can draw on any of the fields in the recordset to define the details of a specific report instance. Users can return repeatedly to a Web page with a PivotTable List control and define new reports based on the fields that a developer or page author specifies as available for analysis.

Figure C displays a PivotTable report on a Web page. The report sums both Extended Price and Freight by ship country and shipper from the sample Northwind database. This sample database is available to both Access and SQL Server developers. The bottom row of the table displays the grand totals across all countries. Similarly, the two columns on the right present the sums of Extended Price and Freight across all shippers.

Figure C
Notice how the PivotTable chart report illustrates cross-tab features.

I developed the sample report in Figure C from within FrontPage. You can begin to do this by opening a blank page. Choose Insert | Web Component to add a PivotTable control to the Web page. Select Spreadsheets And Charts in the Component Type list box and highlight Office PivotTable. Click Finish to add the Web component to the page. Next, click the link to designate a record source within a data source that supplies fields for analysis with the PivotTable control. In the Commands And Options dialog box on the Data Source tab, click Edit. Then, click New Source on the Select Data Source dialog box if you do not see an item listed for the record source that you want to specify for the PivotTable report. This opens the Data Connection Wizard. Explore its options to specify a data source for the PivotTable report. I chose the Invoices table in the SQL Server Northwind database on the local server. After initially selecting a record source, you can select the record source again for new Web pages containing a PivotTable control. Close the Commands And Options dialog box after designating a record source for the report.

This opens a blank PivotTable report on your new Web page. The page contains four drop areas. The sample report in Figure C uses the row, column, and detail drop areas, and a fourth area that participates indirectly in the PivotTable crosstab by filtering records for the report. The area in Figure C labeled Drop Filter Fields Here is the fourth area. Click the Field List control on the PivotTable toolbar to open a list of fields for defining the report. Drag ShipCountry and ShipperName to the row and column drop areas, respectively. Next, drag Freight to the drop area for detail fields. Then, drag ExtendedPrice to the detail fields drop area. Next, right-click any instance of each detail field type and choose AutoCalc | Sum. This creates sums of ExtendedPrice and Freight for each cell, or combination of ShipCountry and ShipperName field values. It also automatically creates row and column totals for the two detail fields. Next, click the Hide Details control on the PivotTable control toolbar. Finally, rename the captions for the two detail fields. Right-click their labels and change their Caption settings on the Captions tab of the Commands And Options dialog box.

Introduction to the Data Source control
Focusing on data access pages
There is no direct manual interface to the Data Source control. This control provides the data for data access pages, PivotTable reports, and charts bound to relational databases. Since I have already introduced the Chart control and the PivotTable control, I will discuss the use of the Data Source control with data access pages.

A data access page is a Web page that you create and edit from Access. You can view the page from either Access or Internet Explorer 5.0+. Data access pages can contain either forms or interactive reports. Forms provide a means for binding text boxes, drop-down boxes, and other types of form controls to record source fields. Interactive reports allow the representation of parent-child relationships, such as the line items within an order. By default, interactive reports expose the parent data, but users can click an Expand control to expose related child data for any parent field value. The Data Source control binds these forms and reports to a record source. Users can update field values in both forms and interactive reports. The ability to update field values on interactive reports is an innovation introduced with the Office XP version of the Web controls.

To create a new data access page, select Pages from the Objects bar of a Database window for an Access database file or Access project that connects to a SQL Server database. By default, you will be able to select from record sources for the database file or SQL Server database, but you can also specify a record source in any other Access database file or SQL Server database. Given this start, there are several ways to finish creating a form on a data access page. Perhaps the easiest of these is to click New on the Database window and select AutoPage: Columnar in the New Data Access Page dialog box. Use the drop-down list control on the dialog box to select a record source, such as a table, for your page and click OK.

Figure D shows the result of selecting Northwind’s Shippers table as the record source for a form. The form resides on a data access page so that users can open it with an IE 5.0+ browser that can connect to a Web server containing the page. Notice that the navigation bar on the form offers expanded capabilities beyond those available on the built-in navigation bar for Access forms. For example, it allows you to sort the local record source for a form in either ascending or descending order on any field. The local sorted order in a browser does not affect the original order of the records on the data source. You can restore the initial order of records in a browser by refreshing the page. In addition to sorting and normal navigation functions, you can also filter records and commit or uncommit changes to a record showing on a form.

Figure D
A data access page with a form based on the Northwind’s Shippers table

Office XP Web Components offer page authors quick and easy ways to build Web applications. The use of ActiveX controls enables Microsoft to provide full-featured design-time creation and editing capabilities, as well as flexible end-user features. Users must have the Office XP Web Components and an IE browser installed on their workstations to use Web pages containing Office XP Web Components. Therefore, solutions constructed with these components are better suited for intranet or extranet applications when all users are running Office XP or when an organization and its partners have enterprise licenses for Office XP, which includes the IE 5.0 browser.