Chapter 16: PivotTables and PivotCharts

---

This chapter discusses how to create and manipulate PivotTables and PivotCharts using Microsoft Visual Basic for Applications (VBA). In this chapter, you will learn about the objects associated with PivotTables and PivotCharts and how to manipulate PivotTables and associated objects programmatically.

PivotTables and PivotCharts

Worksheets are a useful tool when you are dealing with two-dimensional data that can be organized into rows and columns. However, there are many situations where your data can be viewed in multiple ways. Although you could build multiple worksheets and charts based on the data from the original worksheet, creating a new worksheet or chart for every different aspect of your data that you want to emphasize can quickly spin out of control. Fortunately, Microsoft Excel includes a feature that gives you a powerful tool to deal with this problem.

PivotTables and PivotCharts provide useful ways to look at your data from multiple perspectives. Often you can find hidden relationships between two or more fields that you might have otherwise ignored.

Introducing PivotTables

A PivotTable is a tool that lets you deal with multidimensional reports. Figure 16-1 contains a simple two-dimensional report-also known as a cross tabulation report-where you have a predefined collection of columns and rows indexing summary data.

Figure 16-1: A simple two-dimensional report contains summary data indexed by two fields.

If you want to represent a report with three fields, you need to envision a cube where the intersection of any row, column, and depth variable will produce a single summary value. To make a three-dimensional report easier to deal with, you can 'flatten' the report by cutting the cube into multiple slices along the depth axis and representing the third dimension at the top of each page of the report, as is often done when you create reports in Microsoft Access.

Visualizing a report with more than three variables is something that many humans aren't capable of doing, but it's easy to flatten a report with any number of variables. Simply choose the fields for the rows and columns for the flattened report, and display the values of the other fields at the top of each page.

The data that you would use for a PivotTable is typically organized as a series of rows known as facts, which consists of a collection of keys and measures. A measure is a numeric value such as the number of sales or the total dollar value of the sales. A key, which can be composed of one or more columns, characterizes a corresponding measure and usually answers questions such as when the sales were made or to whom the sales were made. A key field (or collection of fields) always contains a unique value for each row in a table, allowing Excel to identify the corresponding measure. In the following table, the Month, Week, Weekday, Day, and Hour fields comprise the key, while the Sales field contains a measure.

Month    Week  Weekday   Day   Hour   Sales
January  1     Mon       1     9      147
January  1     Tue       2     9      161
January  1     Wed       3     9      182
January  1     Thu       4     9      201
January  1     Fri       5     9      158
January  1     Sat       6     9      190
January  1     Sun       7     9      243
January  2     Mon       8     9      147
January  2     Tue       9     9      161
January  2     Wed       10    9      182
January  2     Thu       11    9      201
January  2     Fri       12    9      685
 

Excel uses individual transactions such as these to create the facts that are displayed in the PivotTable. This process involves two steps: extracting the keys from the information in the transactions and identifying the corresponding measure.

A PivotTable implements multidimensional reports by combining the facts in different ways. You can drag and drop key fields from the PivotTable Field List to the page area to determine which specific page is displayed. You can also drag and drop key fields onto the column and row areas to determine which column and rows will be displayed. Measures should be dragged only into the Data area because these values will be computed based on the other fields. These actions result in a PivotTable that looks like Figure 16-2.

Figure 16-2: A PivotTable allows a user to combine facts in many different ways.

Creating a PivotTable with the PivotTable Wizard

Although you can manually create a PivotTable, the PivotTable wizard really simplifies the process. To create a PivotTable with the PivotTable wizard, follow these steps:

1. Choose Data, PivotTable and PivotChart Report from the Excel main menu.

2. On the first step of the wizard, choose the location of your data and then select PivotTable.

   

3. The second step of the wizard prompts you to select the source data for the PivotTable. You can either enter a range in the Range box or press the button at the end of the box to collapse the dialog box, allowing you to select a range in any of the currently open workbooks. You can press the Browse button if you want to open a new workbook.

   

4. In the last step of the wizard, you choose the location where the wizard will place the PivotTable. You can select a location on the current worksheet, or the wizard will create a new worksheet for you. Pressing the Layout button displays a dialog box that allows you drag and drop the various fields for the PivotTable's initial configuration.

   

5. If you press the Options button in the last step of the wizard, the wizard will display the PivotTable Options dialog box. You can select from a wide range of options, as shown here.

   

6. Press the Finish button to create the PivotTable.

When the wizard is finished, you'll see a PivotTable similar to the one shown in Figure 16-3.

Figure 16-3: A PivotTable contains summary information based on the underlying data supplied.

Introducing PivotCharts

A PivotChart is simply a graphical representation of a PivotTable. (See Figure 16-4.) In fact, a PivotChart is generally created from the data of an existing PivotTable. Just as with a PivotTable, you can drag fields into the appropriate areas of a PivotChart.

Figure 16-4: A PivotChart contains drag and drop areas similar to a PivotTable.

The chart's x-axis and y-axis correspond to the row and column areas in the PivotTable, whereas the page area is located near the top of the chart. More technically, these areas are known as the category and series areas. The page area and the data area are carried over directly from the PivotTable.

After you drag and drop the fields into the various parts of the PivotChart, you can change the type of chart by choosing Chart, Chart Type from the Excel main menu. You can also use most of Excel's chart-formatting tools to customize the chart to fit your needs.

Creating a PivotChart with the PivotTable Wizard

The same wizard you use to create a PivotTable can also create a PivotChart. Under the covers, the wizard creates a new PivotTable and then uses that PivotTable to create a PivotChart. If you already have a PivotTable, simply select any cell within the one you wish to use and choose Insert, Chart from the Excel main menu. A resulting chart is shown in Figure 16-5.

Figure 16-5: A PivotChart is a graphical representation of a PivotTable.

Online Analytical Processing (OLAP) Issues

PivotTable data can come from a number of sources besides a worksheet. Typically, the data is imported into Excel, while the information necessary to get a fresh copy is also preserved. This arrangement allows the PivotTable user to refresh the data easily.

The one big limitation is that an Excel PivotTable isn't capable of managing large volumes of data directly. This limitation should be expected when you consider that a worksheet is limited to 65,536 rows. However, Excel has a facility that lets a PivotTable work with a special type of external database server known as an Online Analytical Processing (OLAP) server.

Note 

SQL Server Standard Edition and Enterprise Edition include a tool called Analysis Services, which provides the OLAP database facilities that can be accessed from Excel.

With an OLAP database, much of the processing required to summarize data is shifted from the local computer to the OLAP server. An OLAP server is capable of dealing with large volumes of data and is designed to provide summaries of its data quickly and efficiently by precomputing many useful values.

Note 

Because Excel relies on the OLAP server to precompute the data, each time you change the layout of a PivotTable, Excel will request a fresh copy of the data from the OLAP server. This could cause unexpected delays as the data is transmitted from the OLAP server to Excel.

Because Excel uses the summary data directly, you can't change the summary functions for data fields. Another side effect of using the summary data is that you might not be able to access the detail data. This information is determined by the design of the OLAP database.

Warning 

PivotTables based on OLAP data sources behave somewhat differently than those created using non-OLAP data sources. This is due to the fact that OLAP servers return data that's already summarized, so different objects are used internally to store the summary data and to interact with the OLAP server. The rest of this chapter assumes that you are working with non-OLAP data sources.

---