You can add six types of form elements to a spreadsheet: ComboBox, List, CheckBox, Button, DataPicker and Label. These elements are available in the Insert tab in Jedox Web.
Each element can be formatted to suit your document.
Format Control Dialog
After selecting a form element, the Format Control dialog is displayed. The following tabs are available: General, Size & Position, Layout Options, and Font.
- In the General tab, you can define the name, the data source or value and the target of the form element.
- In the Size & Position tab, you can enter pixel data for the size and the position and “TRUE” or “FALSE” for visibility.
- In the Layout Options and Font tabs, you can choose the formatting options. Note that font sizes for ComboBoxes, lists, and DatePicker form elements cannot be changed.
Notes on 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.
In case a Combobox is used, and the current OLAP value shown in the target cell is not found in the Combobox list when the spreadsheet with the list is loaded, the Combobox will initialize to the first value in the list, and also set this value of the Combobox in the target cell and thus in OLAP.
- It is not recommended to use a Listbox in combination with a Freeze Pane. This can cause the elements to be incorrectly displayed when the expanded the Listbox crosses the Freeze Pane.
- With the exception of labels, form elements will not be shown in PDF exports of a report.
A Combobox can use a formula, a subset or an ODBC query as a source:
The target can be a cell/range, a named range or a variable.
For example, the named range Combobox1 has a subset of the dimension “Regions” of the database “Demo”:
In the case above the named range Combobox1 has the value “Switzerland”.
To use a Combobox form element in combination with a DynaRange, see Report with ComboBox and DynaRanges.
Learn more about the ComboBoxes in this free online course from Jedox Academy! Just enter your email address when prompted, and the video will begin playing.
The List form element behaves in a similar way as the ComboBox form element, but it also allows any multiselection of elements. The screenshot below shows 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.
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.
Large dimensions in the List form element will appear with paging controls for navigation (see screenshot below). Note that multiselection across pages in the List is currently not possible.
In Layout Options tab you can also select whether the list should be Collapsible or Collapsed.
To use a List form element in combination with a DynaRange, see Report with List and DynaRanges.
You can create a CheckBox in any cell in a worksheet by selecting Tools > Form Elements > CheckBox… The following dialog box appears:
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).
Notes on CheckBox:
- 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.
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:
You can create a button in any worksheet by going to Tools > Form Elements > 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 Icon tab, where you can select an icon/picture for the button.
You can create a date button in any spreadsheet cell by selecting Tools > Form elements > 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.
Notes on DatePickers:
- 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 29th, 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 in it. To ensure compatibility for all other dates before March 1st, 1900, the date value “60” is interpreted as March 1st, 1900 by the Datepicker control, i.e. the same date as the value “61”.
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:
Note: the text, visibility, and position of the label are formula-dependent.