Advanced Settings for Custom Field import

Advanced settings - Step by Step

With Advanced settings for Custom Fields functionality you can:

1) create new data structures :

Add New Dimensions
Add new Properties
Add new Measures
Add new Hierarchies
Add new Data cubes (See Example HERE)

2) define custom views and advanced sharing

Share measures between data cubes
Define Custom Drill Options

What is a considered as a "custom field" for flex.bi ?

field in Source system that is not imported in flex.bi by default
For example : Custom Field that is customer specific

transaction Register Field in Source system that is not imported in flex.bi by default,
For example : Custom Module that is customer specific and is based on customer register
any addition data structure that exists in Source system that needs be used in flex.bi as dimension/ Measures or property etc. in reports

Where to find Advanced settings ?
Advanced settings is a section in flex.bi applications that allow you to define custom data input for flex.bi data cubes, create new data cubes and adjust them.

How to use this function?

STEP 1

Define what you want to add in “Advanced settings” sectioning XXX language.
Custom field Code has to be described in blocks and organised based on type

STEP 2

Select in Cube Properties section — Custom Fields section what you want to import and how.

Watch a Demonstration here :

Advanced setting key components & detailed instructions for Standard ERP

Below is a description of necessary parameters to define simple custom fields and a description of the process by steps.

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 HansaWorld 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

CODE

[register_code.register_rest_api_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.

CODE

name = "Price"

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 or if you would like to create a new cube you can use define the name here.

CODE

cube_name = "HansaWorld Invoices"

Dimension name in flex.bi for the register

Name of the dimension that should be created or name of the dimension where this field should be taken from when bind_field is used (more on that later).

CODE

dimension_name = "Invoice"

Dimension level name

The name of the level within the specified dimension, where the field should be added e.g. or taken from when bind_field is used (more on that later).

CODE

level_name = "Invoice Item"

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.

CODE

data_type = "string"

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.

When defining fields for key_field, name_field, bind_field flex.bi will recognise that they will be strings and this parameter can be skipped.

Additional options

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

CODE

dimension = true

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

CODE

measure = true

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

CODE

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

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

CODE

cubes_for_measure_sharing = ["HansaWorld Invoices"]

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.

CODE

file_field = 4

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.

CODE

rest_api_field = 'T.NAME'

Example of custom dimension definition

CODE

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

Example of custom measure definition with javascript

CODE

[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);
  })
}
'''

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.

CODE

custom_date_measure_group = 'Planned'

Example for both record and item level below.

CODE

[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;
    })
  }
'''

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.

Defining dimension in a separate table

If you would like to have a dimension definition that has a hierarchy, has a key and name separately or you would like to add additional properties to the dimension then you can use an more advanced definition. The definition consists of 3 parts:

A field that contains the keys for dimension elements
A field that contains the name for dimension elements (optional)
A field that binds the dimension to measures

Key field

Using the custom field definition and an additional parameter key_field you can define that this field will be used as a key for the new dimension.

CODE

[INVc.item_code]
name = 'Item Code'
dimension_name = 'Item'
cube_name = "HansaWorld Invoices"
rest_api_field = "Code"
key_field = true

Name field

Using the custom field definition and an additional parameter name_field you can define that this field will be used as a name for the new dimension.

CODE

[INVc.item_name]
name = 'Item Name'
dimension_name = 'Item'
cube_name = "HansaWorld Invoices"
name_field = true
rest_api_field = "Name"

Bind field

While the dimension members will be imported with the key_field and name_field definitions a bind field also needs to be defined so that flex.bi can know what field from the register where measures is imported will be linked to this new dimension. For this 2 parameters have to be used: bind_field_name and for_custom_dimension.

bind_field_name is the field's rest api code that we defined previously (the "item_code" from this "[INVc.item_code]") and the for_custom_dimension is the name of the dimension we want to link the measures import to.

If the bind field from the measures import register is available only on the item level level_name parameter can be used to specify this.

CODE

[IVVc.item_code]
name = 'Item Code'
dimension_name = 'Invoice'
level_name = 'Invoice Item'
cube_name = "HansaWorld Invoices"
for_custom_dimension = 'Item'
bind_field_name = 'item_code'
rest_api_field = 'ArtCode'

Available parameters for Advanced Settings

