Advanced Settings for custom fields

You can use flex.bi advanced settings to add measures, properties or dimensions for existing cubes for HansaWorld application and edit existing cube import structure for other applications.

Additional custom fields

If you would like to import in flex.bi additional fields from a custom field that is not supported by default, then you can add a definition of how this custom field should be imported. Also, if some default custom field settings should be changed, then it can be done with adding necessary custom field configuration parameters.

1. Find the field name

First, you need to find out the register and REST API code for the field. You can find codes for registers in the HanasWorld source File import documentation page. Then you can use the code to find code for REST API field in HansaWorld Module Technics → Reports → Import/Export format.

You can see in the example screenshot below the register code (1.) and codes for REST API fields (2.).

2. Configure custom field settings

Register code and register REST API code

[register_code.register_rest_api_code]

CODE

Where register_code is register code and register_rest_api_code is the register REST API code. This will start the block of configuration parameters for the custom field.

Name for the custom field

A name for the field describing the logic in the most understandable for you way e.g.

name = "Price"

CODE

Cube Name

The cube name where the custom field will be available. You can find the full cube names in Analyze section in your flex.bi.

cube_name = "HansaWorld Invoices"

CODE

Dimension name in flex.bi for the register

Name of the dimension to which this field should be related to e.g. Invoices, Transactions, Quotations etc.

dimension_name = "Invoice"

CODE

Dimension level name

The name of the level within the specified dimension, where the field should be added e.g.

level_name = "Invoice Item"

CODE

Dimension item level names for registers that do not have dimensions: "Transaction Item", "Simulation Item".

Data type of this field

One of the supported data types - integer, value, string e.g.

data_type = "string"

CODE

By default, the string maximum length is 255 characters. If there is a need to import longer string values, then use the additional limit setting to specify the maximum length.
Other available types are text, integer, decimal, date, datetime.

If you use the decimal data type, then by default the precision (maximum number of digits) is 15 and the scale (digits after the decimal point) is 2. You can change these defaults with the additional precision and scale settings.

Additional options

If you would like to import custom field as additional dimension then add

dimension = true

CODE

If you would like to import custom field as a measure then add

measure = true

CODE

If you would like to do operation with the incoming data before fields are imported you can use custom javascript code

javascript_code ='''
  if (doc.rows) {
    doc.rows.forEach(function(row){
    row.negative_rowGP = String(Number(row.rowGP) * -1);
  })
}
'''

CODE

If you would like to share the added measure to another cube then specify the cube name with the cubes_for_measure_sharing parameter:

cubes_for_measure_sharing = ["HansaWorld Invoices"]

CODE

If you are importing HansaWorld's data with files then the number of the field also needs to be defined. You can find what number is each field in your HansaWorld's "Technics → Reports → Export/import format" by specifying the register's code that you are importing.

file_field = 4

CODE

If an REST API field contains a full stop characters (“.“) or the same REST API should be used twice you can use rest_api_field to define the REST API field name instead. If rest_api_field is used then in order to change the returned value for the whole custom field using Javascript the name in rest_api_field needs to be referenced instead as well.

rest_api_field = 'T.NAME'

CODE

Example of custom dimension definition

[ORVc.LangCode]
name = "Language code"
cube_name = "HansaWorld Sales Orders"
data_type = "string"
dimension_name = "Sales Order"
level_name = "Sales Order"
dimension = true

CODE

Example of custom measure definition with javascript

[QtVc.negative_rowGP]
name = "Quotation negative GP"
cube_name = "HansaWorld Quotations"
data_type = "decimal"
dimension_name = "Quotation"
level_name = "Quotation Item"
measure = true
javascript_code ='''
if (doc.rows) {
  doc.rows.forEach(function(row){
    row.negative_rowGP = String(Number(row.rowGP) * -1);
  })
}
'''

CODE

If the data need to be linked to another date field then use custom_date_measure_group to define the name for the measure group and also the prefix for the duplicated measures.

