Understanding the Role of PivotTables and PivotCharts

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

graphics/access_2002_new_icon.gif

Office XP changed the licensing terms for its updated OWC 10; the new terms also apply to Access 2003. Users of runtime Access 2003 applications and DAP who don't have Office 2003 licenses automatically download the runtime version of OWC 11 from the Microsoft Web site. Using the runtime version of OWC, however, limits user modification of the PivotTable view. Internet users still need IE 5+ to view PivotTables in DAP, but IE's increasing share of the browser market makes this a less onerous problem. Chapter 24, "Designing and Deploying Data Access Pages," covers the ramifications of read-only DAP for users without OWC licenses.


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.


graphics/2002_icon.gif

Access 2002 added two new views to tables, queries, and forms: PivotTable and PivotChart. These views are available in conventional (Jet) Access applications and Access data projects (ADP). PivotTables and PivotCharts are interdependent; when you design a PivotChart view, you create a corresponding PivotTable view, and vice-versa. You can't restrict tables and queries to specific views such as PivotTable, PivotChart, or both but you can set the default view. You can set the default view and limit views of forms to include or exclude Pivot... views.

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.




Special Edition Using Microsoft Office Access 2003
Special Edition Using Microsoft Office Access 2003
ISBN: 0789729520
EAN: 2147483647
Year: 2005
Pages: 417

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net