Chapter 11: Pivot Tables

Chapter 11: Pivot Tables Transcript

A pivot table is another means of profiling data in a tabular format. In this example, I’m going to create a table showing the different payment types used for each product code as well as the sales totals.

Start by opening your source file, which is the Sales Transactions database. On the Analysis tab, in the Categorize group, select Pivot Table.

The first thing you will be asked is to provide a name. I’m calling mine Sales Transactions Pivot Table. When you’re done, click OK.

Start by clicking Pay Type and dragging it to the row header where it says Drop Row Fields Here. The creation of a pivot table in IDEA is graphic, so all you need to do is select the field you want from the field list box and drag it to the appropriate location on the pivot table.

Next, click Product Code, and drag it to where it says Drop Column Fields here. Now we drop the data into the middle. Since I want to see the sales totals, click the amount and drop it in the middle where it says Drop Data Items Here.

Notice that you can see a subtotal for each payment type in the far right column, and the totals for each product code along the bottom. The bottom right square is the total sales amount of 6.4 million which is what I expect to see here.

By default, the values in the data grid are totals, but this can be changed by right-clicking in the grid to open the Pivot Table Field dialog box. I’m going to change my display from sum, to count. To view the records which make up a specific group, highlight the cell – you can pick either a data cell or a total cell for this and click the VIEW button on the Pivot Table toolbar. For example, these are the four records for Product code 2, paid for by Amex. These are the 4 records for product code 3 paid for with Cash.

You can also have more than one field in a row. If it isn’t already open, open up the Pivot Table Field List and drag SALES_ID to the column area, dropping it just to the left of the Prod code field. This shows me the same information – sales totals by payment type but is now further broken down by Salesrep ID. I can filter my results by either the row or column headers by clicking the drop-down arrow beside the header. So for example, say I want to see only the results pertaining to Product Code 2 and 4, I’d click the first checkbox. This will clear all the boxes and then I’ll select the items I want to see – product codes 2 and 4. The focus is still on the Sales Reps but each sales rep’s detail can be collapsed by clicking the + or – box. To collapse them all, right-click on the Sales ID header and select Collapse All.

A pivot table is a type of result output so, like the stratification result, it does not generate a child database, but rather is a view of the parent data. To return to the regular data view, click Data in the properties window.

When you’re finished, close all open databases.