The TGKRET function

Description

From TaGetik RETrieve, the TGKRET function allows you to retrieve, according to filters on the dimensions, a value from a certain database, but not necessarily the one on which the form is run.

Syntax

The function uses the following syntax: TGKRET(dataSource,"DimensionName1:[HierarchyCode:*]value1", "DimensionName2:value2", ...,"Convert")

The value retrieved by the function is then shown in the cell in which the function is inserted.

Arguments

The items that can be added to the function are detailed below.

Datasource

Code of the database from which the value must be recovered. This code can be indicated in the following ways:

• with the database code, e.g. =TGKRET("TCPM_DEMODEV_001", ...)
• with the TGKCURRDB function, e.g. =TGKRET(TGKCURRDB(), ...)
• with reference to the Excel cell containing the database code, e.g. = TGKRET(A25, ...)

Selecting dimensions

To retrieve the value of the desired dimension, you must apply filters. Some examples of syntax are provided below:

• To retrieve a value of a dimension: :, e.g. for entity A00, specify Entity:A00. To retrieve a value of a dimension on which aggregation structures exist, specify a node: ::*, e.g. for the entity Entity:H_CODE:*H_NODE.
• For the account dimension, it is also possible to refer to an FST: :*, e.g. Account:*FST_CODE|FST_NODE

The supported dimensions are listed below.

Dimension	Description
Account:	Account code. Mandatory dimension. Individual accounts, nodes of an account hierarchy or an FST item can be used. In order for the formula to be correct, the account must be present in the list and must meet one of the following conditions: - be related to an FST. - be present in a control group. - be related to a node of an accounts hierarchy.
AmountType	Amount type. Mandatory dimension. The possible values are: - TIP_O: (original) amount type of data entered in the system by the user. - TIP_OV: (in transaction currency) amount type that only represents the amounts expressed in a currency other than the default entity currency (for example, an amount in euros to be attributed to an entity whose currency is the dollar) - TIP_OE: proportional amount type - TIP_OEV: proportional amount type in transaction currency - TIP_PR: converted amount type - TIP_PRV: amount type converted into transaction currency - TIP_CV: consolidable amount type - TIP_CVV: consolidable amount type in transaction currency - TIP_DES: (description) amount type that allows the user to enter and save text-based information, such as notes or comments.
Category	Category code
Currency:	Currency code
Cust_Dim1..Cust_Dim5:	Element code of one of the custom dimensions. For example, if the products to recover the value of a certain product are handled on custom dimension 1, the code is Cust_Dim1.
Entity	Entity code
ICCust_Dim:	Element code of the intercompany custom dimension
ICEntity	Intercompany entity code
Period	Period code. Mandatory dimension.
PeriodLength:	Period length. The possible values are: - LUN_0: progressive - LUN_1: monthly - LUN_2: bimonthly - LUN_3: quarterly - LUN_4: every four months - LUN_5: every five months - LUN_6: half-yearly - LUN_12: annual - LUN_QTD: Quarter to date - LUN_STD: Semester to date - LUN_YTD: Year to date
Scenario	Scenario code. Mandatory dimension.

Data conversion

The Convert element allows the user to convert dimension data while running a form. The Convert values are:

• Final: final FX rate
• Average: average FX rate
• Period: period average FX rate
• Account: Conversion type associated with the account

The syntax is the following: <("dimensionNamex:valuex")> i.e. dimension name: Convert value.

Calculation by segment

In the TGKRET formula, it is possible to request the amount calculation in the following ways: by segment, intersegment, parent segment and parent intersegment.

To do this, the desired suffix (suffixes are case insensitive) must be concatenated to the desired dimension code (node).

Calculation method	Suffix
segment	- \s - \segment Example: \s
intersegment	- \i - \is - \intersegment Example: \i
parent segment	- \p - \ps - \parentSegment Example: \p
parent intersegment	- \pis - \pi - \parentIntersegment Example \pis

Syntax conventions and specifics

• For dimension elements, codes can be entered in lowercase even if the application database on which the form was run does not support them (because the Accept lower case codes option is not active).
• To recover several elements or nodes of aggregation structures for the same dimension, values can be separated by ",". The system reads the data related to all specified elements. For example, to recover accounts 50010 and 40010, the syntax in the position relative to the account is 50010,40010.
• To recover several nodes belonging to different aggregation structures for the same dimension, the system reads the intersection data related to the elements related to the nodes. For example, to recover the entities related to node N1 of hierarchy H1 and node N1 of hierarchy H2, the syntax is Entity:H1:*N1,H2:*N1.

Example

To retrieve the value of the revenue for the Antivirus product for the geographical area Italy and for scenario 2012BDG, apply the following filters:

• Scenario = 2012BDG
• Period = 12
• Account = all revenue accounts, i.e. FST item 500300 - Total Revenues in FST 010.
• Product (managed on custom dimension 2) = Antivirus, whose code is A01
• Entity = all entities related to the Italy node (X01) of hierarchy 03 which groups entities by geographical area
• Cost Center (managed on custom dimension 1) = generic element G01
• Employee (managed on custom dimension 2) = generic element G01

The function’s syntax is =TGKRET(TGKCURRDB(), "Account:*010|50030", "Scenario:2012BDG","Entity:03:*X01", "Cust_Dim1:G01", "Cust_Dim2:A01","Cust_Dim3:G01","Period:12","Category:$AMOUNT","AmountType:TIP_O","PeriodLength:LUN_0")

To read the original data for account 50010 on December 2011 converted into EUR at the average FX rate, the function’s syntax is =TGKRET(TGKCURRDB(), "Scenario:2011ACT", "Period:12", "Account:50010", … , "Currency:EUR", … , "Convert:Average")