Skip to content

CCH® Tagetik Functions

Introduction

CCH® Tagetik Intelligent Analytics for Microsoft Excel provides a set of functions that can be used in Excel formulas to retrieve data and element lists from CCH® Tagetik.

These functions allow the user to build Excel reports, retrieving data from CCH Tagetik, with a completely free and intuitive approach, without the need for any additional training.

Excel formulas can be written to combine several functions and reference the values of other cells, even from other Excel sheets, to build reports and simulations from whatever source the data came from.

CCH® Tagetik Intelligent Analytics functions can be accessed via the Excel formula bar or through its side panelCCH® Tagetik.

In the add-in CCH® Tagetik Intelligent Analytics, the following functions are available for Microsoft Excel:

  • TGK.FILTER
  • TGK.OR
  • TGK.DISPLAY
  • TGK.VAL

TGK.FILTER function

The TGK.FILTER function allows you to select lowest level elements or nodes from the CCH Tagetik data model and use them in other formulas.

Parameters

  • Dimension_code: string specifying the Tagetik dimension code.
  • Hierarchy_code: (optional) string specifying the code of a dimension hierarchy. If omitted, the following parameters indicate the codes of lowest level elements; otherwise they indicate the codes of node elements from the specified hierarchy.
  • Selected_item1...n: list of strings representing the code of a lowest level element or node. The list must consist of at least one element.

Returned value

A value representing a filter consisting of only the specified lowest level elements or nodes. The filter is represented by an Excel Data Type (official Excel documentation: https://support.microsoft.com/en-us/office/excel-data-types-stocks-and-geography-61a33056-9935-484f-8ac8-f1a89e210877) It can be used as a filter parameter in the functions TGK.VAL, TGK.DISPLAY or TGK.OR.

Examples

TGK.FILTER("DIM",, "01", "02")

This example returns the lowest level elements '01' and '02' of the analytical dimension with code 'DIM'.

TGK.FILTER("DIM", "H1", "01", "02")

This example returns the nodes '01' and '02' of the 'H1' hierarchy of the analytical dimension with code 'DIM'.

TGK.OR function

The TGK.OR function allows you to combine multiple filters within the same analytical dimension using the logical OR operator.

Parameters

filter1...n: list of filters to be combined with the OR operator. Two or more filters can be combined.

Returned value

A value representing the combination of the specified filters. The filter is represented by an Excel Data Type (official Excel documentation: https://support.microsoft.com/en-us/office/excel-data-types-stocks-and-geography-61a33056-9935-484f-8ac8-f1a89e210877). It can be used as a filter parameter in the TGK.VAL or TGK.DISPLAY functions.

Example

TGK.OR(TGK.FILTER("DIM",, "01"), TGK.FILTER("DIM", "H1", "02"))

This example combines in OR the lowest level element filter '01' and the node filter '02' of the hierarchy 'H1' both of the analytical dimension with code 'DIM'.

TGK.DISPLAY function

The TGK.DISPLAY function allows you to retrieve the element descriptions defined via the TGK.FILTER and TGK.OR formulas

Parameters

  • application_name: string specifying the application name.
  • filter: a filter obtained using the TGK.FILTER or TGK.OR function.
  • text: (optional) string specifying the text used to overwrite the original description.

Returned value

A value containing the elements selected through the applied filter, enriched with descriptions. The filter is represented by an Excel Data Type (link to official Excal documentation: https://support.microsoft.com/en-us/office/excel-data-types-stocks-and-geography-61a33056-9935-484f-8ac8-f1a89e210877). It can be used as a filter parameter in the TGK.VAL or TGK.OR functions.

Example

TGK.DISPLAY("App", TGK.FILTER("DIM",, "01", "02"))

This example shows the descriptions of elements '01' and '02' of the analytical dimension with code 'DIM'.

TGK.VAL function

The TGK.VAL function allows you to retrieve values from the specified filters.

Parameters

  • application_name: string specifying the application name.
  • reporting_view: string specifying the reporting view.
  • measure: string specifying the measure to be aggregated.
  • filter1...n: (optional) filters to be combined with the AND operator.

Returned value

The numerical value of the filtered data intersection for the specified measure. The function returns an empty cell if the filter does not match any data.

Examples

TGK.VAL("App", "ReportingView", "Measure", TGK.FILTER("DIM",, "01", "02"))

This example retrieves the aggregate value from the reporting view ‘ReportingView’ for elements ‘01’ and ‘02’ of the analytical dimension coded ‘DIM’, related to the measure ‘Measure’.

TGK.VAL("App", "ReportingView", "Measure", TGK.OR(TGK.FILTER("DIM1",, "01", "02"), TGK.FILTER("DIM1", "H1", "03")), TGK.FILTER("DIM2", "H2", "04"))

This example retrieves the aggregate value from the reporting view ‘ReportingView’ for the measure 'Measure' resulting from the application of the following filters based on the logic below:

TGK.FILTER("DIM1",, "01", "02") OR TGK.FILTER("DIM1", "H1", "03") ] AND TGK.FILTER("DIM2", "H2", "04")