custom_date_measure_group = 'Planned'

CODE

Example for both record and item level below.

[SHVc.Planned_Send_date]
rest_api_field = 'PlanSendDate'
name = 'Planned Send Date'
dimension_name = 'Delivery'
cube_name = 'HansaWorld Deliveries'
custom_date_measure_group = 'Planned'

[SHVc.Plan_Send_DateRow]
name = 'Item Planned send Date'
dimension_name = 'Delivery'
level_name = 'Delivery Item'
cube_name = 'HansaWorld Deliveries'
custom_date_measure_group = 'Planned'
javascript_code ='''
  if (doc.rows && doc.PlanSendDate) {
    doc.rows.forEach(function(row){
      row.Plan_Send_DateRow = doc.PlanSendDate;
    })
  }
'''

CODE

Add additional custom field definitions as needed in advanced settings text area field and then click Save.

3. Import the custom field

Finally, Edit the HansaWorld source application again and in "Cube properties" section select your new defined custom field for import.

Save the application settings and start the import. After the import is finished start using your property, measure or dimension in your reports.

In case of any questions please contact support@flex.bi for more information.

Building a cube from advanced settings for custom fields

Currently Tilde and Horizon application cube structure is built using custom field definitions and it can be modified in the application's Advances settings section.

The predefined custom fields are loaded from a file on the server, that can be found here:

.../config/advanced_settings/{application_type}_custom_fields.toml

The following are custom field parameters used especially for defining cube structure:

Parameter Name	Example	Description
default_custom_field	default_custom_field = true	This parameter specifies that this field is loaded from the default custom fields file and is required for building the standard cube structure. A custom field with this parameter enabled can't be de-selected for import. Also, if this parameter is set, then the custom field definition is not validated.
dimension_in_separate_table	dimension_in_separate_table = true	This parameter specifies that the particular dimension will be created in a separate table. This is a mandatory parameter for new dimension creation. This parameter can be used together with the following additional parameters, to define dimension structure and binding: `key_field, name_field, source_id_field`. Or with the parameter `property`, to add a property to the dimension.
key_field	key_field = true	This parameter specifies that this field will be used as the key field for binding the dimension to the measures and as the first part of dimension member's name ("`key_field` - `name_field`").
name_field	name_field = true	This parameter specifies that this field will be used as the second part of dimension member's name ("`key_field` - `name_field`").
level_name	level_name = "Invoice Item"	If a dimension has multiple levels `level_name` parameter should be used to specify the level. This parameter should not be used with dimensions that have only 1 level.
source_id_field	source_id_field = true	This parameter specifies that this field should be used for binding the dimension to the measures instead of the key field. For example, when importing data from a relational database, this could be the primary key of the table that is used for dimension creation.
rest_api_field	rest_api_field = 'T.NAME'	If an REST API field contains a full stop characters (“.“) or the same REST API should be used twice you can use rest_api_field to define the REST API field name instead. If rest_api_field is used then in order to change the returned value for the whole custom field using Javascript the name in rest_api_field needs to be referenced instead as well.
for_custom_dimension	for_custom_dimension = 'Customer'	This parameter specifies that this field from the measures register or table will be used for binding the measures to the particular dimension. This parameter should be used together with the following parameters, to specify the particular use of the custom field: `key_field, name_field, source_id_field`.
for_custom_dimension_level	for_custom_dimension_level = 'Customer'	This field is an addition to `for_custom_dimension` field and can be used to also specify a level for binding measures to the particular dimension.
import_sort_date	import_sort_date = true	This parameter specifies that this field will be used for limiting data requests from the particular register or table. For instance, it will be used to get data starting from a particular start date, to rewrite data for a specified refresh period and also for incremental import of only those data lines that have been changed since the last import run.
skip_request	skip_request = true	This parameter specifies that the REST API request should be skipped for this field. This can be useful when the field has been already requested in another custom field or the field is calculated with Javascript.
bind_field_name	bind_field_name = 'status_code'	This parameter specifies what custom field's register code should be used for binding measures to another dimension. With this parameter the same field can be referenced multiple times.
matrix_field	matrix_field = true	This parameter specifies that the information should be taken from matrix level of a record for creating a dimension.