Below you can find a list of all the available custom field parameters.

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.
ordinal_field	ordinal_field = true	This parameter specifies that the field should be used as an ordinal column for dimensions in a separate table.
credit_debit_dimension_column	credit_debit_dimension_column = true	This parameter specifies that the values for the particular custom field should be taken from 2 separate custom fields with name prefixed `credit_` and `debit_`. Works only for specific cubes (Jumis Financials, Horizon Financials).
for_standard_dimension	for_standard_dimension = "Customer"	Similar to `for_custom_dimension` this parameter specifies that this field from the measures register or table will be used for binding the measures to the particular dimension with the difference that this parameters should be used for already standard existing dimensions that have been defined without custom fields.
for_standard_dimension_level	for_standard_dimension_level = "Customer"	Similar to `for_custom_dimension` parameter and can be used to also specify a level for binding measures to the particular dimension with the difference that this parameters should be used for already standard existing dimensions that have been defined without custom fields. This is optional if the data is linked to the lowest hierarchy level.
property_with_mdx	property_with_mdx = true	Automatically creates a calculated measure for the imported property.
drill_through_return	drill_through_return = true	drill_through_return parameter can be used to specify that the dimension should be included in the result as a column when "Drill through cell" is used on a cell.
drill_through_default_measure	drill_through_default_measure = true	`drill_through_default_measure` parameter can be used to specify that the measure should be included in the result as a column when "Drill through cell" is used on a cell with a value from a calculated member.
drill_through_dimension_level	drill_through_dimension_level = true	`drill_through_dimension_level` parameter can be used to specify that the dimension will be available in the selection when using "Drill through" option on a cell.
default_measure	default_measure = true	`default_measure` parameter can be used to specify that the measure should be enabled by default when a new, empty report is created.
rest_api_item_field	rest_api_item_field = "TRANSORDER_P"	This field can be used to specify how to reference item level from a record correctly. Using this field once is sufficient as this will overwrite the item level name reference from request for the same dimension for other custom fields as well.
rest_api_nested_field	rest_api_nested_field = "SHIPTO2"	This parameter can be used to specify a record sub-level that's field will be requested through the REST API for the register, and available for custom field definition on the record level. If the sub-level is an array then the first element from the array will be used.

Advanced Settings examples

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.

CODE

[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

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

CODE

[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

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.

CODE

[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'};
'''

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

CODE

[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;
    }
  })
}
'''

In the example below, account_code value will be populated from the credit_account_code for the credit transaction and from debit_account_code for debit transaction.

CODE

[FinancialDocLine.account_code]
skip_request = true
default_custom_field = true
name = 'Transaction Account Code'
dimension_name = 'Transaction'
cube_name = 'Jumis Financials'
for_custom_dimension = 'Account'
bind_field_name = 'account_code'
credit_debit_dimension_column = true

[FinancialDocLine.credit_account_code]
default_custom_field = true
name = 'Credit Account Code'
dimension_name = 'Transaction'
cube_name = 'Jumis Financials'
rest_api_field = 'CreditAccount.AccountCode'

[FinancialDocLine.debit_account_code]
default_custom_field = true
name = 'Debit Account Code'
dimension_name = 'Transaction'
cube_name = 'Jumis Financials'
rest_api_field = 'DebetAccount.AccountCode'
dimension_in_separate_table = true

In the example below a new cube is build for Jumis application build on Payment Orders:

CODE

[PaymentOrder.Amount]
default_custom_field = true
cube_name = 'Jumis Payment Order'
name = 'Amount'
dimension_name = 'Payment Order'
measure = true

[PaymentOrder.PaymentOrderID]
default_custom_field = true
name = 'Payment Order ID'
dimension_name = 'Payment Order'
cube_name = 'Jumis Payment Order'
source_id_field = true
rest_api_field = "PaymentOrderID"
dimension_in_separate_table = true

[PaymentOrder.PaymentOrderNo]
default_custom_field = true
name = 'Payment Order No'
dimension_name = 'Payment Order'
cube_name = 'Jumis Payment Order'
rest_api_field = 'PaymentOrderNo'
dimension_in_separate_table = true
key_field = true

[PaymentOrder.Comments]
default_custom_field = true
name = 'Payment Order Comments'
dimension_name = 'Payment Order'
cube_name = 'Jumis Payment Order'
rest_api_field = 'Comments'
dimension_in_separate_table = true
name_field = true

[PaymentOrder.PaymentOrderID2]
default_custom_field = true
name = 'Payment Order ID bind'
dimension_name = 'Payment Order'
cube_name = 'Jumis Payment Order'
for_custom_dimension = 'Payment Order'
bind_field_name = 'PaymentOrderID'
rest_api_field = 'PaymentOrderID'

[PaymentOrder.PaymentOrderDate]
default_custom_field = true
dimension_in_separate_table = true
name = 'Payment Order Date'
dimension_name = 'Payment Order'
cube_name = 'Jumis Payment Order'
rest_api_field = 'PaymentOrderDate'
data_type = 'date'
import_sort_date = true
for_standard_dimension = "Time"

[PaymentOrder.CurrencyID]
default_custom_field = true
name = 'Payment Order Currency ID'
dimension_name = 'Payment Order'
cube_name = 'Jumis Payment Order'
for_custom_dimension = 'Currency'
bind_field_name = 'currency_id'
rest_api_field = 'CurrencyID'

[PaymentOrder.PartnerID]
default_custom_field = true
name = 'Payment Order Partner ID'
dimension_name = 'Payment Order'
cube_name = 'Jumis Payment Order'
for_custom_dimension = 'Partner'
bind_field_name = 'partner_id'
rest_api_field = 'PartnerID'