Functions
Last updated on 2025-12-11
Overview
With functions in tilde formulas, you can query information and data from Lucanet and output them in an Excel report.
A function specifies what type of information is retrieved (e.g., numerical values, currencies, exchange rates).
This article contains the following sections:
Tilde Formula Structure
For functions, the tilde formula must be structured as follows:
~Function(Parameter 1="Value";...;Parameter n="Value")
Rules for Functions in Tilde Formulas
The following rules apply for functions in tilde formulas:
- One, several or no parameters can be added to the function name.
- The parameters of a function are enclosed in round brackets ( ).
- Several parameters are separated using a semicolon ( ; ).
- The parameters for a function are described with a keyword or a placeholder.
- A keyword has a value that follows an equals sign ( = ) and is enclosed in quotation marks.
- It is also possible to use cell references as values. Quotation marks must not be used for cell references.
Available Functions
The following functions can be used in a tilde formula:
The Value function can be used to export any value from an account or an item in the Lucanet database.
Tilde Formula Structure
The tilde formula must be structured as follows:
~Value(Item="Name or URL of item/account";Keyword_1/Placeholder_1;...;Keyword_n/Placeholder_n)
Basic Parameter of the Value Function
In the Value function, you can use the following basic parameters, which match the dimensions in Lucanet:
- Item/account
- Period
- Organization element
- Data level
- Adjustment level/group
- Transaction currency
- Display currency
- Partner
Rules for the value function
- The basic parameter item/account must be specified.
- All other basic parameters are optional.
- For optional parameters, you can use either a placeholder or a keyword.
- A value following an equal sign ( = ) is assigned to a parameter.
- The value of a parameter must be in quotation marks ( " ).
Keywords
For the parameters of the Value function, the following keywords are available:
- Item/account: Item, Account, ACC
- Period: Period
- Organization element: Organization element, OE
- Data level: Data level, DL
- Adjustment level: Adjustment level, BE
- Transaction currency: Transaction currency, TC
- Display currency: Display currency, Currency, DC
- Partner: Partner, P
Example
In the Excel worksheet, the Value function has parameters for item (item), period (period), and transaction type (tt). In the tilde formula, references to a cell area (item=b:c), a cell (c12) and to a row (15) are used:
The Currency function enables the default currency of elements from the Reporting entities or Organization elements workspace to be read in to MS Excel.
Structure of tilde formulas
The tilde formula must be structured as follows:
~Currency(Organization element="Name of the element")
The following elements can be specified as organization element:
- Reference to reporting entity
- Reference to cost center
- Reference to cost center group
- Reference to consolidation area
- Reference to consolidation unit
- Reference to consolidation unit group
- Organization group
The specification of an element is optional. If no element is specified, the user has to choose an element when creating the report.
Example
In the Excel worksheet, the Organization element parameter of the Currency function is specified with a reference to cell B2:
'Currency' function
The rate function can be used to read in the average exchange rate, the closing exchange rate, or the YTD exchange rate at which a transaction currency is converted into a display currency, to MS Excel.
Structure of tilde formulas
The tilde formula must be structured as follows:
~rate(table="name of exchange rate table";data level;exchange rate type;period)
Keywords
The following keywords are available for the parameter Exchange rate type:
- Closing exchange rate: Returns the closing exchange rate for a month
- Average exchange rate: Returns the average exchange rate for a month
- YTD exchange rate: Returns the YTD rate of a month
Only one of the available keywords can be specified at a time.
Example
In the Excel worksheet, the closing exchange rate and the average exchange rate for the table USD -> EUR are read in in the Actual data level on a monthly basis (~month) to MS Excel. In the tilde formula, data level (B2), closing exchange rate (B) and period (4) are used by means of cell references:
The generated report appears as follows:
Result for function 'rate'
You can use the Attribute function to query attribute values. The Attribute function can also be combined with the tilde formulas ~Fill rows and ~Fill columns to import the names of elements into MS Excel in addition to their OIDs.
Tilde Formula Structure
The tilde formula must be structured as follows:
~Attribute(attribute;element)
Parameters of the Attribute Function
The following placeholders must be specified:
- Attribute: Key for the attribute whose value is to be read in to MS Excel
- Element: The name or URL of the element whose attribute value is to be read in to MS Excel
Example
In the Excel worksheet, the Attribute function is used to query the English (Attribute=name) and the German name (Attribute=name_ger) of items and accounts. In column C, a cell reference (b) is used in row 3 and the URL of the account is used directly in row 4:
'Attribute' function
The generated report appears as follows:
Result for function 'attribute'
You can use the Document function to access the description for elements.
Tilde Formula Structure
The tilde formula must be structured as follows:
~Document(Element)
The Element parameter represents the element whose description you want to copy to MS Excel.
Example
The tilde formula returns the description of the data level with the URL ln:goto/LNFC/demo?oid=12907.
The Comment function can be used to read in comments on items and accounts in MS Excel.
Tilde Formula Structure
The tilde formula must be structured as follows:
~Comment(Item/Account/Acc="Item/Account";Period="Period";Data level="Data level";Adjustment level="Adjustment level";Organization element="Organization element")
Specifying a data level, adjustment level, and reporting entity is optional. If no element is specified, the user has to choose one of these elements when creating the report.
Example
The tilde formula supplies the comment on the item Technical equipment and machines in November 2017:
'Comment' function