8.1 PivotTables | Microsoft Office Automation with Visual FoxPro

PivotTables

PivotTables are cool. They re cross-tabs on steroids, created from a simple list of data that contains the cross-tab categories in the data within each column the same list you d have to SCAN and conditionally set a slew of variables, or write a complicated SQL statement to build a cross-tab. VFP does have Cross-Tab and PivotTable Wizards, but they are much more limited than Excel s engine.

On the surface, simple PivotTables can cross-tab data, such as totaling the quantity of items for each product category sold in each month. PivotTables sound even better when you find that they can summarize multiple fields for the cross-tabs summarize the total items sold and the total sales price by each product by month sold. They ll also do pages summarize the total items and sales price by each product by month sold, showing a "page" for each year. You can change your mind, too: instead of showing the totals for each product by month, you can change one property to show the products sold by the buyer s country. If that weren t cool enough, it s interactive for the user you pass Excel the data and get the PivotTable set up, and your users can slice and dice the data to their hearts content. Less code for the developer, more features for the user it doesn t get any better than this!

PivotTables are so cool that VFP 6.0 has a PivotTable Wizard but it requires Excel and Microsoft Query to be installed before it can be used. We ve found Query to be temperamental in terms of programmatically ensuring that it is properly installed and configured on a user s machine; if your application can t verify that Query s going to work, your users can (and will) get nasty error messages, prompting them to make irate tech support calls. We can solve this whole problem, though, by putting the data into Excel directly from FoxPro using Automation rather than Query, then accessing Excel directly to create and manipulate a PivotTable.

Excel has several sources of data for PivotTables: a range of data in a worksheet, several ranges that the PivotTable will consolidate, another PivotTable report, or an external data source. The external data source is accessed through On-Line Analytical Processing (OLAP), ODBC, ADO, or DAO technologies, depending on the data source. Since it s so easy to COPY TO TYPE XL5 and automate Excel to open the file generated by FoxPro as a worksheet, the only data source for PivotTables that we ll cover is the range in a worksheet. We won t attempt to cover the complexities of automating OLAP, ODBC, ADO, or DAO through Excel. While it is technically feasible to use these approaches (we have successfully implemented some of these technologies in our own apps), detailing the intricacies of these complex technologies is really a separate subject from the focus of this book.