Clausion Dynamic Reporting
Clausion Dynamic Reporting is an additional module available for the Clausion environment. With Clausion Dynamic Reporting you can create dynamic reports and analyze any data available in the Clausion environment using Dynamic Reporting (DR) -specific Excel formula functions that are connected to the Clausion database. The reported and analyzed data is customer-specifically adjustable. For example, when organization, accounting period, document series or account chart is modified in the application, the changed dimensions and IDs are dynamically usable in the reports defined in Clausion Dynamic Reporting.
The Clausion Dynamic Reporting functionality is available on a separate tab in the Excel ribbon.
If the Clausion DR tab is not visible in your Excel workbook, contact your Clausion administrative user for information on the location of the installation package.
Installing Clausion Dynamic Reporting add-in
Double-click the setup.exe file and click Install. If there are problems with the Clausion Dynamic Reporting functionality, check from the Configuration drop-down list that the correct configuration is in use.
Using Clausion Dynamic Reporting
If a user who has "read and write" rights to unit A has saved a DR Excel file and a user who does not have rights to the unit opens it, the user without the rights can see the data of unit A but cannot update it. When a user without the required rights tries to update and refresh the workbook, the data is replaced by 0 values. In addition, when the Automatic Calculation option is selected, the data is not shown if the user does not have the required rights.
Inserting Clausion Dynamic Reporting functions
Click Insert DR Function. An Insert Function dialogue box similar to Excel function dialogue box opens. The following functions are available:
DRData: Retrieves data for the selected combination (period, year, unit (dim00dimxx), data type, periodic/cumulative, currency, document series, document number) from the Clausion database. If something is incorrect in the entered argument values (for example, the value cannot be found from the database or you do not have sufficient rights to retrieve the data), the result of the function will be 0.[v24.12 and later:] You can also filter data using dimension extra field values. [v24.12 and latercontent ends]
DRAccountName: Retrieves the name of an account from the Clausion database.
DRAccountDCCode: Retrieves the debit/credit code of an account from the Clausion database. The debit value is 1 and credit value -1.
DRCurrencyRate: Returns the currency rate used for converting currency amounts to or from the group currency when saving to and reporting from the Clausion database.
DRDatatypeName: Retrieves the name of a valid data type from the Clausion database.
[v24.06 and later:] DRExtraData: Returns data type's extra field value for the selected combination (data type, document series, year, account, unit (dim00–dimxx), extra field). If this results in several values for a text type extra field, the values are combined into one cell as consecutive values, separated by a delimiter. [v24.06 and latercontent ends]
DRUnitCurrency: Returns the currency code of a unit from the Clausion database.
DRUnitName: Returns the unit name from the Clausion database.
More information about the functions and their arguments is shown in the Insert Function dialogue box.
The following sections include lists of DR functions based on their arguments.
ID and string must be written in quotes ("300000P").
Arguments in DR functions can be cell references but cannot be other functions or contain concatenations ("&" character).
To get fastest results use as little DR functions as possible by using cell references.
An empty cell cannot be referenced as an optional argument. This will produce an error.
To get default balances as positive (Show DK: No) you may use product of DRAccountDCCode and DRData (example below).
'=DRData(Datatype_Code;Doc_Serie;Account_No...)
Get account balance (debit: positive, credit: negative)
DRData functions
Content |
Type |
Possible values* |
Notes |
|---|---|---|---|
Datatype |
ID |
Typically ACT, BUD, EST, ... |
|
Document series / number |
ID |
10000, 20000, 60002, ... |
Use ";" as separator for multiple choices. You can also select just one document series or number. You cannot select both series and numbers at the same time. Clausion custom parameters not supported. |
Account |
ID |
979989P, 199999P, 299999P, ... |
|
Year |
ID |
Typically 2020, 2019, ... |
|
Period |
ID |
Typically 0, 1, 2, ..., 12 |
|
Is cumulative |
boolean |
TRUE or FALSE |
No quotes. False means periodical. |
Currency |
ID |
[v24.03 and later:] UNIT, GROUP, or a specific currency: [v24.03 and latercontent ends] EUR, SEK, USD, ... |
|
DIM00 unit |
ID |
Organization-specific unit codes |
|
DIM00 counter unit |
ID |
Organization-specific unit codes |
Optional. This argument only for DIM00. When empty, takes value from argument 8. |
DIM00 hierarchy |
ID |
Could be, for example, "STRUCTURE1" |
Optional. Hierarchy used for consolidation units. When empty, the first hierarchy is used. |
DIM01 unit |
ID |
|
Optional |
DIM01 hierarchy |
ID |
|
Optional |
DIM02 unit |
ID |
|
Optional |
DIM02 hierarchy |
ID |
|
Optional |
DIM03 unit |
ID |
|
Optional |
DIM03 hierarchy |
ID |
|
Optional |
... |
|
Last possible dimension is 9 |
|
[v24.12 and later:] Dimension extra field filter |
Filter |
Extra field definition + operator + filtering value, for example, "E01=Europe" |
Optional. Use vertical bar "|" as a separator for multiple filters. Wrap the extra field value with curly brackets "{}" if the value includes special characters. Use semicolon ";" as a separator for multiple operators. The operators for extra field operations are "=" (equal to), "!=" (not equal to), "<" (less than), "<=" (equal to or less than), ">" (greater than), ">=" (equal to or greater than), "BT" (between), "!BT" (not between), "CT" (contains), "IN" (in), "!IN" (not in).
|
'=DRAccountName(Account_No;Language_Code)
Get account name
DRAccountName functions
Content |
Type |
Possible values* |
Notes |
|---|---|---|---|
Account |
ID |
979989P, 199999P, 299999P, ... |
|
Language |
ID |
FI, EN, SV, ... |
|
'=DRAccountDCCode(Account_No)
Get default balance of an account (debit: 1, credit: -1)
DRAccountCode functions
Content |
Type |
Possible values* |
Notes |
|---|---|---|---|
Account |
ID |
979989P, 199999P, 299999P, ... |
|
'=DRDatatypeName(Datatype_Code;Language_Code)
Get datatype name
DRDatatypeName functions
Content |
Type |
Possible values* |
Notes |
|---|---|---|---|
Datatype |
ID |
Typically ACT, BUD, EST, ... |
|
Language |
ID |
FI, EN, SV, ... |
|
'=DRCurrencyRate(Currency;Rate_Type;Datatype_Code;Year;Period)
Get currency rate
DRCurrencyRate functions
Content |
Type |
Possible values* |
Notes |
|---|---|---|---|
Currency |
ID |
Typically SEK, USD, EUR, ... |
|
Type |
string |
INCOME, BALANCE or OPENING |
|
Datatype |
ID |
Typically ACT, BUD, EST, ... |
|
Year |
ID |
Typically 2020, 2019, ... |
|
Period |
ID |
Typically 0, 1, 2, ..., 12 |
|
[v24.06 and later:]
'=DRExtraData(Datatype_Code;Doc_Serie;Account_No;Year;ExtraField_ID...)
Get account balance (debit: positive, credit: negative)
DRExtraData functions
Content |
Type |
Possible values* |
Notes |
|---|---|---|---|
Datatype |
ID |
Typically ACT, BUD, EST, ... |
|
Document series / number |
ID |
10000, 20000, 60002, ... |
Use ";" as separator for multiple choices. You can also select just one document series or number. You cannot select both series and numbers at the same time. Clausion custom parameters not supported. |
Account |
ID |
979989P, 199999P, 299999P, ... |
|
Year |
ID |
Extra field IDs of the data type |
|
Extra Field ID |
ID |
Typically 0, 1, 2, ..., 12 |
|
Currency |
ID |
UNIT, GROUP, or a specific currency: EUR, SEK, USD, ... |
|
DIM00 unit |
ID |
Organization-specific unit codes |
|
DIM00 counter unit |
ID |
Organization-specific unit codes |
Optional. This argument only for DIM00. When empty, takes value from argument 8. |
DIM00 hierarchy |
ID |
Could be, for example, "STRUCTURE1" |
Optional. Hierarchy used for consolidation units. When empty, the first hierarchy is used. |
DIM01 unit |
ID |
|
Optional |
DIM01 hierarchy |
ID |
|
Optional |
DIM02 unit |
ID |
|
Optional |
DIM02 hierarchy |
ID |
|
Optional |
DIM03 unit |
ID |
|
Optional |
DIM03 hierarchy |
ID |
|
Optional |
... |
|
Last possible dimension is 9 |
|
[v24.06 and latercontent ends]
'=DRUnitCurrency(Dim_Id;Unit;Year)
Get unit currency code (ID)
DRUnitCurrency functions
Content |
Type |
Possible values* |
Notes |
|---|---|---|---|
Dimension |
ID |
Typically DIM00, DIM01, ... |
|
Unit |
ID |
Organization-specific unit codes |
|
Year |
ID |
Typically 2020, 2019, ... |
|
'=DRUnitName(Dim_Id;Unit;Year;Language_Code)
Get unit name
DRUnitName functions
Content |
Type |
Possible values* |
Notes |
|---|---|---|---|
Dimension |
ID |
Typically DIM00, DIM01, ... |
|
Unit |
ID |
Organization-specific unit codes |
|
Year |
ID |
Typically 2020, 2019, ... |
|
Language |
ID |
EN, FI, SV, ... |
|
Excel references can be used in DR formulas, however, Excel formulas cannot be used inside DR formulas. For more information on using references, refer to Microsoft Excel documentation.
*Account, language, currency, etc. included in the system. The listed accounts or other values are examples and they may differ from the values you have in use.
Examples
To make the example work in Excel, delete the apostrophe (') from the beginning of the cell.
Use correct separator for function arguments (',' or ';')
=DRData("ACT";"10000;20000";"979989P";"2019";"12";TRUE;"EUR";"UNIT1";;;;;;;;;;;;;;;;;;"E01=Europe")
=DRAccountDCCode("994989P")
=DRAccountName("994989P";"EN")
=DRDatatypeName("ACT";"EN")
[v24.06 and later:] =DRExtraData("ACT";"10000;20000";"979989P";"2024";"E02";"UNIT1") [v24.06 and latercontent ends]
=DRCurrencyRate("USD";"OPENING";"ACT";"2019";"0")
=DRUnitCurrency("DIM00";"UNIT1";"2019")
=DRAccountDCCode("979989P")*DRData("ACT";"10000;20000";"979989P";"2016";"12";TRUE;"EUR";"UNIT1")
Create Static Copy
The Create Static Copy feature enables the user to create a snapshot of the data in the Excel workbook at creation time. The feature creates a new Excel workbook, which is similar to the original workbook but all the formulas are replaced with actual values (values, which the formula restored just before creating a new workbook file).
Automatic calculation option and refresh mode
You can select, whether you want the values of the formula(s) to be calculated every time you enter a DR formula, or if you want to do this manually. To have values of the formula(s) calculated automatically, select the Automatic Calculation check box.
To calculate values manually:
Deselect the Automatic Calculation check box.
-
Select an option from the Refresh Mode drop-down list:
Workbook: All functions in the workbook are calculated.
Sheet: All functions on the sheet are calculated.
-
Selection: All functions in the selected range are calculated. Multiple ranges can be selected on one sheet.
This option only applies within the active sheet, not on multiple sheets.
Click
Refresh.
When Automatic Calculation is selected, copy-pasting to several cells retrieves data to all included cells and also the formatting is copied. When Automatic Calculation is not selected, copy-pasting to several cells only copies the formula and the additional formatting. By default the Automatic Calculation check box is not selected.
Data is not automatically retrieved from the database when the Excel workbook is opened. If local Excel settings are set to automatically update external links, the formula results saved previously are not shown when the file is opened (instead, the DR formula is shown as text).
Note also that if the Calculation Options setting in Excel is Automatic and the Automatic Calculation option has been selected in DR, all reference values are updated even outside a selected Refresh Mode range when you click Refresh.
Configuration
The Configuration drop-down list contains the available configurations. You can change your DR environment by selecting another configuration from the list.
If no configurations are found, check that the path to the configuration folder is valid, you have the required permissions to the folder, and a configuration file exists.
If there is an error reading a configuration, the XML structure of the configuration file may be invalid.
For further assistance, contact your administrator.
DR Logging
Dynamic Reporting logging is not on by default. To start or stop logging, click DR Logging.
The Log folder button opens the folder where the log file is saved (your temp folder). The name of the log file is dr_info.log. One log file per day is created.