The creation of a Named Range is described in Name Manager and is known in the accompanying Jedox spreadsheet. By contrast, variables are defined throughout the whole Jedox Web session of a given user. Among other capabilities, variables allow passing states (such as the selection of a ComboBox) easily between multiple Reports in a Web application.
Entries of Named Ranges are stored in spreadsheet cells and are called again with the file in the next Jedox Web session. Assigned global variable values are lost at the end of a Jedox Web session and reset in a new session to default values or to "no value" ( #VALUE!). Assigned private variable values are stored with the user settings and are called again in the next Jedox Web session of the same user.
Variable names are case sensitive, while Named Ranges are not case sensitive.
When changing the name of a Named Range which is used in the source formula of a DynaRange, the DynaRange formula must be manually updated afterward.
Creating global names
If you want to use Named Ranges from another workbook (Resource workbook), such as the file globals, in the workbook Test1, then you must follow these steps:
- Set globals as a resource: in the Designer, right-click on globals and Mark/Tag – As resource.
- Right-click Test1, select Properties, and open the Resources tab.
- Add globals as a resource. You can use the name Region from the worksheet Sheet1 of the workbook globals with the options 1 and 2 below.
Important: you cannot open two workbooks with the same name in the same session, even if the files have different paths or different upper- and lower-case spelling. This is also true for resource workbooks (e.g. globals). When defining workbook resources, avoid cyclic dependencies between resources. For example, a scenario where WB1 is a resource for WB2, WB2 is a resource for WB3, and WB3 is a resource for WB1 isn’t allowed.
You can call Region in any cell of Test1 with:
=[globals]Sheet1!Region
If there are special characters in the file or sheet name, set the entry before the exclamation mark in single quotes, e.g. =‘[globals-new]Sheet1‘!Region
Creating variables
Variables are syntactically defined by prefixing the "=@" characters to a string.
Option 1
By setting "=@variable1", you can create variable1 in a spreadsheet cell, e.g. A10. The variable can then be referred to in the current Jedox Web session, for example in other reports. First, "#VALUE!" appears in the cell. This changes once you enter a value in cell A10. After that, variable1 has this value. The value is written to the Jedox database and displayed in A10. However, what actually remains in cell A10 is "=@variable1". This entry is displayed in the edit line. The value of "variable1" can be called with "=@variable1" in any cell of the current Jedox Web session. In each cell where "=@variable1" is entered, you can assign variable1 a new value with new input. In a new Jedox Web session, "#VALUE!" will appear in A10 again, because the variable value of the last session is not saved.
After assigning a value to a field, the variable changes its type based on its value:
- With Booleans, the variable becomes an object-type.
- With numbers, the variable becomes a double-type.
- With letters or special characters, the variable becomes a string-type.
In specific languages, the values can be translated. For example, the cell content "True" becomes "Wahr" in German.
However, when you retrieve the cell values by using a macro or by comparing them using an IF formula, it has a value of true or false.
As Booleans are a type of object, comparing them in a PHP Macro needs to be done like in the example below:
$boolvar = retrieve_variable("varBoolean");
$boolValue = ($boolvar) ? 'true' : 'false';Option 2
You can create a variable with an entry in the input field for variables. Such entry fields for variables are in the Format Control dialog windows for ComboBoxes, CheckBoxes, and DatePickers, in the corresponding dialog window of Widgets, and in Hyperlink transfer tabs.
To the right of the entry field for variables, you can use the variable picker to select an existing variable. You will receive the dialog „VARIABLES“ described below in Option 3 with the only difference that the button Unset variable (2) is missing. Click Apply/Cancel to close the dialog. You can now call the variable with "=@Country" in any spreadsheet cell of the Jedox Web session. Variable values entered in these fields will be stored in the file.
Option 3
Under Formulas > Variables you get an overview of the variables in the current workbook. Below is an example:
Here you can add a variable (1) or unset a selected variable (2).
By using Unset variable (2), the value will be deleted. If the variable name is not present in any workbook cell anymore, then in the next Jedox session the variable name will also disappear from the variable list.
You can double-click a variable and then edit the value or, if the variable is in the current workbook, set it as private. You have to use the Update button to save your changes.
Private/global mode of variables
When a user session ends, then variables in Jedox Spreadsheets are normally reset to "no value", with the exception of variables set in objects. The definition of a variable as private applies for all users of the workbook in which the variable is defined. Thus, every user can set a value, which will be stored with that user's settings.
Note: a changed value of a private variable will only be stored in a user's settings by closing or saving the report/spreadsheet, not by logging out and leaving the report/spreadsheet open and unsaved!
At the next login, the stored variable value will be restored. This set variable value can be overridden for mapped variables with the check mark "Force." Thereafter, the variable value behaves dynamically in reports with mapped variables.
Updated March 27, 2024