You can use several optional parameters in your Workbook in order to realize specific functions. Please remember that all named ranges in data1.io must be defined on a sheet (and not workbook) level.

The most important

The following optional parameters can be defined to control the bottom and sum level elements:

• data1_CELLS01_DIMxxx_Key
• data1_CELLS01_DIMxxx_Attribute
• data1_CELLS01_IsSumLevel
• data1_CELLS01_DIMxxx_Type
• data1_CELLS01_DIM001_Path

The following optional parameters can be defined to control the loading behaviour:

• data1_ReadFirstElement
• data1_TreeFilter

The following optional parameters can be used to control mandatory entries:

• data1_Write_Validation (_1, _2, etc.)
• data1_Write_ValidationMessage (_1, _2, etc.=)

The following optional parameters can be defined to trigger certain actions:

• data1_Trigger_ReadOnChange
• data1_Trigger_TreeSelectionAndReadOnChange(_x)
• data1_Trigger_RecalculateAndApplyFilterOnChange

The following optional parameters can be used to control the formatting of changed cells:

• data1_ChangedCellFormat

The following optional parameters can be used to control the additional information:

• data1_CurrentUserKey
• data1_IsWriting
• data1_Write_AlwaysOn

How to use the bottom and sum level parameters

data1_CELLS01_DIMxxx_Key

You can use this optional parameter to get the Key of the currently selected element in the dimension tree (addtionally to the obligatory name of the element).

data1_CELLS01_DIMxxx_Attribute

With this parameter you get the name of the field ("level") of the currently selected element in the dimension tree.

This parameter is important to build dynamic element list reports (the parameter is used in the "array format" there). It was also used to build the filter statement for dynamic tables, but this parameter was replaced by the newer "data1_CELLS01_DIMxxx_Key_Path" parameter which is the better alternative for this field of application now.

data1_CELLS01_IsSumLevel

This is very simple: double clicking an element in the dimension tree will deliver a "0" or a "1":

• 0 = Bottom Level
• 1 = Sum Level

You can use this parameter for a level-specific configuration of the R/W control panel in order to realize advanced aggregation effects.

data1_CELLS01_DIMxxx_Type

Use this parameter to get the element type (UID) of the selected element in the dimension tree. Please be aware that this parameter delivers null/blank when selecting a sum level element.

data1_CELLS01_DIM001_Path

We have created a new named range “Path” in order to create a breadcrumb display of the currently selected item in your workbook.

The parameter delivers the path of the selected element. This path can be combined with the parameter data1_CELLS01_DIM001 to establish a breadcrumb display (green box):

How to use the loading parameters

data1_ReadFirstElement

This parameter rules the behaviour when entering a sheet the first time after loading the workbook.

0 = do not read

1 = default = the first top element (from the tree) will be read on entering the sheet. Used to read directly the current aggregated sums in order to get an overview of the status of the data collection.

2 = the first bottom element (from the tree) will be read on entering the sheet. Used to jump directly to the first bottom element in order to start immediately with data entry.

3 = the predefined element from the named range "data1_CELLS01_DIM001" (from the workbook) will be read -> if this element is not found / not valid, then no READ is executed.

Please note that this parameter replaces the parameters "data1_ReadFirstTopElement" and "data1_ReadFirstBottomElement".

data1_TreeFilter

This parameter rules the sheet-specific content of the dimension tree after entering the sheet. The most common filter is the element type filter, but you could use filters on any attribute in the dimension tree table:

• DIM001.DIMTYPE.UID = 11
• DIM001.DIMTYPE.UID = 11 OR DIM001.DIMTYPE.UID = 12
• etc.

How to use the mandatory entries parameters

data1_Write_Validation

Use this parameter to define mandatory entries in your workbook.

• data1_Write_Validation
  0 = default = Writeback is open
  1 = Writeback is blocked
  
  
• data1_Write_ValidationMessage
  Use this parameter to define the message for the user, which is displayed if the first parameter has value "1" and the Write button is pressed.
  
  
• Multi Messages with _1, _2, etc.
  Use named ranges couples like this to define as many validation rules within the same sheet as needed:
  
  data1_Write_Validation_1
  data1_Write_ValidationMessage_1
  
  data1_Write_Validation_2
  data1_Write_ValidationMessage_2
  
  etc.

How to use the trigger parameters

data1_Trigger_ReadOnChange

Any change in a cell in this name range will cause a read process. Can be used for dropdowns in your workbook to trigger a refresh.

Please note that this parameter was defined as data1_CELLS01_TriggerReadOnChange until November 2022.

data1_Trigger_TreeSelectionAndReadOnChange(_x)

With this named range you can select a dimension tree key in the dropdown, pass it to the tree and then trigger the read process (= refresh).

More exactly, you can define a variable number of named ranges starting with data1_Trigger_TreeSelectionAndReadOnChange, all of them will trigger the described process.

Please note that this parameter was defined as data1_CELLS01_TriggerTreeSelectionAndReadOnChange until November 2022.

data1_Trigger_RecalculateAndApplyFilterOnChange

With this named range you can define one or more cells of the sheet as trigger cells to execute a RECALCULATE of the formulas on the sheet and a refresh of the ROW FILTER ("auto filter"). The second feature is similar to the automatic "change trigger" for the visibility settings (see there).

How to use the changed cells parameter

data1_ChangedCellFormat

With this named range you can define, how the background and text color should be changed in the Web Client, after a collector user has entered a value in a cell.

Please note that this parameter applied to all CELLS-Areas (CELLS01, CELLS02, etc.) within a sheet.

How to use the additional information parameters

data1_CurrentUserKey

Delivers the e-mail adress of the currently logged in user. Can be used to setup a user specific security within the workbook.

data1_IsWriting

This parameter can have two values:

0 = no Write process is currently executed
1 = Write process is currently executed

You can use this parameter e.g. to set alternative dimension keys for the WRITE process other than for the READ process. The current value of this parameter is derived from all CELLS0x areas.

data1_Write_AlwaysOn

This parameter can have two values:

0 = normal behaviour

1 = write button will be forced

You can use this parameter to force the availability of the write button although otherwise the button would be deactivated (e.g. presence of exclusively read-only cells, presence of exclusively level queries, etc.). Please be aware that the write button can be forced only on bottom level elements and within the valid period of the current workflow.