In the example below, a Customer dimension with two hierarchy levels Customer type and Customer is defined. This dimension contains both key and name fields for both levels and is linked to the measures (Transaction dimension) using customer_code key field. In this example, customer_name is defined both for Customer and Transaction dimension import. This allows for Customer dimension member creation from the Transaction record in case a matching customer code is not found in the Customer dimension.

[TdmGramatSL.customer_code]
default_custom_field = true
name = 'Transaction Customer Code'
dimension_name = 'Transaction'
cube_name = 'Horizon Financials'
for_custom_dimension = 'Customer'
bind_field_name = 'customer_code'
rest_api_field = 'D.K.KODS'

[TDdmKlBaseSar.customer_type_code]
default_custom_field = true
name = 'Customer Type Code'
dimension_name = 'Customer'
level_name = 'Customer Type'
cube_name = 'Horizon Financials'
key_field = true
rest_api_field = 'K.KTIPS'
dimension_in_separate_table = true

[TDdmKlBaseSar.customer_type_name]
default_custom_field = true
skip_request = true
rest_api_field = 'T.NOSAUK'
name = 'Customer Type Name'
dimension_name = 'Customer'
level_name = 'Customer Type'
cube_name = 'Horizon Financials'
name_field = true
dimension_in_separate_table = true
javascript_code = '''
cust_type = doc.K_KTIPS;
switch(cust_type) {
  case "0":
    doc.T_NOSAUK =  "Active";
    break;
  case "1":
    doc.T_NOSAUK =  "Potential";
    break; }
'''

[TDdmKlBaseSar.customer_code]
default_custom_field = true
name = 'Customer Code'
dimension_name = 'Customer'
cube_name = 'Horizon Financials'
rest_api_field = "K.KODS"
key_field = true
dimension_in_separate_table = true

[TDdmKlBaseSar.customer_name]
default_custom_field = true
name = 'Customer Name'
dimension_name = 'Customer'
cube_name = 'Horizon Financials'
name_field = true
rest_api_field = "K.NOSAUK"
dimension_in_separate_table = true

CODE

In the example below, a Currency dimension is created and linked to the measures (Transaction dimension), using a source id field.

[FinancialDocLine.currency_id]
default_custom_field = true
name = 'Transaction Currency ID'
dimension_name = 'Transaction'
cube_name = 'Jumis Financials'
for_custom_dimension = 'Currency'
bind_field_name = 'currency_id'
rest_api_field = 'FinancialDocLine.CurrencyID'

[Currency.currency_code]
default_custom_field = true
name = 'Currency Code'
dimension_name = 'Currency'
cube_name = 'Jumis Financials'
rest_api_field = "CurrencyCode"
key_field = true
dimension_in_separate_table = true

[Currency.currency_name]
default_custom_field = true
name = 'Currency Name'
dimension_name = 'Currency'
cube_name = 'Jumis Financials'
name_field = true
rest_api_field = "Description"
dimension_in_separate_table = true

[Currency.currency_id]
default_custom_field = true
name = 'Currency ID'
dimension_name = 'Currency'
cube_name = 'Jumis Financials'
source_id_field = true
rest_api_field = "CurrencyID"
dimension_in_separate_table = true

CODE

In the example below field FinancialDocLine.status_code has to be used twice so bind_field_name field is used to reference the same field twice.

[FinancialDocLine.status_code]
rest_api_field = 'FinancialDoc.Disbursement'
default_custom_field = true
name = 'Disbursement Status Code'
dimension_name = 'Disbursement Status'
cube_name = 'Jumis Financials'
dimension_in_separate_table = true
key_field = true

