Versions Compared

Old Version 5

changes.mady.by.user Fiona Sargeant

Saved on

compared with

New Version Current

changes.mady.by.user Anthony George

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

Insert excerpt
_Banners
_Banners
nameanalysis
nopaneltrue

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:

Image Added

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

Image Added

Basic Pivot View

Solution

  1. Open the

...

  1. table's Properties.
  2. Go to the Views

...

  1. section.
  2. Create a new

...

  1. View with Display Type Pivot.

...

  1. Set the following:
    1. Name, a useful name indicative of the view's purpose.
    2. Pivot Column Source, as

...

    1. View Data

...

    1. . 

...

    1. View

...

    1. Attribute,

...

    1. add the attributes you require

...

Image Removed

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 an attribute and the following will appear :

Image Removed

In this newly opened View Attribute Configuration Panel, one can modify the details of that attribute, namely one can modify the role it has in that Pivot, the way it aggregates and more importantly the way the Label is used, as it can be used as an Expression, which I will explain further on, in the example.

In the examples bellow, one will see how changing the Pivot Value impacts the way the view behaves.

Image Removed

In this 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.

Image Removed

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 

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

Image Removed

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

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

while the Pivot View will look like in the following picture : 

Image Removed

...

    1. by clicking
      Insert excerpt
      _attributes_show
      _attributes_show
      nopaneltrue
       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. Maximum Pivot Columns
  2. This setup is illustrated below:
    1. 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


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. Image Added

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. Image Added