Overview

A user may wish to summarise data in a table to get a desired report. The pivot view functionality allows the user to use a table's view attributes as the rows against which data is reported.

Scenario

We have the following car data:

We want to view this data pivoting on Year, and show the data by Car:

Basic Pivot View

Solution

1. Open the table's Properties.
2. Go to the Views section.
3. Create a new View with Display Type Pivot.
4. Set the following:
   1. Name, a useful name indicative of the view's purpose.
   2. Pivot Column Source, as View Data. 
   3. View Attribute, add the attributes you require by clicking  Show Attributes and dragging in the required attributes in.
   4. Edit the attributes by double clicking:
      1. Set Role to Pivot Row for the attributes we wish to group on in the rows.
      2. Set Role to Column for the attribute you want to pivot on. Each unique value will be added as a column.
      3. Set Role to Row Value for the attribute you wish to aggregate on and set Aggregate Function as required e.g. Sum.
   5. Maximum Pivot Columns
5. This setup is illustrated below:
   1. 
6. This produces the following view:
   1. 
7. To Pivot on Car we can use the following setup, note we have removed Description as we can only pivot on one attribute:
   1. 
8. This produces:
   1. 

Pivot View with Expression

Expressions allow you to control the headers that are pivoted on. This allows you to sort the pivoted column order and also add entries where no data is provided. In the latter example, if we had no car data for "Car 4" but wished to include it in the report this is achievable using an expression.

Note for expressions

• Pivot Column Headers must begin with a letter.
• We cannot use the expression where a Pivot Column Header is numeric.
• The Pivot Column Headers must match the values of the content within them.
• Spaces are replaced with underscores, this does not need to be reflected in the column headers.

Solution

1. Open the Pivot View properties.
2. Click on the Pivot Column Source and select Expression.
3. Complete the Pivot Column Expr.:
   1. ["Car 1", "Car 2", "Car 3", "Car 4"]
4. The setup looks as follow:
   1. 
5. This setup results in:
   1. 

Pivot View Enhancements

Pivot with Pivot Row Total

This allows us to generate a total for each row in our data.

1. Open the View properties.
2. Rename the existing Quantity attribute e.g. Quantity_.
   1. This is necessary as we cant have two attributes with the same name.
3. Add Quantity again.
4. Double click the newly added Quantity:
   1. Name, QuantityByYear
   2. Role, Pivot Row Total
   3. Aggregate Function, Sum
5. Save your changes and refresh your view. THe results will be as follows:
   1. 

Pivot with Column Value Label

It is possible to Pivot on one attribute but use the values from a different attribute as the pivot column header labels.

1.  Open the View properties.
2. Set Pivot Colum Source to View Data
3. Add the Description attribute, or the attribute you wish to use as the label.
4. Double click the new attribute to open its properties:
   
   1. Set Role to Pivot Column Label
5. Save all changes and refresh your view. The results will be as follows, where the description field is now the column header:
   1.