Macros : Variables

Jedox Web variables are session based, in-memory objects which allow users to transfer values between and around reports. Variables allow you to do things like:

  • Set a default value on a report
  • Transfer selected filter values from one report to another
  • Pass user selected parameters to a relational Dynarange

Setting a Variable value – 3 Ways

1. Directly on the spreadsheet. If you type in “=@hello” into a cell , and you would have created your first variable, called hello. If you check the Used variables Manager (on menu, click Data, Variables) , you will see that you variable has been created. It has #VALUE as a value as you have not yet passed a value to it:

Type over the cell again with a value , and you will see the Variable has now changed to that value. You can now refer to @hello anywhere in your spreadsheet or macros and it will return whatever value that you have assigned to it:


2. Using a control to assign a value to it. You can use the date picker, combo box, check box or button to assign a value to a variable:

3. Finally, you can assign a variable & value via a macro:

function __open()
 //variable name is hello and hello’s value is world
 define_variable('hello', 'world');

You can also retrieve a variable like this:

function __open()
 //get variable value
 $value = retrieve_variable('hello');

You cannot assign a cell value to a variable (for instance, a result of ENAME formula), but you CAN write you own small user defined function to return the cell value to a variable:

function CellToVar($variable, $value)

 define_variable($variable, $value);

you user defined function looks like this in the spreadsheet:

=CELLTOVAR(“variable1”, D3)

Once you assign a variable, you can use it in many areas of you report. One of the more interesting is the relational drill through:


This functionality means that you can construct a variable in a macro and then filter your relational query with this.

When a user logs out, the session variable value will disappear unless  the variable has been marked as a private variable. If it is marked private, Jedox stores the last assigned value and the next time a user logs in, that value will be used.

You can see variables in action in a web spreadsheet here.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s