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

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. This setup is illustrated below:
   6. 
5. This prodduces the following view:
6. In View Attribute Properties, you can 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.

11. In the following example we can see how the Pivot Value is set for the "Contract Term" and we can easily see which the contract length an account has, emphasised by the True or False values.

12. To show how the Pivot View changes around the Pivot Value, I have modified the Value to pivot around, and in this case it is now pivoting around when the billing frequency, be it Monthly or just a one time bill.

If by any means we want to sort through the columns we have in here, we will not be able to do so via a Sort Order, instead we will have to change the Pivot Columns Source from "View Data" to "Expression" and now the expression will help sort through the Pivot View.

13. Clicking on the Pivot Column Source and we will change the "View Data" option to: 

14. To correctly set up the column sort the expression must point to the Pivot Column. 

15. To showcase this we want the pivot view to only display these Column Names in this particular sort order:  Handset , Internet, Voicemail, SIM_Only the expression to be written in the "Pivot Column Expr" field will look like this : ["Handset", "Internet", "Voicemail", "SIM_Only"]

16. While the Pivot View will look like in the following picture : 

Where the columns "Contract Term", "Charged", "Period", "Match Status", "Quantity" and "Owner" are fixed and the rest of the columns are sorted based on the Pivot Column and Value that we are pivoting around.