Comboboxes, Lists, Checkboxes, Buttons, DatePicker, and Labels

You can add six types of form elements to a spreadsheet: Combobox, List, Checkbox, Button, DataPicker, and Label. These elements can be found in the Insert menu in Jedox Web.

Format Control Dialog

Each form element can be customized to fit your document’s needs. When you select a form element, the FORMAT CONTROL dialog appears, offering the following tabs: General, Size & Position, Layout Options, Font, and Actions.

General: Define the element's name, data source or value, and target.
Size & Position: Set the element’s dimensions and position using pixel values, and control its visibility with TRUE or FALSE.
Layout Options & Font: Adjust formatting settings. Note that font sizes cannot be modified for Comboboxes, Lists, and DatePicker elements.
Actions: Assign a specific action that will be triggered when the form element is used.

Combobox

A Combobox can retrieve data from a formula, a Subset, or an ODBC query.

If you're using a Subset from a large dimension (over 1,000 elements), enable the Large Subset checkbox. This improves performance by loading elements dynamically as you scroll, rather than displaying the entire list at once.

The target can be a cell, a range, a named range, or a variable. For example, the named range Combobox1 represents a subset of the Regions dimension in the Demo database.

Combobox screenshot

In the example above, the named range Combobox1 contains the value "Switzerland". You can also search for a specific value by typing directly into the Combobox.

Note that if an object's target is a cell or range, it will not update automatically when the worksheet is renamed. After renaming the sheet, you must manually edit the object to reference the new sheet name in the target range.

If a Combobox is used and the OLAP value in the target cell is not present in the Combobox list when the spreadsheet loads, the Combobox will default to the first value in the list. This value will then be set in both the target cell and OLAP.

For guidance on using a Combobox form element with a DynaRange, refer to the Example: Report with Combobox and DynaRanges.

List

The List form element functions similarly to the Combobox form element and also supports multiple selections. The screenshot below illustrates an example of a List element in a spreadsheet.

Multiselection of List elements is only possible if, in the Layout Options tab the value of the Multiselect option is “TRUE” (which is the default setting). When enabled, there is a short waiting period of approximately 1 second after you click an element in the List. After this period, the result of the selection is sent. This delay prevents unnecessary recalculation of the worksheet while you are still selecting elements.

Displaying the selected values in Lists

You can display the selected values in "List1" in an array as named range definition as in the screenshot above or as variable definition shown in the screenshots below. In both cases, the different values are only displayed if the array cells are in a row. If the array cells are in a column, only the first value will be displayed in all cells of the array.

In the Layout Options tab, you can also select whether the List should be Collapsible or Collapsed.

It is not recommended to use a List in combination with a Freeze Pane. This can cause the elements to be incorrectly displayed when the expanded List crosses the Freeze Pane.

To use a List form element in combination with a DynaRange, see Example: Report with List and DynaRanges.

Checkbox

You can create a Checkbox in any cell in a worksheet by selecting Insert > Checkbox.

When the Value setting is Checked, the CheckBox is given the value "True"; when it is Unchecked, the value is "False". You can choose to have the response value displayed in a target cell of your choice or have it called up with the variable names, such as a variable in an IF function: =IF(CheckBox1,A1,A2).

In the Layout tab, you can choose whether the background of the Checkbox should be transparent or not. You can define this separately for the designer mode and the user mode. By right-clicking on the Checkbox, you get the following context menu:

By entering "Checkbox1" as named range, the name is known in the current file; by entering "Checkbox1" as a variable, the name is known throughout the Jedox Web session.

If "Checkbox1" is registered as a named range and later deleted, then the defined name "Checkbox1" has the last selected entry. The defined name "Checkbox1" is not deleted when you delete the Checkbox1.

Button

You can create a button in any worksheet by selecting Insert > Button.

You can also assign a macro to the button by right-clicking on it once you create it, and selecting Assign macro...

The Button form element has an additional Thumbnail tab, where you can select a image for the button. Allowed image file formats include gif, jpg, jpeg, and png up to 400x300 pixels. Read also Custom Thumbnails in Reports.

Note that the Button form element does not support a transparent background color. Using the "No color" setting as the background color in the "Layout" tab will reset the background color to the default color.

DatePicker

You can create a date button in any spreadsheet cell by selecting Insert > DatePicker. For this form element, you can select a fixed date or use a formula; set it as a "named area" or as a "variable"; or assign a macro to it by right-clicking on it and selecting Assign macro...

When you use a formula as a source for the DatePicker, the output needs to be a valid timestamp (integer); otherwise, the display of the DatePicker form elements might be wrong or left empty. The timestamp value can only be reset to another timestamp; it cannot be left empty. If no changes are made, it will retain the last value selected.

For historic and compatibility reasons, many spreadsheet engines (such as Microsoft Excel) define the year 1900 as a leap year, even though it was not. Thus, the date timestamp "60" will return the date Feb 29, 1900, when formatted as a date in a cell. The Jedox Web Spreadsheet behaves in the same way.

The DatePicker control in Jedox Web, however, does not support this. The year 1900 is not defined as a leap year. To ensure compatibility for all other dates before March 1st, 1900, the date value "60" is interpreted as March 1, 1900, by the DatePicker control, i.e. the same date as the value "61".

Label

You can make free-form annotations on a report by adding labels. The label can display either a text or the result of a formula. You can also add more details about it in a tooltip. In the example below, the label will display the sum of the values in cells B3 to B5:

The following result is displayed on the spreadsheet:

The displayed text and the visibility of the label are formula-dependent. Using the SHOWPICT() formula on the label to display an image inside the label is not supported.

If a numeric value is shown on the label which has more than 15 decimals digits, these digits will be truncated when the report is exported as PDF.

Considerations when using Form Elements:

Form elements are not moved dynamically in the spreadsheet if a row / column is hidden or if the width / height of rows / columns changes. However, if the form contains a DynaRange, the form element may be moved when the DynaRange is expanded / collapsed.
If a spreadsheet cell contains a PALO.DATA formula and a value is set in this cell via a form element, the formula will not be overwritten. Instead, the set value is sent to OLAP for writeback.
With the exception of labels, form elements will not be shown in PDF exports of a report.
Form names must be valid. See Naming Conventions for Jedox Objects.
You must use the syntax of the current locale when entering the formula, as the "Formula" source is displayed in the current locale. However, if the source type is changed, for example, from "Subset / ODBC" to "Formula", the formula will be displayed by default in English. To use the correct argument separator for your current locale, the argument must be changed manually. If you change the source type using the English locale, and then save and open the workbook using, e.g., the German locale, the formula will be displayed with the German syntax.
Combining both an Action and the execution of a macro on Form Elements is not supported. If an Action is defined on a form element, any macros also assigned with it will not be executed anymore when clicked/used.

Updated February 20, 2025