Pivot Tables

While we are assuming that the reader is familiar with the basics of Excel, it probably would not hurt to review the concept of a pivot table (or PivotTable) quickly.

PivotTables are one of the most powerful features in Excel. They are designed to accomplish three main tasks:

• Import external data

• Aggregate data; for example, sum, count, or average the data

• Display the data in interesting ways

PivotTables can use data from external sources, as well as from one or more Excel tables. For instance, the data for a PivotTable can come from an Access database. However, setting up Excel to import external data requires that the appropriate data source drivers be installed on the user's computer. Moreover, there are significant limitations on Excel's ability to import data through PivotTables. For instance, all strings are limited to a length of 255 characters, which makes using SQL to define a data source much more difficult.

All in all, importing data using a PivotTable can be problematic. Furthermore, we always have the option of importing the required data directly to an Excel worksheet (using a variety of more sophisticated methods, such as DAO and the GetRows method) and then creating the PivotTable from the worksheet. Accordingly, we will restrict our discussion to using Excel data as the PivotTable source.

Table 20-1, which represents sales from a fictitious fast food company that has both company and franchise stores, shows the first half of the data that we will use to build our pivot table. The actual source table is an Excel worksheet that contains twice the number of rows as Table 20-1, the additional rows being the analogous data for the year 1997. (Thus, the first column in the remainder of the table contains the year 1997.)

Table 20-1. Source Data for PivotTable (for 1998)

Year

Period

Store Code

Store City

Store Type

Transactions

Sales

1998

1

BO-1

BOSTON

Company

3881

$6,248.00

1998

1

BO-2

BOSTON

Company

3789

$5,722.00

1998

1

BO-3

BOSTON

Company

3877

$6,278.00

1998

1

BO-4

BOSTON

Company

3862

$6,123.00

1998

1

BO-5

BOSTON

Franchise

4013

$6,861.00

1998

1

BO-6

BOSTON

Franchise

3620

$5,039.00

1998

2

BO-1

BOSTON

Company

3948

$6,468.00

1998

2

BO-2

BOSTON

Company

3878

$6,301.00

1998

2

BO-3

BOSTON

Company

3911

$6,390.00

1998

2

BO-4

BOSTON

Company

3926

$6,438.00

1998

2

BO-5

BOSTON

Franchise

3990

$6,767.00

1998

2

BO-6

BOSTON

Franchise

3615

$5,091.00

1998

3

BO-1

BOSTON

Company

3936

$6,307.00

1998

3

BO-2

BOSTON

Company

3857

$6,153.00

1998

3

BO-3

BOSTON

Company

3898

$6,319.00

1998

3

BO-4

BOSTON

Company

3949

$6,453.00

1998

3

BO-5

BOSTON

Franchise

3617

$5,052.00

1998

3

BO-6

BOSTON

Franchise

3624

$5,111.00

1998

4

BO-1

BOSTON

Company

3853

$6,021.00

1998

4

BO-2

BOSTON

Company

3891

$6,333.00

1998

4

BO-3

BOSTON

Company

3892

$6,289.00

1998

4

BO-4

BOSTON

Company

3966

$6,571.00

1998

4

BO-5

BOSTON

Franchise

3595

$4,945.00

1998

4

BO-6

BOSTON

Franchise

3611

$5,051.00

1998

1

LA-1

LOS ANGELES

Franchise

8259

$29,267.00

1998

1

LA-2

LOS ANGELES

Company

9140

$31,947.00

1998

1

LA-3

LOS ANGELES

Company

9727

$35,405.00

1998

1

LA-4

LOS ANGELES

Franchise

9494

$33,830.00

1998

1

LA-5

LOS ANGELES

Franchise

10644

$39,971.00

1998

1

LA-6

LOS ANGELES

Franchise

10649

$40,077.00

1998

2

LA-1

LOS ANGELES

Franchise

9066

$32,595.00

1998

2

LA-2

LOS ANGELES

Company

9789

$35,217.00

1998

2

LA-3

LOS ANGELES

Company

9814

$35,455.00

1998

2

LA-4

LOS ANGELES

Franchise

9917

