Chapter 7. Pivot Mode of the Dynamic List

In the previous chapter (Dynamic Lists) we explored the various possibilities offered by the Dynamic List. In the following chapter we want to present a whole new mode of the Dynamic List offering further possibilities for ad-hoc analysis of data, new visualizations and charting: the Pivot mode of the Dynamic List.

To switch to Pivot mode choose the Pivot option from the toolbar and acknowledge the subsequent check. This will trigger a reload of the Dynamic List now in Pivot mode. The effect is that the aspects list configuration and preview have been replaced by Dimensions/Measures and Pivot/Preview. The aspects parameters, computed columns and pre filter remain visible and are identical to before.

The Pivot mode can be regarded as an extensive form of the aggregation function and is best explained with an example. For this we consider a sample data set consisting of an opinion poll on the quality of certain products where persons were asked to rate a given product on a scale from 1 to 5. The following table gives the collected data, i.e., the data that we assume is underlying the Dynamic List.

When using the standard aggregation functionality of the Dynamic List (see Section 6.8.) you could aggregate the given data, for example, by choosing the product category and product as grouping attributes and take the average over the rating. This would give you the following result:

In contrast to this simple form of aggregation, the Pivot mode offers many additional features to, for example, quickly change the level of aggregation, display the result as a cross tab or diagram, add totals, drill through to the underlying data rows and much more. As an example you could have the same example displayed as a cross tab that additionally group the data by the sex of the interviewed person:

Besides displaying aggregation results in the form of cross tabs the Pivot mode also allows to easily change the level of aggregation, for example, to change the level the level of product category using a single mouse click.

Working with the Pivot mode is usually done in two steps. After changing from the dynamic list to the Pivot mode you first need to define the attributes you want to work with in the dimensions/measures aspect and define whether those attributes are grouping attributes (dimensions) or aggregation attributes (measures). For this, use from the dynamic list known column selection dialog. The selected columns / attributes are then displayed in a list.

By default, the attribute's are associated with their technical name, or if available, a preset name. To rename an attribute, click on the cell column name and assign a new name. This is identical to changing names in the normal mode of the Dynamic List.

To define an attribute as an aggregation measure (in the above example this would be the attribute rating) assign an aggregation function for the attribute. You can choose from the same aggregation functions as in the normal mode of the dynamic list.

All attributes that are not assigned an aggregation function are regarded as a grouping characteristics. Sometimes grouping characteristics are aligned along a common dimension. In the above example this is the case for Product Category and Product which both classify products from a coarser scale (category) to a more detailed scale (product). Other examples are geographic features (e.g., continent, country, city) or temporal characteristics (year, month, day). To group attributes along one dimension, assign a common dimension name in cell dimension. For example, assign the dimension name Product for the features Product Category and Product. In such a case, the order of the attributes is important and they should be sorted from coarse to detailed. In the given example the product category should thus be before the product.

Once you have your dimensions and measures configured, you can switch to the aspect Pivot/preview where all further configuration takes place. Note that the aspects computed columns and pre filter are identical to the dynamic list. Pre filters restrict the underlying set of data and computed columns can serve as additional attributes (both as measures or dimensions).

In the next chapter The Analysis View (Chapter 8.) we present the Pivot/Preview aspect. The analysis view is a shared component by the Pivot mode of the dynamic list and OLAP reports (see chapter 4.) and thus discussed jointly in Chapter 8.