Pivot tables and grids

Now we come to the computer part of the whole idea. A pivot table is a two-dimensional representation of a cross tab cube. There are many ways of arriving at a 2D representation of higher-dimensional data - you can take a slice, you can sum up across a category (aggregate) or you can adopt a tree like diagram approach to displaying the data. All of these fit under the general title of "pivot table". For example you could draw a 2D table from the 3D data above by simply looking at all of the stores in the Northern sector - then you only have Store and Type to tabulate. Instead of fixing the Region to be "N" you could sum across all of its categories to produce a total North plus South profit - once again you would only have Store and Type to tabulate in a 2D table. You can use a range of different aggregation functions to summarise a set of categories that you are not going to plot.

For a slice the rule no matter how many dimensions you have is simple: select the two sets of categories you are going to tabulate on and assign a fixed value to the rest.

For example, tabulate on Store and Type and set Region to N.

For an aggregation the rule is: select two sets of categories you are going to tabulate on and apply the aggregation function to the values generated by allowing the other categories to vary.

For example, tabulate on Store and Type and sum across all values of region.

The third method of displaying a pivot table depends on there being lots of values which don't occur in the hypercube. For example in the case of Region a store can only be in one region so is Store=A and Region=N then Store A can't have a value in the cells Store=A and Region=S.

To display this sort of data you simply pick two categories to be tabulated and then add columns for the sub categories and fill in the data where appropriate. For example, if we tabulate on Store and Type and use Region as the sub category the result is:

You can see that Region category repeats for each of the major column categories, i.e. for each store there is an N and S sub-category only one of which has data in this case. If you look at this sort of table you can see that the rows form a tree structure - A splits into N and S, B splits into N and S and so on. In some cases you can simplify things by omitting the empty branches of the tree.

You can also mix approaches; you can fix some sub-categories and aggregate across others while plotting a tree diagram on the remainder.

There is one more idea basic to a pivot table and this is the idea of pivoting. This simply refers to the way that you can select a different view of the data simply by selecting which of the two categories are use for tabulation and hence which of the remaining categories are treated as subcategories. In most case the user can select the tabulation categories interactively and watch the table "pivot" to show a new 2D view. Some users refer to the swapping of the rows and columns in a table as a pivot.

Its all very clever and very fun.

You can play with a pivot table, sorry that should be explore the data with a pivot table for many happy hours.

Now it's time to see how to implement a pivot table in your application

PivotGrid

The control that we are going to use to demonstrate how Pivot Tables can be implemented in code is the Infragistics PivotGrid.

It is assumed that you have the Infragistics Data Visualization components installed and a copy of Visual Studio all setup and ready to go. You can, of course, try out the project using a free trial download of the NetAdvantage for WPF Data Visualization components.

In most cases the PivotGrid would work with a pre-constructed data cube taken from say SQL server - a so called OLAP cube. In this case everything is pre-defined and you simply read the cube in and start working with it. In this sense it is actually simpler. However, going through the details of how to extract an OLAP cube from a SQL database is another story....

As the control can work with flat data from a variety of sources including an Excel spreadsheet for reasons of simplicity and practicality let' work with the simple profit spreadsheet that we have been developing as part of the explanation of what a pivot table is.