Overview

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

Scenario

We have the following car data:

Image Added

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

Image Added

Basic Pivot View

1. Open the table properties's Properties.
2. Go to the Views section.
3. Create a new View with Display Type Pivot.
4. Give the View a Name. In this example the view is named Demo Pivot
5. Leave the Set the following:
   1. Name, a useful name indicative of the view's purpose.
   2. Pivot Column Source, as View Data. 
   In the
   1. View
   Attribute pane
   1. Attribute,
   insert
   1. add the attributes you require
   :
6. Image Removed
7. In our case we have the attributes already inserted, you can modify the role of an attribute in the pivot view simply by double clicking on it to open the following:
   1. Image Removed
8. In this newly opened View Attribute Configuration Panel, one
   1. by clicking

      Insert excerpt
      _attributes_show _attributes_show nopanel true

       and dragging in the required attributes in.
   2. 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.
   3. This setup is illustrated below:
   4. Image Added
9. This prodduces the following view:
10. In View Attribute Properties, you can modify the details of that attribute, namely one can modify the role it has in that 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.In the examples bellow, one will see how changing the Pivot Value impacts the way the view behaves.

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"]

while 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.