8.1.1 PivotTable data

PivotTable data

The first step in defining a PivotTable is to set up the data in a worksheet using a row and column format that Excel calls a list or database. Have no fear; it looks just like what FoxPro developers call a database, too. There s only one difference: the first row of a list is reserved for column labels, which are used to label the PivotTable, and is used like a field name for accessing the data in the columns.

When programming a cross-tab report, a list of the unique data items in one field becomes the labels for the columns, and a list of the unique data items in another field becomes the labels for the rows. The intersection of these rows and columns reflects a summary of the numeric data, such as a total, average, count, or some other numeric function. Just like the cross-tab report, a PivotTable uses one field each for the RowFields and ColumnFields properties and summarizes the numeric data contained in the DataFields property. We ll come back to the properties later; right now, we need to set up the data. So we know we need at least three columns (and later we ll see that we can use many more).

The TasTrade database is perfect for illustrating PivotTables. We ll illustrate the PivotTable examples with a table containing a list of all the order line items. We could almost take the Order_Line_Items table as is, except that it contains a lot of codes, for which we d like the text descriptions. Listing 1 (XLData.PRG in the Developer Download files available at www.hentzenwerke.com) shows how to create the cursor, copy it to an XLS file, open the XLS file in Excel, then fit the columns so we can read it better. Figure 1 shows a portion of the resulting worksheet.

Listing 1. Creating an example cursor to use for PivotTables.

* Clean out any existing references to servers.

* This prevents memory loss to leftover instances.

RELEASE ALL LIKE o*

* For demonstration purposes, make oExcel and oBook

* available after this program executes.

PUBLIC oExcel, oBook

OPEN DATABASE (_SAMPLES + "\TasTrade\Data\TasTrade")

SELECT Customer.Company_Name, ;

Customer.Country, ;

CMONTH(Orders.Order_Date) AS Order_Month, ;

YEAR(Orders.Order_Date) AS Order_Year, ;

Category.Category_Name,;

Products.Product_Name, ;

Order_Line_Items.Quantity, ;

Order_Line_Items.Unit_Price * Order_Line_Items.Quantity ;

AS Total_Price;

FROM Orders, Order_Line_Items, Customer, Products, Category ;

WHERE Order_Line_Items.Order_ID = Orders.Order_ID ;

AND Orders.Customer_ID = Customer.Customer_ID ;

AND Order_Line_Items.Product_ID = Products.Product_ID ;

AND Products.Category_ID = Category.Category_ID ;

INTO CURSOR Pivot

LastLine = ALLTRIM(STR(_TALLY + 1 ))

COPY TO (CURDIR() + "Pivot") TYPE XL5

oBook = GETOBJECT(CURDIR() + "Pivot.XLS")

* Open the workbook, and best-fit all the columns.

WITH oBook

oExcel = .Application

.Application.Visible = .T.

.Windows[1].Activate()

.Sheets[1].Range("A1:H" + LastLine).Columns.AutoFit()

ENDWITH

Figure 1. The sample worksheet for the PivotTable examples. This worksheet contains 2,822 rows. Most columns have repeating data, and when used as a row or column field, the unique values within the column become the headings.

This worksheet has 2,822 rows, and plenty of fields to use as PivotTable rows and columns. A gentle reminder here: unlike FoxPro s tables with an unlimited number of records, Excel has a limit of 65,536 rows.

 

Copyright 2000 by Tamar E. Granor and Della Martin All Rights Reserved