Working with Named Ranges and Variables

image_pdfimage_print

A named range is known in the accompanying Jedox spreadsheet, and a specified variable is known throughout the whole Jedox Web session. Entries of named names 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.

Creating global names

If you want to use named ranges of another workbook, such as the file globals, in the workbook Test1, then you must set globals as a resource: in the Report Designer, right-click on globals and Mark/Tag – As resource. Right-click Test1, select Properties and open the Resources tab. There you can add globals as a resource. After this action, you can use the name Region from the worksheet Sheet1 of the workbook globals with the following options 1 and 2.

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 resource for WB2, WB2 is a resource for WB3, and WB3 is resource for WB1 isn’t allowed.

Option 1

You can call Region in any cell of Test1 with:

=[globals]Sheet1!Region

Note: 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

Option 2

In Test1, define  a name (preferably Region) to be assigned to the corresponding cell of “[globals] Sheet1”:

You can call Region in any cell of Test1 with:

=Region

Creating variables

Option 1

With “=@variable1”, you can create variable1 in a cell, e.g. A10. The name is known in the current Jedox Web session. First, “#VALUE!” appears in the cell. This message disappears once you enter a value in the 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 a 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 type of object, comparing them in a PHP Macro needs to be done like in the example below:

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 see the button V (Variable picker), which you can use to select an existing variable. Click OK to close the dialog above. You can now call the entered 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 Data > Variables you get an overview of the known variables in the current workbook. Below is an example:

Here you can add a variable or unset a selected variable. You can double-click a variable and then edit the value or, if the variable is in the current workbook, define it as private.

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 user’s settings by closing or saving the report / the spreadsheet, not by logout and leaving the report / the 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 checkmark “Force”. Thereafter, the variable value behaves dynamically in reports with mapped variables.

image_pdfimage_print