Does your organization need custom decision-support tools that work on its Web pages? If so, the PivotTable list control introduced with Office 2000 may be the answer. Pivot tables are a mature technology within Microsoft Office that lets users interact with crosstabs to modify, or pivot, the elements of their layout. Since crosstabs serve as input to many business decision-making activities, pivot tables are a valuable resource for business analysis. The new PivotTable list control expands the reach of pivot tables by enabling them on Web pages for the first time.
Pivot tables offer significant benefits for business analysis. Users can redesign a pivot table by dragging row and column field labels to new positions. They can also filter and sort the contents of a crosstab report, graphically add new fields to a crosstab from a data source defined by the designer of the pivot table, or refresh a table against a data source to have their instance of the pivot table reflect the most recent data in its source. With Office 2000, Microsoft made it possible to design these capabilities into Web pages through the PivotTable list control—one of the Office Web Components.
Users can open pivot tables from Excel, Access, and Internet Explorer. IT pros, such as database managers and Web masters, can design applications with PivotTable lists from Excel, Access, and FrontPage. The rules for designing and using pivot tables vary slightly between its various usage and design environments. This article focuses on using the new PivotTable list control introduced with Office 2000 to design pivot tables in FrontPage 2000 for viewing on a Web page by IE 4 and 5. You will learn about referencing relational database sources, such as those from Access and SQL Server.
What is a PivotTable list?
Developers can use pivot tables to organize and summarize relational data sources, spreadsheets, or SQL Server OLAP data cubes. The pivot table organizes the data by one or more row and column fields. Pivot tables must always have at least one data field, but you can use more than one. Typically, a pivot table has one or more row and column fields.
The PivotTable list organizes one or more data fields by the row and column fields. You can open the row and column label buttons to restrict selected values from inclusion in a PivotTable list report. In addition, you can filter data with a Page field. You can use a Page field to filter data when the filter field is neither a row nor a column in a report’s design.
With the PivotTable list, you can summarize the data fields at the intersection of row and column field labels with one of four functions. These are Sum, Count, Min, and Max. The output from one of these functions is a total or summary field. You can invoke the Sum, Min, and Max functions only with numeric data fields. The Count function is the only way to summarize data fields containing text strings. You can also summarize numeric data field values with the Count function. The PivotTable list can display the detailed data field values, the summaries of values, or both.
What makes a pivot table especially attractive for business analysis is the fact that it lets users rearrange the row and column field labels. The PivotTable list enables this functionality on a Web page. In addition, Web page designers can optionally enable users to add and remove row, column, and data fields besides those appearing on a Web page.
Users do not need to open or even have installed on their workstation Excel, Access, or any other Office component to work with a PivotTable list. All a user workstation requires is IE 4 or 5 and the Office Web Component library installed on the workstation. IE 5 continues to ship without charge. In addition, it (and the Office Web Components, including the PivotTable list control) installs with Office 2000. Webmasters can design Web pages that automatically download the Office Web Components library from an image of Office 2000 created by running setup.exe /a. The Web installer for the Office Web Components needs a collection of items from the Office setup image and not just the Microsoft Office Web Component library file (Msowc.cab). For example, it also needs to download and install MDAC 2.1 and a collection of Help files, which are also in the Office install image. You can obtain more coverage of these topics from the Office 2000 Resource Kit and a white paper (http://officeupdate.microsoft.com/2000/downloaddetails/owcwp.htm).
Adding a control and making a connection
The initial steps in making a PivotTable list work on a Web page include adding the ActiveX control to a page and setting a database connection for the control.
You can add a PivotTable list to a Web page with FrontPage by choosing Insert | Components | Office PivotTable. This inserts a blank rectangle with a toolbar across its top on the Normal tab in FrontPage. The HTML tab shows an OBJECT tag sandwiched between a pair of paragraph tags. The OBJECT tag references the PivotTable list control through its classid parameter.
To make a PivotTable list function on a Web page, you must specify its data source. This is a two-step process when working with a relational database, such as Access or SQL Server. First, graphically set a connection to the database. Second, specify a database object, such as a stored query, or a SQL string for designating selected records in the relational database as the data source for the PivotTable list. It is possible to add the control and designate a data source for it programmatically. Therefore, if you decide to use the toolbars and dialogs for this purpose, you might as well create a view or stored query with the data that you want for the PivotTable list. This approach allows you to use Access’ graphical query design tools for creating stored queries and views in Access database files and SQL Server databases.
Start the process of designating a data source for the PivotTable list by clicking the Property Toolbox button on the toolbar. Then, open the Data Source portion of the PivotTable Property Toolbox dialog. Select the Connection radio button. Next, click the Connection Editor command button to open the Data Link Properties dialog. On the Provider tab, choose a data source type. Your options include Jet 4.0, SQL Server, Oracle, and more. The selected option on the Provider tab determines the layout for the Connection tab. Complete the Connection tab to specify the particular database or file that you will use as the data source. The Data Link Property dialog compiles a connection string based on your replies to its Connection tab. Two additional tabs allow more refinements in how your PivotTable list connects to its data source. You can click the Test Connection button on the Connection tab of the Data Link Properties dialog to verify the link to the data source. Clicking OK on the Data Link Properties dialog returns control to the PivotTable Property Toolbox. Click the drop-down arrow in the Data Member box to expose a list of database objects from which you can choose the specific data source for your PivotTable list.
After referencing a data source, the rectangle representing the control changes from blank to one with a title bar and four drop areas. The title bar shows the name of the database object selected for the data source. You can edit this default name as your application requires. Click the Field List toolbar button to open a list of fields that you can drag into any of the four drop areas. Use this dragging process to populate the row, column, filter, and data fields for the PivotTable list.
Figure A shows a PivotTable list with the Invoices query from the sample Northwind Jet database serving as a data source. The figure shows the Salesperson field dragged to the row drop area and the ExtendedPrice field dragged to the data field drop area. The Column and Filter, or Page Filter, areas remain exposed. You can successively drag multiple fields to any drop area.
Three sample PivotTable lists on Web pages
It’s easy to design a quick report like the one in Figure A with a PivotTable list. In this section, you’ll learn how to embellish reports by adding total fields and build reports with multiple fields for row and column grouping. The discussion of samples in this section also illustrates some of the actions users can perform with PivotTable lists. Web page designers can successively restrict user capabilities until the reports in PivotTable lists are completely non-interactive.
If a Web page designer permits it, users can modify a PivotTable list on a Web page even more simply than by following the steps for generating the display shown in Figure A. By default, users can add fields to a report. If a user initially opens a Web page from a browser containing a PivotTable list without any fields in its report, the PivotTable Field List dialog appears automatically. Users can drag fields from the dialog into the drop areas on the blank report. Changes made by a user in a browser are limited to the instance of the PivotTable list in that browser. Updates by users do not propagate to the original version of a PivotTable list on a Web server.
The display in Figure A lists all the ExtendedPrice field values in the Invoices query by salesperson. While this data can be informative, some analysts may want to see the sum of the ExtendedPrice field values for each employee. This gives an overall summary of the sales by each salesperson. To incorporate a Sum function into the report, right-click the field you want to summarize—ExtendedPrice in the data area, in this case. From the context menu, choose AutoCalc | Sum. This makes the sum of sales appear at the bottom of the window showing the detailed sales for each salesperson. You can hide the detailed sales data and show only the totals by right-clicking Salesperson in the row drop area and clearing the check from Expand on the context menu.
After editing a report so that it appears the way you prefer, save it to an intranet server. Next, open the Web page file in an IE 5 or IE 4.01+ browser. Figure B shows the PivotTable list from Figure A with the details suppressed so that only the summary field shows. You can expose the detail data for any individual salesperson by selecting the report and then clicking the expand button next to a salesperson’s name. To make the details for all salespersons show, right-click the Salesperson field label button and select Expand so that its check appears.
|An updated version of the report from Figure A viewed in a browser. The new version displays summary, instead of detail, field values.|
Figure C shows the initial report from Figure A with even more editing. One major change is the addition of a column field. To add the new column field, drag the Shippers_CompanyName field from the PivotTable Field List dialog to the report’s column drop area. After the modification, detail records group by both salesperson and shipper. Figure C also presents grand totals by salesperson and shipper. The Items column in the report is a summary field just like the Sales column that represents the sum of extended prices. I inserted the Items column into the report by adding a summary field that counts the number of salesperson records per cell. Since the Invoices query duplicates the salesperson field for each record and each record represents a line item on an invoice, this is a surrogate for counting the number of line items in a cell. The background for the Web page heading in Figure C denotes Dynamic HTML formatting—in this case, a wipe effect for the letters from left to right when the page opens in an IE 4.01+ or 5 browser. FrontPage supports this effect and several more for enhancing the appearance of Web pages containing PivotTable lists.
|An updated version of the report from Figure A that includes column as well as row fields and a second summary field.|
Figure D shows a report based on a PivotTable list with two row and two column fields. You can drag multiple field names to any drop area on a report. In the case of rows and columns, it is wise to assign the fields in hierarchical order. The row fields nest the ProductName field within the CategoryName field. Notice that the report shows the Beverages category field expanded. This depicts the products within that category with sales in 1998. The column fields nest the ShippedQuarter field within the ShippedYear field. The Year field filters out any records not from 1998. Web page designers can filter on row or column field values by clicking the Down arrow in the field name button within the row or column drop areas and selecting just the values they want to retain in the report. The report expands the Year field to reveal the quarters with sales within 1998. The NorthwindCS database contains records for just the first two quarters in 1998. I updated the caption for the ProductSales field from “ProductSales” to “Product Sales.” The format for the detail field has a currency format, as was also true in Figure C. You can assign a number format to a field by right-clicking it, choosing Property Toolbox, and selecting a format from the Number Format drop-down list in the Format section of the PivotTable Property Toolbox.
|A PivotTable list with two row and column fields. The sample report illustrates expanded fields within the row and column field hierarchies.|
Notice that the caption for the report is “dbo.My Product Sales By Year and Quarter.” The prefix dbo is a SQL Server qualifier signifying that the data source for Figure D is a SQL Server database. In fact, the database is the NorthwindCS database, which is a client/server version of the sample Northwind Jet database that ships with Office 2000. I developed a custom view named My Product Sales by Year and Quarter. It contains both year and quarter fields. This custom view computes the data source for the PivotTable list in Figure D.
Listing A reveals the SQL string for the view defining the data source for the report in Figure D. Notice that the SQL string uses the DATENAME function to compute the quarter and year field values from the ShippedDate field. The expressions for these fields are important since they translate directly into column names for the display in Figure D.
Listing A: The SQL string that generates the data source for the report in Figure DSELECT Categories.CategoryName, Products.ProductName,
[Order Details].UnitPrice *
[Order Details].Quantity *
(1 – [Order Details].Discount)/ 100) *
100) AS ProductSales,
‘Qtr ‘ + DATENAME(qq, Orders.ShippedDate)
‘Yr’ + DATENAME(yyyy, Orders.ShippedDate)
FROM Categories INNER JOIN
Products.CategoryID INNER JOIN
Orders INNER JOIN
[Order Details] ON
Orders.OrderID = [Order Details].OrderID ON
Products.ProductID = [Order Details].ProductID
WHERE (Orders.ShippedDate IS NOT NULL)
GROUP BY Categories.CategoryName,
‘Qtr ‘ + DATENAME(qq, Orders.ShippedDate),
‘Yr’ + DATENAME(yyyy, Orders.ShippedDate)
I developed the T-SQL code in Listing A graphically using the Query Design Grid, but I show the SQL statement because the grid is very wide and its image is difficult to read. (You can recover the graphical representation of the view by pasting the code from Listing A into the SQL window of a new view in an Access project connected to the NorthwindCS database.)
A PivotTable list with a chart
You can chart the report in a PivotTable list the same way you can graph the results of a spreadsheet. However, charting a PivotTable list is considerably more powerful because the PivotTable list is so flexible. For example, users can readily exchange row and column fields, filter out values, and add more fields. Web page designers can also permit users to sort the row and column fields. As a Web page designer or user makes any of these modifications to the report that a PivotTable list displays, an associated chart will instantly reflect it.
Before starting to chart a PivotTable list, make sure you have at least one total field because the Chart Control wizard recognizes only total fields when it plots data from a PivotTable list. Also make sure the fields you want in your chart appear in either the rows or columns of the report. By default, data in rows appear as chart series, and data in column fields act as categories for the chart. Of course, if the Web page designer or user wants to alter the appearance of a chart, they can readily change it by dragging field label buttons to new report positions.
After designing the initial version of your report with the PivotTable list, position your cursor on the Web page where you want to insert the chart. Do this on the Normal tab. Next, choose Insert | Component | Office Chart from the FrontPage menu. Then, reply to the wizard to specify the chart type, source, and initial format for the new chart.
Figure E displays a PivotTable list and its corresponding chart in a browser. This chart selects four employees from the NorthwindCS database and shows the sales volume that they mail by each of three shippers. Although the Web page initially opens with this chart layout, a user is not restricted to viewing data from that perspective. You can also rearrange the fields and controls on the report in the browser.
A PivotTable list control is powerful and flexible. It offers rich interactive capabilities, and it is easy to manipulate—for both Webmasters and end users. When you add a chart to a Web page containing a PivotTable list, you further heighten the impact of this powerful reporting tool. This article focuses on the manual design of Web pages containing PivotTable lists. There are many aspects to PivotTable lists and Office Web Components that this article does not address. Advanced developers will want to explore their programmability. Webmasters will want to explore the options for restricting users’ ability to modify Web pages containing PivotTable lists.
Rick Dobson, Ph.D., and his wife operate a development and training consultancy. He is the author of the best-selling book Programming Microsoft Access 2000 for Microsoft Press. Rick is a regular contributor to TechRepublic and numerous computer periodicals. In addition, he has presented training sessions and seminars on Access and Web development topics in Australia, Canada, the United Kingdom, and throughout the United States. Rick is a Microsoft Certified Professional and a Microsoft Certified Trainer. You can reach Rick at either of the two Web sites that his practice maintains (www.programmingmsaccess.com and www.cabinc.net ).
The authors and editors have taken care in preparation of the content contained herein but make no expressed or implied warranty of any kind and assume no responsibility for errors or omissions. No liability is assumed for any damages. Always have a verified backup before making any changes.