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 , e.g. 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 on “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 variants 1 and 2.

Important Note: it is NOT possible to open two workbooks with the same name in the same session (this applies even if the files have different paths or two different upper- and lower-case spellings.). 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.

Variant 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

Variant 2:

You define in your file “Test1” 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

Variant 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 date, “variable1” has this value. The value is written to the Jedox database and displayed in cell 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 was not saved.

Variant 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. After you have closed the dialog above by clicking OK, you can call the entered variable with “=@Country” in any spreadsheet cell of the Jedox Web session. Variable values entered in such entry fields for variables will be stored in the file. 

Variant 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.

URL with passing variables:

To find the URL of a report on a server, right-click on a report (user preview), select Properties, and open the Link tab. If this report has the variables  “year”,  “region”, and “product”, and all of them are not private, then you can pass variables with the URL as follows: <URL>&var/year=2009&var/region=West&var/product=Monitors

Example:

http://paloweb.jedox.com/ui/studio/?_=eJwlzDELwjAQBeC%2FUm4Wgo5ZdXFwsaPpEJqzCWrvuF6EUvrfve CbPh6Pt8GiNRUCv0HCZ6xvbRRkEl0aJ6HK4EEm4RMcIBeUKGNerctHK2 ZKaJ6bOWo2BxfcBT%2FU3f8%2Fwd2ufXem8cVFg%2Bs1itr6GwX8Y9gtPyJn LSY%3D&user=paloinsider&pass=S8CqA98pLafSGrHm%2 BIHSOQ%3D%3D&var/year=2009&var/region=West&var/product=Monitors

With the URL above  you can access this report from another computer via the internet, and the attached variables will be entered in the report.

image_pdfimage_print
Was this post helpful?
NoYes (0 rating, 4 votes)
Loading...