$35,926.00

1998

2

LA-5

LOS ANGELES

Franchise

10617

$39,424.00

1998

2

LA-6

LOS ANGELES

Franchise

10190

$38,387.00

1998

3

LA-1

LOS ANGELES

Franchise

9531

$33,966.00

1998

3

LA-2

LOS ANGELES

Company

9698

$34,419.00

1998

3

LA-3

LOS ANGELES

Company

9771

$34,494.00

1998

3

LA-4

LOS ANGELES

Franchise

10232

$37,315.00

1998

3

LA-5

LOS ANGELES

Franchise

10561

$39,141.00

1998

3

LA-6

LOS ANGELES

Franchise

10924

$41,938.00

1998

4

LA-1

LOS ANGELES

Franchise

9310

$33,202.00

1998

4

LA-2

LOS ANGELES

Company

9496

$33,910.00

1998

4

LA-3

LOS ANGELES

Company

9596

$34,500.00

1998

4

LA-4

LOS ANGELES

Franchise

10050

$37,274.00

1998

4

LA-5

LOS ANGELES

Franchise

10440

$38,304.00

1998

4

LA-6

LOS ANGELES

Franchise

10778

$40,965.00

1998

1

NY-1

NEW YORK

Company

6390

$19,890.00

1998

1

NY-2

NEW YORK

Franchise

7016

$22,229.00

1998

1

NY-3

NEW YORK

Franchise

7293

$24,077.00

1998

1

NY-4

NEW YORK

Company

7037

$22,704.00

1998

1

NY-5

NEW YORK

Franchise

7815

$26,962.00

1998

1

NY-6

NEW YORK

Franchise

6935

$22,925.00

1998

2

NY-1

NEW YORK

Company

6954

$22,389.00

1998

2

NY-2

NEW YORK

Franchise

7531

$25,324.00

1998

2

NY-3

NEW YORK

Franchise

7486

$24,753.00

1998

2

NY-4

NEW YORK

Company

7285

$24,112.00

1998

2

NY-5

NEW YORK

Franchise

7749

$26,325.00

1998

2

NY-6

NEW YORK

Franchise

6881

$23,123.00

1998

3

NY-1

NEW YORK

Company

7256

$23,330.00

1998

3

NY-2

NEW YORK

Franchise

7330

$24,258.00

1998

3

NY-3

NEW YORK

Franchise

7212

$23,386.00

1998

3

NY-4

NEW YORK

Company

7480

$24,619.00

1998

3

NY-5

NEW YORK

Franchise

6771

$22,189.00

1998

3

NY-6

NEW YORK

Franchise

6954

$23,188.00

1998

4

NY-1

NEW YORK

Company

7086

$22,703.00

1998

4

NY-2

NEW YORK

Franchise

7275

$24,245.00

1998

4

NY-3

NEW YORK

Franchise

7121

$23,025.00

1998

4

NY-4

NEW YORK

Company

7562

$25,329.00

1998

4

NY-5

NEW YORK

Franchise

6569

$20,845.00

1998

4

NY-6

NEW YORK

Franchise

6973

$23,220.00

The Period column in Table 20-1 is the time period. For simplicity, we consider only four time periods. The Store Code column gives the store code, used to uniquely identify a store. The Store City gives the city in which the store is located. The Store Type column indicates whether the store is owned by the company or is franchised. The Transactions column gives the number of transactions for that time period. The Sales column gives the total sales for that store during that period.

Note that there is one, and only one, row for each time period/store code. (In database language, the time period/store code forms a key for the data.)

Our goal is to create a PivotTable from the data in Table 20-1. Of course, before creating a PivotTable, we need to identify the type of aggregate data in which we are interested. Clearly, we want total sales and transaction counts. The question is: "Over what groupings?"

The best approach is first to identify the most refined (or smallest) grouping for the aggregate data. In this case, it is store type/store location/time period. For example, we want the total sales for all company stores in New York during period 1.

In addition, we will want aggregates for larger groupings—for example, total sales for all company stores in New York over all periods and total sales for New York.

Finally, we want separate totals for the years 1998 and 1997.

0 0

Post a comment