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

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.

Image Removed 

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

Image Removed

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

Image Removed

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

Image Removed

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:In
   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

      Insert excerpt
      _attributes_show _attributes_show nopanel true

       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. Image Removed
5. This prodduces the following view:
6. 1. 1. View Attribute Properties,
   you can
   1. 1. 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.

1. This setup is illustrated below:
2. Image Added
3. This produces the following view:
   1. Image Added
4. To Pivot on Car we can use the following setup, note we have removed Description as we can only pivot on one attribute:
   1. Image Added
5. This produces:
   1. Image Added

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. Image Added
5. This setup results in:
   1. Image Added