PivotTables and PivotCharts are powerful tools for summarizing detailed data stored in Jet or SQL Server databases. Like crosstab queries, PivotTables present data generated by aggregate queries in a spreadsheet-like format that's familiar to all accounting and management personnel. PivotTable views deliver to Access users the benefits of Excel worksheets without having to launch Excel to manipulate the data. PivotChart views automatically render PivotTable views as line, bar, or area charts. PivotTables and PivotCharts accomplish the primary objective of decision-support front ends converting online transaction processing (OLTP) data to usable information. Office 2000 introduced the Microsoft Office PivotTable 9.0 ActiveX control a lightweight version of the Excel 2000 PivotTable and the Chart 9.0 control, a substitute for the Microsoft Graph OLE server application. Microsoft designed these two controls primarily for use with data access pages (DAP) and intranet applications, but you also could insert them in Access forms or reports. The original PivotTable and Chart controls were members of the Office 2000 Web Components (OWC) 9.0, which required an Office 2000 license and Internet Explorer (IE) 5+ for users to view them. These licensing restrictions made Access 2000's DAP unsuitable for public consumption via the Internet. Other deficiencies in Access 2000's DAP implementation limited their deployment on private intranets. Note
PivotTables replace embedded Excel PivotTables, and PivotCharts supplement or replace Access charts embedded by the Chart Wizard. Excel PivotTables require a local copy of Excel.exe and the Chart Wizard needs Office 2002's Graph.exe to act as Object Linking and Embedding (OLE) 2+ servers. Internet protocols and Web browsers don't support OLE. If you subscribe to Microsoft's Internet-centric view of the world, original Excel PivotTables and OLE-based charts are obsolete. In the real world, Office PivotTables outperform their earlier Excel counterparts, but conventional Access charts you create with the Chart Wizard have several features that PivotCharts don't offer.
To add charts or graphs to forms with the Chart Wizard, see "Using the Chart Wizard to Create an Unlinked Graph," p. 704.
The behavior of the PivotTable and PivotChart views of forms is identical to those of tables or queries. You can use the AutoForm: PivotTable and AutoForm: PivotChart Wizards to create these views of forms from a table or query you specify. Northwind.mdb's sample Sales Analysis form, for example, alternately displays PivotChart and PivotTable form views of a query in a subform. Forms, reports, and DAP can contain OWC PivotTable and PivotCharts as conventional ActiveX control objects. You can set the properties of these controls in forms and reports with Visual Basic for Applications (VBA). You use VBScript or ECMAScript (JavaScript) to program PivotTable and PivotChart controls in DAP. You can't, however, program these two controls in PivotTable or PivotChart views of tables, queries, or forms. Note The limitations of PivotTable and PivotChart views might cause you to wonder why this chapter is in Part III, "Transforming Data with Queries and PivotTables," rather than in Part IV, "Designing Forms and Reports," or Part VI, "Publishing Data to Intranets and the Internet." The reason is that well-designed queries usually based on multiple tables are fundamental to generating meaningful data for presentation in PivotTables and PivotCharts. The query and view design techniques you learn in this chapter apply equally to PivotTables and PivotCharts contained in conventional Access and ADP forms, as well as in DAP. |