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.
      4. View Attribute Properties, modify the details of that attribute, namely the role it has in the Pivot, the way it aggregates and more importantly the way the Label is used, as it can be used as an Expression, which will be explained further on in the example.
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.