You can add five types of form elements to a spreadsheet: ComboBox, List, CheckBox, Button, and DataPicker. These elements are available in the Tools menu of Jedox Web.
Each element can be formatted to suit your document.
Form Control Dialog
After selecting a form element, you receive the Format Control dialog with several tabs: General, Size & Position, Layout Options, and Font.
- In the General tab you can define the name, the 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 will find many more formatting options.
Notes on Form Elements:
- Form elements will usually not be 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.
A Combobox can use a formula, a subset or an ODBC query as source:
The target can be a cell/range, a named range or a variable.
As example is here shown the named range “Combobox1” with a subset of the 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.
As of Jedox Version 7.0, you can select the List form element. It provides a similar behavior as the Combobox form element, but it allows additionally any multiselection of elements.
Multiselection of list elements is only possible if in Layout Options tab the value of the Multiselect option is “TRUE”.
In this case there is a short “wait” period after the user clicks an element in the list, of ca. 1 second. Only after this period, the result of the selection is sent.
This is done to prevent unnecessary recalculation of the worksheet while the user is still selecting elements.
In Layout Options tab you can also set checkmarks for Collapsible and for 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 will appear:
When the Value setting is “Checked”, the CheckBox is given the value “True”; when “Unchecked”, the value is “False”. You can select 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:
- With entry of “CheckBox1” as “Named Range”, the name is known in the current file; with entry of “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 specify wheter the background of the CheckBox should be transparent or not. This you can define separately for the designer mode and the user mode. By right-clicking on the CheckBox, you get the following context menu:
The command “Tools – Form Elements – Button…” allow you to create a button in any worksheet. You can also assign a macro to the button by right-clicking on it.
The Button form element has an additional Icon tab, where you can select an icon/picture for the button.
With the command “Tools – Form elements – Datepicker” you can create a date button in any spreadsheet cell. 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. Note that when you use a formula as source for the Datepicker, the output needs to be a valid timestamp (integer) or else the display of the Datepicker form elements might be wrong or empty.
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 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”.