Info | ||
---|---|---|
| ||
With Advanced settings for Custom Fields functionality you can: 1) create new data structures :
2) define custom views and advanced sharing
What is a considered as a "custom field" for flex.bi ?
Where to find Advanced settings ? How to use this function? STEP 1 Define what you want to add in “Advanced settings” sectioning XXX language. STEP 2 Select in Cube Properties section — Custom Fields section what you want to import and how. |
Watch a Demonstration here :
Youtube macro width 300 aspectRatio 4 / 3 url https://www.youtube.com/watch?v=BOmOgM-TLV8 height 200
...
Advanced setting key components & detailed instructions for Standard ERP
...
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.).
...
Code Block |
---|
[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 Block |
---|
name = "Price" |
Cube Name
...
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 Block |
---|
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 Block |
---|
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 Block |
---|
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 Block |
---|
dimension = true |
If you would like to import custom field as a measure then add
Code Block |
---|
measure = true |
If you would like to do operation with the incoming data before fields are imported you can use custom javascript code
...
Code Block |
---|
rest_api_field = 'T.NAME' |
Example of custom dimension definition
Code Block |
---|
[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
...
that imports the row gross profit but turns the numbers negative:
Code Block |
---|
[QtVcQTVc.negative_rowGP] name = "Quotation negative GP" cube_name = "HansaWorld Quotations" data_type = "decimal" dimension_name = "Quotation" level_namerest_api_item_field = "Quotation Itemrows" measure = true javascript_code =''' if (doc.rows) { doc.rows.forEach(function(row){ row.negative_rowGP = String(Number(row.rowGP) * -1); }) } ''' |
...
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 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.
...
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 a more advanced definition. The definition consists of 3 parts:
...
Code Block |
---|
[INVc.item_code] name = 'Item Code' dimension_name = 'ItemItem2' cube_name = "HansaWorld Invoices" rest_api_field = "Code" key_field = true |
...
Code Block |
---|
[INVc.item_name] name = 'Item Name' dimension_name = 'ItemItem2' cube_name = "HansaWorld Invoices" name_field = true rest_api_field = "Name" |
...
If the bind field from the measures import register is available only on the item level level_name
parameter /row level rest_api_item_field
parameter can be used to specify this.
Code Block |
---|
[IVVc.item_code_bind] name = 'Item Code' dimension_name = 'Invoice' level_name = 'Invoice Item bind' cube_name = "HansaWorld Invoices" for_custom_dimension = 'ItemItem2' bindrest_api_item_field = "rows" bind_field_name = 'item_code' rest_api_field = 'ArtCode' |
...
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 key_field | key_field = 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 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. | ||||
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_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 Block |
---|
[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 Block |
---|
[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.
...
request_filter | request_filter = 'NDOK.D.T.KODS+eq+RREK' | This parameter can be used to add additional filters to the REST API link when requesting data. Currently only supported for Horizon application. |
item_level_from_header_key | item_level_from_header_key = 'NDOK_PK_DOK' | This parameter can be used as a key value to create the item level from the header level data. The key is used as the unique identifier for aggregating item level rows that belong to the same record. Currently only supported for Horizon application. |
cubes_for_measure_sharing | cubes_for_measure_sharing = ["HansaWorld Invoices", "HansaWorld Sales Orders"] | When defining a new custom field this field can be used to specify in what other cubes this measures should be shared in. |
source_cube_name | source_cube_name = 'HansaWorld Sales Orders' | Can be used to share measures from standard HansaWorld data cubes to other cubes. This parameter specifies from what cube the measure comes from. Should be used together with "source_measure_name". |
source_measure_name | source_measure_name = "Item base amount" | Can be used to share measures from standard HansaWorld data cubes to other cubes. This parameter specifies the name for the measure that should be shared. Should be used together with "source_cube_name". |
display_name | display_name = "URL" | This parameter can be used alongside "name" property to specify the property or measure name displayed in the data cube. This can be useful when the same field name has to be used multiple times (otherwise there would be an error that the same name can't be used twice). |
Skipping and deleting records
If it's necessary to skip or delete records during import, define a custom field "delete_and_skip". All the records that will have "true" value for this custom field will be skipped and also deleted if they have have been already imported. Example below.
Code Block |
---|
[TdmGramatSL.delete_and_skip] default_custom_field = true name = 'TransactionDelete Documentand Disbursement FlagSkip' dimension_name = 'Transaction' for_custom_dimension_name = 'Disbursement StatusTransaction' cube_name = 'JumisHorizon Financials' bind_field_nameskip_request = true property = 'status_code'true javascript_code = ''' if switch(doc.FinancialDocDisbursement === true)STATUSS) { case "1": break; default: {doc.FinancialDocDisbursementdelete_and_skip = 'true'} else ; {doc.FinancialDocDisbursement = 'false'}; ''' |
Advanced Settings examples
In the example below, a combination of Price List, Price and Customer registers are used to create 3 level hierarchy.
Code Block |
---|
[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 [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 [PLVc.F_PriceItemCode] name = 'Price Item Code' dimension_name = 'Price' cube_name = 'Hansaworld Invoices' level_name = 'Item' rest_api_field = "ArtCode" key_field = 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 [PLVc.F_PriceCustomerCode] name = 'Price Customer Code' dimension_name = 'Price' cube_name = 'Hansaworld Invoices' level_name = 'Customer' rest_api_field = "CustCode" key_field = 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 [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' cube_name = 'Hansaworld Invoices' for_custom_dimension = 'Price' for_custom_dimension_level = 'Price list' bind_field_name = 'F_PriceList' rest_api_item_field = "rows" javascript_code = ''' if (doc.rows) { doc.rows.forEach(function(row){ if (doc.PriceList){ row.RowPriceListCode = doc.PriceList; } }) } ''' [IVVc.ArtCode] name = 'Invoice Item Code' cube_name = 'Hansaworld Invoices' for_custom_dimension = 'Price' for_custom_dimension_level = 'Item' bind_field_name = 'F_PriceItemCode' rest_api_item_field = "rows" [IVVc.RowCustCode] name = 'Invoice Customer Code' cube_name = 'Hansaworld Invoices' for_custom_dimension = 'Price' for_custom_dimension_level = 'Customer' bind_field_name = 'F_PriceCustomerCode' rest_api_item_field = "rows" 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 Block |
---|
[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 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 Block |
---|
[TdmGramatSL.customer_code] default_custom_field = true name = 'Payment Order ID' dimensioncube_name = 'PaymentHorizon OrderFinancials' cube_name = 'JumisTransaction PaymentCustomer OrderCode' sourcerest_idapi_field = true rest_api_field = "PaymentOrderID" dimension_in_separate_table = true [PaymentOrder.PaymentOrderNo = 'D.K.KODS' for_custom_dimension = 'Customer' bind_field_name = 'customer_code' [TDdmKlBaseSar.customer_type_code] default_custom_field = true cube_name = 'Payment Order No' dimensionHorizon Financials' dimension_name = 'Customer' level_name = 'PaymentCustomer OrderType' cube_name = 'JumisCustomer PaymentType OrderCode' rest_api_field = 'PaymentOrderNo' dimension_in_separate_table = trueK.KTIPS' key_field = true [PaymentOrder.CommentsTDdmKlBaseSar.customer_type_name] default_custom_field = true cube_name = 'Horizon Financials'Payment Order Comments dimension_name = 'Customer' dimensionlevel_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.PaymentOrderDateCustomer Type' name = 'Customer Type Name' skip_request = true name_field = true javascript_code = ''' cust_type = doc.K_KTIPS; switch(cust_type) { case "0": doc.customer_type_name = "Active"; break; case "1": doc.customer_type_name = "Potential"; break; } ''' [TDdmKlBaseSar.customer_code] default_custom_field = true dimension_in_separate_table = true name = 'Payment Order Date' dimension_name = 'Payment Order' cube_name = 'JumisHorizon Payment OrderFinancials' rest_apidimension_fieldname = 'PaymentOrderDateCustomer' data_typename = 'dateCustomer Code' importrest_sortapi_datefield = true for_standard_dimension"K.KODS" key_field = "Time"true [PaymentOrderTDdmKlBaseSar.CurrencyIDcustomer_name] default_custom_field = true cube_name = 'Payment Order Currency IDHorizon Financials' dimension_name = 'Payment OrderCustomer' cube_name = 'JumisCustomer Payment OrderName' for_custom_dimension = 'Currency' bindrest_api_field_name = 'currency_id' rest_api_"K.NOSAUK" name_field = 'CurrencyID'true [PaymentOrder.PartnerIDTDdmKlBaseSar.customer_pvn_reg_no] default_custom_field = true cube_name = 'Payment Order Partner IDHorizon Financials' dimension_name = 'Payment OrderCustomer' cube_name = 'JumisVAT PaymentRegistration OrderNo' forrest_custom_dimension = 'Partner' bind_field_nameapi_field = 'partner_id' rest_api_field"K.PVN_REGNR" property = 'PartnerID'true |