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]
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"
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"
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"
Dimension level name
The name of the level within the specified dimension, where the field should be added e.g.
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.
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.
Additional options
If you would like to import custom field as additional dimension then add
dimension = true
If you would like to import custom field as a measure then add
measure = true
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);
})
}
'''
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"]
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
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'
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
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);
})
}
'''
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'
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;
})
}
'''
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: |
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
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
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'};
'''
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;
}
})
}
'''