Skip to content

Pivot CCH® Tagetik

Introduction

CCH® Tagetik Pivot tables are dynamic tables created using CCH® Tagetik Intelligent Analytics.

This type of table allows you to easily analyse multidimensional data from a Reporting View, by organising them in rows and columns, similar to Excel Pivots.

Unlike standard Excel Pivots, CCH® TagetikTagetik Pivots are integrated with the company's data model and support advanced functionalities. Specifically, through the Pivot tool, data can be extracted from the Intelligent Analytics platform by specifying the measures to be analysed, aggregating them by dimension and displaying them with the required layout.

CCH® Tagetik Pivots consist of a dedicated Excel formula (TGK.PIVOT) that uses the SPILL mechanism to automatically return the calculation results to the worksheet and supports the main Excel operations:

  • copy and paste pivot, which creates a new independent pivot;
  • cut & paste or drag & drop, which allow the pivot to be moved while retaining its metadata;
  • duplication of a sheet within the workbook.

In all these cases, the pivots continue to function correctly once repositioned or duplicated.

Pivot Wizard

The Pivot wizard is the main interface for creating and editing CCH® Tagetik Pivot tables and consists of three main sections:

  • Source: for selecting the data source.
  • Filters: for defining dimensional filters to be applied globally.
  • Values, Rows & Columns:
  • Values: for selecting the measures to be analyzed.
  • Selected Values: List of Selected Values.
  • Dimensions: list of available dimensions with their hierarchies and levels.
  • Rows / Columns: Containers for arranging hierarchy levels in rows or columns.

Filters definition

It is possible to configure, for each Pivot, one or more dimensional filters that will be applied during data extraction.

The filter can be defined for each dimension by selecting individual elements directly from the panel displaying the entire hierarchical structure of the dimension. Alternatively, the filter can be set by referencing a cell, by selecting a cell that contains an Excel Data Type.

Property

It is possible to display and edit the properties of hierarchy levels and measures, placed in rows/columns and in Selected Values respectively.

Change sign

This property is only available for measures in Selected Values and allows you to define how the sign of the displayed values is to be handled.

Available options

  • None The value is displayed without any sign change.
  • Invert

The sign of the value is reversed. - Based on account

The sign of the value is based on the account nature.

Default value

  • For financial reporting views: Based on account
  • For analytical reporting views: None

Display options

This property defines how elements are displayed in the report.

Available options

  • Code
  • Description
  • Code and Description

Default value:

  • Description

Order

The Sorting property allows you to define the elements sorting criteria; it is only available for elements in the Columns and Rows containers

Sorting by

  • Code The elements are sorted in alphanumerical order.
  • Amounts

Elements are sorted based on the sum of the associated amounts.

Sort type

  • Ascending
  • Descending

NOTE: The available properties and their default values may vary depending on the type of reporting view and the selected item.

Management and display of hierarchy levels

When a level of the dimensional hierarchy is placed in Rows or Columns in the Pivot, the display in the Excel table is based on a multilevel expansion logic, similar but not identical to that of standard Excel Pivots.

  • Each selected level of the hierarchy is represented as a separate header level in the table.
  • Layers are nested in the same order as they were added to the container (the first element corresponds to the outermost layer).
  • The resulting table shows the Cartesian product of the contributing elements for each selected level, generating an expanded tree structure.

Measures management and display

Pivots support the selection of one or more measures from the Reporting View and the behaviour of the table varies according to the number of measures selected.

When only one measure is selected:

  • If no dimensions are configured in a row or column, the description will be given in the row or column header where there are dimensions;
  • If dimensions have been configured in both rows and columns, the description of the measure will appear in the first cell in the top left-hand corner.

When more than one measure is selected:

  • A placeholder element called [Selected Values] is inserted;
  • This element may be placed in rows or columns;
  • Each selected measure will be represented as a separate header in the table.

Automatic formatting

Two pivot formatting modes are available:

From the Settings menu, the chosen styles are applied to all pivots in the workbook, the first time the sheet or individual pivot is updated.

From the pivot wizard, in the Layout section, by enabling Customize Style.

In this case, styles are applied upon refresh only to the current pivot, overriding those defined in the global settings

From the Settings menu, it is also possible to remove the formatting chosen for all pivots from the wizard using the reset button.

Only the custom styles defined in Excel Cell Styles can be used and assigned to the following pivot areas:

  • Column header titles

  • Column headers

  • Row header titles

  • Row headers
  • Values
  • Default, which includes all areas not covered by a specific style, including the corner area between titles and row and column headers

Error and conflict management

When creating or updating a Pivot, the system checks for cells whose contents could be overwritten by the Pivot. If the area in which the Pivot is generated contains non-empty cells, the formula cannot expand and the #SPILL! error is displayed in the top left cell of the pivot area.

IMPORTANT:In the event of an error, a warning is shown allowing you to either continue (where possible) or cancel. In particular, it is not possible to continue in the event of a collision with other Pivots in Excel or Tagetik; whereas, in the case of a collision with other types of content, it is up to the user to choose whether to continue or not.