My Reports – Pivoting

Pivoting the Grid

The reporting tool in Albert allows you to build pivot tables and even pivot charts just as you would in a program like Excel. Pivoting allows you to take a column's values and turn them into columns. For example you can pivot on Country to make columns for Ireland, United Kingdom, USA etc.

Pivoting only makes sense when mixed with aggregation. If you turn a column into a pivot column, you must have at least one aggregation (value) active for the configuration to make sense. For example, if pivoting by country, you must provide something you are measuring such as 'gold medals per country'.

Pivot Mode

Pivot mode is required to be turned on for pivoting to work. You can activate Pivot Mode by selecting the toggle in the upper right hand portion of the screen. 

• 1. • When the grid is in pivot mode, the following will happen:
       • Only columns with Group, Pivot or Value active will be included in the grid.
       • Only aggregated rows will be shown, the lowest level row data will not be displayed.
       • If pivot mode is off, then adding or removing pivot columns will have no effect

  2. Example: Simple Pivot

     The example below shows a simple pivot on the Sport column using the Gold, Silver and Bronze columns for values. Columns Date and Year, although defined as columns, are not displayed in the grid as they have no group, pivot or value associated with them.

Pivot Mode vs Pivot Active

It is possible to have pivot mode turned on even though there is no pivot active on the grid. In this scenario, the grid will display the data as normal but will strip out columns that have no grouping or value active.

The example below demonstrates the difference between pivot mode and having a column with pivot active. The example has three modes of operation that can be switched between using the top buttons. The modes are as follows:

• 1 - Grouping Active: This is normal grouping. The grid groups with aggregations over Gold, Silver and Bronze. The user can drill down to the lowest level row data and columns without aggregation or group (eg Country, Year, Date and Sport) are shown.
  
  
  
• 2 - Grouping Active with Pivot Mode: This is grouping with Pivot Mode on, but without any pivot active. The data shown is identical to the first option except the grid removes access to the lowest level row data and columns without aggregation or group are not shown.
  
  
  
• 3 - Grouping Active with Pivot Mode and Pivot Active: This is grouping with Pivot Mode on AND Pivot active. Although it appears similar to the second option, there is no pivot active in the second option.
  
  
  
  • For more detailed information about pivoting inside the Reporting tool, please visit This Link

Pivot Charts

Pivot Chart allow users to chart all grouped and pivoted data from inside the grid. When pivot mode is active in the grid, the pivot chart menu item will appear in the grid's context menu.

Using Pivot Chart

• Enter Pivot Mode and select a combination of group, pivot and value columns.
• Bring up the Context Menu and select the desired chart type from the 'Pivot Chart' sub menu.
• Notice from the demonstration below that all data is charted when using the Pivot Chart. Also note that the category axis will update to reflect the expanded group categories.
• Learn more about Pivot Charts here, Reporting - Pivot Charts