[FinancialDocLine.status_name]
skip_request = true
default_custom_field = true
name = 'Disbursement Status Name'
dimension_name = 'Disbursement Status'
cube_name = 'Jumis Financials'
dimension_in_separate_table = true
name_field = true

[FinancialDocLine.a_disbursement_flag]
rest_api_field = 'FinancialDoc.Disbursement'
default_custom_field = true
name = 'Transaction Document Disbursement Flag'
dimension_name = 'Transaction'
for_custom_dimension = 'Disbursement Status'
cube_name = 'Jumis Financials'
bind_field_name = 'status_code'
javascript_code = '''
if (doc.FinancialDocDisbursement === true) 
  {doc.FinancialDocDisbursement = 'true'}
else 
  {doc.FinancialDocDisbursement = 'false'};
'''

CODE

In the example below, a combination of Price List, Price and Customer registers are used to create 3 level hierarchy.

[PLVc.F_PriceList]
name = 'Price List Code'
dimension_name = 'Price'
cube_name = 'Hansaworld Invoices'
level_name = 'Price list'
rest_api_field = "PLCode"
key_field = true
dimension_in_separate_table = true

[PLVc.F_PriceListName]
name = 'Price List Name'
dimension_name = 'Price'
cube_name = 'Hansaworld Invoices'
level_name = 'Price list'
rest_api_field = "PLCode"
name_field = true
dimension_in_separate_table = true

[PLVc.F_PriceItemCode]
name = 'Price Item Code'
dimension_name = 'Price'
cube_name = 'Hansaworld Invoices'
level_name = 'Item'
rest_api_field = "ArtCode"
key_field = true
dimension_in_separate_table = true

[PLVc.F_PriceItemName]
name = 'Price Item Name'
dimension_name = 'Price'
cube_name = 'Hansaworld Invoices'
level_name = 'Item'
rest_api_field = "Comment"
name_field = true
dimension_in_separate_table = true

[PLVc.F_PriceCustomerCode]
name = 'Price Customer Code'
dimension_name = 'Price'
cube_name = 'Hansaworld Invoices'
level_name = 'Customer'
rest_api_field = "CustCode"
key_field = true
dimension_in_separate_table = true

[PLVc.F_PriceCustomerName]
name = 'Price Customer Name'
dimension_name = 'Price'
cube_name = 'Hansaworld Invoices'
level_name = 'Customer'
rest_api_field = "CustCode"
name_field = true
dimension_in_separate_table = true

[IVVc.PriceListProperty]
name = "Price list Property"
cube_name = 'Hansaworld Invoices'
data_type = "string"
dimension_name = 'Invoice'
level_name = 'Invoice'
rest_api_field = 'PriceList'

[IVVc.RowPriceListCode]
name = 'Invoice Price List Code'
dimension_name = 'Invoice'
cube_name = 'Hansaworld Invoices'
level_name = 'Invoice Item'
for_custom_dimension = 'Price'
for_custom_dimension_level = 'Price list'
bind_field_name = 'F_PriceList'
javascript_code = '''
if (doc.rows) {
  doc.rows.forEach(function(row){
    if (doc.PriceList){
      row.RowPriceListCode = doc.PriceList;
    }
  })
}
'''

[IVVc.ArtCode]
name = 'Invoice Item Code'
dimension_name = 'Invoice'
cube_name = 'Hansaworld Invoices'
level_name = 'Invoice Item'
for_custom_dimension = 'Price'
for_custom_dimension_level = 'Item'
bind_field_name = 'F_PriceItemCode'

[IVVc.RowCustCode]
name = 'Invoice Customer Code'
dimension_name = 'Invoice'
cube_name = 'Hansaworld Invoices'
level_name = 'Invoice Item'
for_custom_dimension = 'Price'
for_custom_dimension_level = 'Customer'
bind_field_name = 'F_PriceCustomerCode'
javascript_code = '''
if (doc.rows) {
  doc.rows.forEach(function(row){
    if (doc.CustCode){
      row.RowCustCode = doc.CustCode;
    }
  })
}
'''

CODE