Quering Values
Last updated on 2025-04-29
Overview
You can use a LUCANET formula to query values from Lucanet and read them in to MS Excel. The formula for querying values must contain certain parameters and can be expanded with optional parameters.
This article contains the following sections:
Querying Values
To read in values from Lucanet into Excel, the formulas must contain the following parameters:
=LN.VALUE("Abbreviation","Data level","Adjustment level/Adjustment level group",
“Organization element","Account or Item","Periodicity","StartDate")
The abbreviation defines from where the data to be read in are derived. The following abbreviations are allowed:
Abbreviation
Meaning
Abbreviation of a general ledger, abbreviation of a general ledger including subledgers or abbreviation of a schedule
Reading in values from ledgers or schedules with the specified abbreviation
fin
fin was used in previous versions as keyword for the balance sheet and P&L and can be used as code to read values out of the ledgers to which the balance sheet and P&L of previous versions were transferred.
stat
stat was used in previous versions as a keyword for statistical ledgers and can be used as code in order to read values out of a statistical ledger to which statistical ledgers of previous versions have been transferred.
The Data level parameter specifies in which data level the data to be read in are contained. Data can be located in the actual data level and in any number of planning data levels. To specify data levels:
Data level
Notation in the Lucanet formula
Actual data level
"Actual"
Planning data level
"Plan 2026-2028"
The adjustment level or adjustment level group parameter specifies the adjustment level or adjustment level group to which the values to be imported are posted and evaluated. The default adjustment level is Data imports. To specify adjustment levels or adjustment level groups:
Adjustment level
Notation in the Lucanet formula
Data imports
"Data imports"
Delta under local GAAP
"Delta under local GAAP"
The parameter Organization element specifies the reference to the reporting entity, cost center, cost center group or organization group from which the values are to be read. Specify organization elements as follows:
Organization element
Notation in the Lucanet formula
Holding Inc.
"Holding Inc."
The parameter Account or Item specifies the name of the account and item whose value you want to export. To specify accounts or items:
Account/item
Notation in the Lucanet formula
Technical equipment
"Technical equipment"
Turnover
"Turnover"
The parameter Periodicity specifies the frequency with which the values are read from Lucanet. The following periods are allowed:
Period
Meaning
m
Monthly value of the desired period
q
Quarterly value of the desired period
y
Annual value of the desired period
t
Trimester value of the desired period
s
Semester value of the desired period
fq
Quarterly value for the fiscal year of the desired period
fy
Annual value for the fiscal year of the desired period
Example: Export of values for 31/12/2017. The possible periodicities return the following:
Periodicity
Values
m
The monthly value for the month of December 2017
q
The value of the last quarter 2017
y
Annual value 2017
fq, fy
The quarterly or annual values for the different fiscal year
The StartDate specifies the value for the month, quarter or year in which the date falls. The entry must be made in the format DD/MM/YYYY . This is regardless of the formatting of the view in MS Excel. When entering the date 31/12/2074, for example, the view format could be Dec. 2017.
Note: Instead of a specific entry, you can also use MS Excel to apply absolute or relative cell references, such as A2 (relative) or $A$2 (absolute) and references to named cells (see the following examples).
Optional Parameters
A Lucanet formula with optional parameters is structured as follows:
=LN.VALUE("Abbreviation","Data level","Adjustment level","Organization element",
"Account or Item","Periodicity","StartDate",["EndDate"],["Transaction type"],
["Display currency"],["Transaction currency"],["Partner/partner group"],["YTD"])
Caution:
- In order to correctly interpret the optional parameters, you must stick to the specified sequence!
- If not all optional parameters are entered for a LUCANET formula, commas need to be set also for those parameters which are not entered.
The following parameters can be used:
When querying values, the optional parameter transaction type can be added to the LUCANET formula. By default, for values from the financial sector, the balance at the end of the period is always exported to MS Excel. When using the optional parameter Transaction type, it is also possible to query other values (e.g. the balance at the start of the period).
Note: If the parameter Transaction type is not specified when querying parameter values, account balances are always exported.
Structure of the LUCANET Formulas
A Lucanet formula querying the transaction type must be set up as follows:
=LN.VALUE("Abbreviation","Data level","Adjustment level",
"Organization element","Account or item","Periodicity",
"StartDate",,"Transaction type")
Parameter
The following parameters for transaction type are available:
Parameter
Description
Start
Returns the start value of the account on a given date
TF
Returns the change to the account on a given date
Balance
Returns the balance of the account on a given date
If fixed asset schedule, provision schedule, loan schedule, and/or statement of changes in equity are set up in Lucanet, you can also query information (in addition to the default parameters) from these categories. The following parameters are available:
Parameter
Description
Name of a transaction type (e.g. 110 Addition)
Returns the value of the specified transaction type
Cost.Start
Returns the start value of the balance sheet account in the transaction type group Historical cost. Alternatively, it is also possible to query the group Accumulated depreciation/amortization ("Accumulated depreciation and amortization.Start").
Cost.TF
Returns the change to the balance sheet account in transaction type group Historical cost. Alternatively, it is also possible to query the group Accumulated depreciation and amortization ("Accumulated depreciation and amortization.TF").
Cost.Balance
Returns the balance of the account in the transaction type group Historical cost. Alternatively, it is also possible to query the group Accumulated depreciation/amortization ("Accumulated depreciation and amortization.Balance").
Note: The parameter depends on the name of the transaction types. The parameters must be named just as they are named in the Dimensions workspace under Transaction types.
When querying values, the optional parameter EndDate can be added to the LUCANET formula. You can use this parameter, for example, if you want to read in the cumulated values of a specific period from the P&L to MS Excel. The value read in is then the total of values between the start and end date.
Structure of the LUCANET Formulas
The LUCANET formula must be set up as follows:
=LN.VALUE("Abbreviation","Data level","Adjustment level","Organization element","Account or item","Periodicity","StartDate","EndDate")
When querying values, the optional parameter Partner or Partner group can be added to the LUCANET formula. You can use these parameters to query partner information.
Structure of the LUCANET Formulas
The LUCANET formula must be set up as follows to query partner information:
=LN.VALUE("Abbreviation","Data level","Adjustment level","Organization element", "Account or item","Periodicity","Start date",,,,,"Partner/Partner group")
Note: If no partner is specified, the system returns the value of the account or the item.
By default, values from Lucanet are read in to MS Excel in the default currency. The Display currency and Transaction currency parameters can be used to define which values are to be read in to MS Excel and in which currency.
Structure of the LUCANET Formulas
To query values in a currency other than the default currency, the LUCANET formula must be set up as follows:
=LN.VALUE("Abbreviation","Data level","Adjustment level","Organization element","Account or item","Periodicity","StartDate",,,"Display currency","Transaction currency")
Variants
The result of a query depends on the parameters used in the Lucanet formula. The following variants are available:
Parameter
Result
Without keyword
The sum of all business transactions (in all transaction currencies) translated into the specified display currency
Note: You can also use Transaction currency="All" to enter the values of all transaction currencies.
Display currency only
The sum of all business transactions (in all transaction currencies) translated into the specified display currency
Note: You can also use Transaction currency="All" to enter the values of all transaction currencies.
Transaction currency and display currency
The sum of all business transactions in the specified transaction currency translated into the specified display currency
The YTD parameter can be used to query the value of an account or an item for the Year-To-Date period up to the current period.
Structure of the LUCANET Formulas
The LUCANET formula must be set up as follows:
=LN.VALUE("Abbreviation","Data level","Adjustment level","Organization element","Account or item","Periodicity","Start date",,,,,,"YTD")