Statements

Getting Started

Global Functions

Administration

Extended Planning & Analysis (xP&A)

Consolidation & Financial Planning

User Interface of Lucanet Consolidation & Financial Planning

General Functions

Basic Configuration

Dashboards

Reporting & Analyzing

Import and Journals

Planning

Consolidation

Dimensions

Excel-Integration

Lucanet.Excel-Reporting

Configuring Workspaces

Creating Reporting Templates

Working with Tilde Formulas

Placeholder

Functions

Statements

Defining Properties of Return Values

Generating Excel Reports

CFP Add-in for Excel 365

Workspace Configuration

ESG Reporting

Disclosure Management

XBRL Tagger

Lease Accounting

Banking & Cash Management

Data Collection

Tax Compliance & Reporting

Glossary

Using statements in a tilde formula, you can dynamically insert or name rows and columns in an Excel report with Lucanet.Excel-Reporting.

For statements, the tilde formula must be structured as follows:

~statement:"Parameter_1";"Parameter_2"

The following rules apply for statements in tilde formulas:

Statements always end with a colon ( : ) followed by the parameters of the statement.

The parameters for a statement must be within inverted commas ( " ).

Several parameters are separated using semicolons ( ; ).

The following statements can be used in a tilde formula:

You can use the Row and Column statements to name the corresponding row or column so that they can be accessed in functions using the name, as well as the row or column number.

The advantage of accessing by name is that functions will retain the correct cell link even after rows and columns are inserted (which can change the numbering of existing rows and columns).

Structure of the Tilde Formula for Naming Rows and Columns

The tilde formula must be structured as follows:

~Row/Column:"Name"

Note: The name of the row or column cannot match a placeholder name.

Example

In the following reporting template, row 5 is named Period. In the Value function, the name Period is used to access the row with the period headings:

The generated report is displayed as follows:

The Fill rows and Fill columns statements can be used to insert further rows or columns dynamically, based on the respective row in an Excel report.

Structure of the Tilde Formula for Inserting Rows and Columns

The tilde formula must be structured as follows:

~Fill rows/Fill columns:Keyword=Item/Organization element/Partner/Transaction type/Adjustment level/Data level;[Abbreviation];[Name/OID];[No zero values];[Level]

The Abbreviation, Name/OID, No zero values, and Level parameters can be used optionally depending on the parameter.

Keywords for Items and Organization Elements

Items, accounts or organization elements can be inserted in the rows or columns of an Excel worksheet. The following keywords are available:

Item="Assets”

or **Pos="assets"

** All items or accounts directly subordinate to the

Assets

item

Item=B7

or **Pos=B7

** All items or accounts directly subordinate to the item in cell

Organization element="Group”

or **OE="Group"

** All organization elements directly subordinate to the organization element

Group

Organization element=B

or **OE=B

** All organization elements directly subordinate to the organization element in column

Keywords for Dimension Elements

Elements from the Partner, Data levels, Adjustment levels, and Transaction types workspaces can be added to the rows or columns of an Excel worksheet. This means that the partners, data levels, adjustment levels, or transaction types that are attached under folders can be added. For partners, it is also possible to add elements that lie under partner groups.

The following keywords are available:

Partner="U.S. subsidiary”

P="U.S. subsidiary"

All partners directly subordinate to the

U.S. subsidiary

partner group

Data level=lucanet://db=database&oid=123456

DL=lucanet://db=database&oid=123456

All data levels directly subordinate to the folder with the OID

123456

Adjustment level=lucanet://db=database&oid=234567

BE=lucanet://db=database&oid=234567

All adjustment levels directly subordinate to the folder with the OID

234567

Transaction type=lucanet://db=database&oid=345678

TT=lucanet://db=database&oid=345678

All transaction types directly subordinate to the

345678

folder

Folders for

data levels

adjustment levels

, and

transaction types

cannot be queried by their names. They must be targeted by their OID.

Since Lucanet 11 LTS, links to Lucanet elements are created in format ln:goto/LNFC/databasename?oid=XXXXX. The previous format lucanet://db=databasename&oid=XXXXX remains valid, however, and can still be used. This is why the format lucanet://db=databasename&oid=XXXXX is used in some examples for Lucanet.Excel-Reporting.

Example

The Excel worksheet contains a tilde formula with the Fill rows statement. The formula uses the URL of the item Other operating expenses:

The report created displays all the items contained in Other operating expenses. Row 19 displays the overall total of all items:

Optional Parameters

Depending on the parameter, the following optional parameters can be used for the Fill rows and Fill columns statements:

With the

Item

parameter: Abbreviation, Level, No zero values, Name/OID

With the

Organization element

parameter: Abbreviation, Level, No zero values, Name/OID

With the

Partner

parameter: Level, No zero values, Name/OID

With the

Data level

parameter: No zero values, Name/OID

With the

Adjustment level

parameter: No zero values, Name/OID

With the

Transaction type

parameter: No zero values, Name/OID

Abbreviation parameter is optional and specifies the ledger or schedule from which items or accounts are to be inserted. The Abbreviation parameter is used if an item or account occurs in several ledgers or schedules.

Note: In schedules, you can only search for items.

Tilde Formula Structure for 'Abbreviation'

The tilde formula must be structured as follows:

~Fill rows/Fill columns:Abbreviation=" Abbreviation";Item=" Item"/Organization element=" Organization element"

Keywords

The keyword defines from where the data to be read in are derived. The following keywords are allowed:

Abbreviation of a general ledger, abbreviation of a general ledger including subledgers

abbreviation of an schedule

Reading in values from ledgers or schedules with the specified abbreviation

fin

The keyword fin can still be used to read values from the ledgers to which the balance sheet and P&L from previous versions have been transferred.

stat

The keyword stat can still be used to read values from a statistical ledger to which the values from previous versions have been transferred.

Example for 'Abbreviation'

The statement Fill rows imports all items below Assets from the general ledger for the balance sheet with the abbreviation B to MS Excel:

Shows an Excel section with the tilde formula Fill Rows and the parameter ‘Abbreviation’ — 'Fill rows' statement with parameter 'Abbreviation'

The Level parameter is optional and specifies which level under the selected item, account, organization element, or partner group is to be read in to MS Excel. If the parameter is not specified, only the subsequent level is inserted.

Tilde Formula Structure for 'Level'

The tilde formula must be structured as follows:

~Fill rows/Fill columns:Keyword=Item/Account/Organization element/Partner group\Level= x

Values for 'Level'

The following level values are possible:

Number, e.g. 2

Inserts the level defined with the number under the specified item, account, organization element, or partner group. When a level lower than the first level is inserted, the system does not insert all the elements from the intermediate levels but just the elements that are located on the level entered.

Account

Inserts all accounts under the specified account in alphabetical order

Item

Inserts all lowest-level items under the specified item that have no subordinate elements in alphabetical order

Note: For partner groups, only the entering of a number (e.g. Level=2) is permitted for Level. The account and item parameters are not allowed.

Example for 'Level'

The statement specifies that the second level below the item with the URL lucanet://db=demo&oid=2408240 (= Other operating expenses) is to be read in to MS Excel:

Shows an Excel section with the tilde formula Fill Rows and the parameter ‘Level’ — 'Fill rows' statement with parameter 'Level'

In Lucanet, there are two levels under the Other operating expenses item:

Displays a ledger in Lucanet with one item and multiple sub-levels — Item with multiple levels

The items and values of the second level are displayed in the report:

Shows an Excel section. Column B/C of the table lists the items in the second level of the item Other operating expenses. — Result for parameter 'Level'

The No zero values parameter is optional and specifies that rows and columns that contain no values in Lucanet are not to be displayed in MS Excel.

Tilde Formula Structure for 'No zero values'

The tilde formula must be structured as follows:

~Fill rows/Fill columns:Keyword=Item/Account/Organization element/Partner/Data level/Adjustment level/Transaction type; No zero values

The Name/OID parameter is optional and specifies whether the queried elements are to be imported into MS Excel with their name or URL.

Tilde Formula Structure for 'Name/OID'

The tilde formula must be structured as follows:

~Fill rows/Fill columns: name/OID;Keyword=Item/Organization element/Partner/Data level/Adjustment level/Transaction type

Keywords

The keyword defines whether the elements to be read in are to be imported into MS Excel with the name or OID. The following keywords are allowed:

Name

The names of the elements are imported into MS Excel.

OID

The URL of the elements is imported into MS Excel.

Example

In ~Fill rows it is specified that the OIDs of the elements below the item with the URI ln:goto/LNFC/demo_group?oid=12907 are to be read into MS Excel:

Shows an Excel extract with the tilde formula Fill rows and the parameter OID — 'Fill rows' statement with parameter 'OID'

If the Name/OID parameter is not used, the names of the queried elements are created in MS Excel.

If the OID parameter is used, the

Attribute

function can be used to additionally import the names of the queried elements into MS Excel (see section

'Attribute' Function

Last updated on Aug 16, 2023

Statements

Overview

Tilde Formula Structure for Statements

Rules for Statements in Tilde Formulas

Available Statements

Overview

Tilde Formula Structure for Statements

Rules for Statements in Tilde Formulas

Available Statements

Naming Rows/Columns

Inserting Rows/Columns

Abbreviation

Level

No zero values

Name/OID

Table of Contents

Company

Legal Notice

Table of Contents

Related Articles

Company

Legal Notice