Description of a pivot table view

This overview defines a pivot table view and gives an example.

A pivot table is a powerful data summarization tool that can automatically sort, count, and sum up data stored in tables and display the summarized data. Pivot tables are useful to quickly create crosstabs (a process or function that combines and/or summarizes data from one or more sources into a concise format for analysis or reporting) to display the joint distribution of two or more variables.

Typically, with a pivot table the user sets up and changes the data summary's structure by dragging and dropping fields graphically. This "rotation" or pivoting of the summary table gives the concept its name.

Three key reasons for organizing data into a pivot table are:

• To summarize the data contained in a lengthy list into a compact format.

• To find relationships within the data that are otherwise hard to see because of the amount of detail.

• To organize the data into a format that’s easy to read.

For typical data entry and storage, data is usually flat, meaning that it consists of only columns and rows, such as this example of an ODM input table showing marketing data from four different sales campaigns:

While there is a lot of information stored in such data, it can be difficult to gather the specific information you want. For example, what if you wanted to see in this table resultingSales and profit figures organized by sales campaign? You could sort the table on the campaign column, but what if you then wanted to see the figures by quarter? The information that defines which months fall into which fiscal year quarter is not even contained in the table itself; it is contained in another table called months:

Pivot tables can link the Application Data Model data from a base table to one or more other tables, as long as there is a foreign key relationship between one of the fields in your base table and the second table. In this case, the month_name field in the marketingResults table is a foreign key of the name field in the months table. This means that you can access the quarter information in that second table and use it in the pivot table.

A pivot table can help you quickly summarize the flat data, giving it depth, and display the information you want laid out as you want it. For example, this is a pivot table created from the example discussed previously:

This pivot table view gives you more useful information than the previous flat table view.

A pivot table usually consists of row, column, and data fields. In this example, the rows are organized by Month Name and City. The columns are organized by Campaign, and the data are the Resulting Sales and Profit figures for each campaign. These fields were dragged onto the Pivot Grid from the Field List area to create the layout of the table.

Pivot tables also allow you to automatically display totals by rows, columns, or both. Note that in this example column totals have been generated for each month and a Grand Total field sums each column in the last table row.

The Quarter drop-down list in the Filter Fields area allows you to choose which fiscal year quarter you want to display data for. For example, this is what the table looks like with only the first quarter selected in that drop-down list:

The Campaign drop-down list allows you to choose which of the campaigns you want to display data for. For example, this is the same table with only the Local Ads and Radio Spot campaigns selected:

Finally, if the developers of the ODM application have chosen the option that allows users to modify the pivot table, the users can change its layout by dragging and dropping fields from the Field List area and adding them to the Pivot Grid display. Or, they can drag fields from the Pivot Grid back to the Field List area, and remove them from the display. The following example shows the same pivot table with the Cost field added to the data being displayed for each campaign:

Pivot tables in ODM can be modified by the users to their specifications (as long as the option to rearrange them has been selected by the developers), and these changes can be saved along with their scenarios. These changes are saved by user, and do not affect the default layout of the pivot table for other users.