Data Sources for Data Validation, DynaRanges, Comboboxes, and Lists

For Data Validation, DynaRanges, Comboboxes, and Lists, you can use several data sources, including Subsets, ODBC queries, or formulas. You have to ensure that the formula result returns the same structure as e.g. Subset formula, i.e. three-column matrix with element name in the first column, alias in the second and level/indent in the third.

For example, the macro function below can be used to return a source for a DynaRange. It will return a tree with Years as root node and QTR1 and QTR2 as children of it, with leaf elements Jan and Apr.

function my_dr_feeder(){
$monthDim = array(
            array('Year','Jahr',1),
            array('QTR1','Q1',2),
            array('Jan','J',3),
            array('QTR2','Q2',2),
            array('Apr','A',3)
            );

return $monthDim;
}

When used as a source of a DynaRange:

It will produce the following DynaRange when executed:

DynaRange based on Formula in a preview mode used in spreadsheet

The following example function would produce a Combobox, List, or Data Validation:

function my_cb_list_feeder(){
$monthDim = array('Year','Jahr',1,'QTR1','Q1',2,'Jan','J',3,'QTR2','Q2',2,'Apr','A',3);
return $monthDim;
} 

Note that unlike the DynaRange example above, this function does not require an additional array inside.

Constraint for the data sources formula and ODBC query

When using a source formula or ODBC query, there is one constraint to consider. If the number of elements in the data set is divisible by three, and the last value is numeric, the corresponding process assumes it is handling a Subset-like data structure. It will then only show the first, fourth, seventh, etc. element from the source data set. In some dialogs, you can select the option "Show Alias". It would then only show the second, fifth, eighth, etc. element as first alias, and the third, sixth, and ninth element etc. as second alias.

Note: in Jedox Web, the maximum result size of Subsets, such as in Comboboxes, is limited to 65,336 elements by default. To change this limit, contact Jedox Support. See also: Additional Spreadsheet Server Configuration.

Updated September 27, 2024