Tag Archives: Jedox Web

Managing Web Reporting – Part 2

After being out in the wild for over 12 months, the Performance Dashboard has been one of the most popular posts on our blog. As it was originally written for in Jedox 5.0, we have updated it to reflect the latest functionality in 5.1 SR2. New Features

  • Hits chart now using a Stock type chart, that allows zooming in to specific days
  • Hits by Hour, to allow you to zoom in on the busiest time of the day
  • Doughnut chart now shows the report  for a specific hour, vs overall usage
  • Filters are clearer, and easy to reset.
  • Login stats, to see the numbers of users logging onto the server (including from Excel).
  • On the report tab, you can now open the specific report that you are analysing.
  • The ad-hoc tab now has a heatmap which helps with visualisation of the most popular reports. The drop list on this tab also now has the complete File Manager hierarchies to enable you to navigate easily.

Continue reading

Jedox 5.1 – a Consultant’s Perspective : Part 2 – Security

Blue Doors LockedSecurity is critical in any Jedox implementation. Efficient ‘lock down’ of your models are just as important to an organisation as the data they contain. There has been a need for an additional layer of security in Jedox’s database architecture for a while now.

Pre-5.1 , you could lock down cubes , dimensional elements, even cells. But you could not actually hide or restrict databases themselves. This is a hugely important step – now you can hide all assortment of databases from certain user groups. A great example that has come up time and time again for me is around Payroll models. Even though Jedox security stops unauthorised access to cubes and dimensions within a Payroll database, the fact that unauthorised users could ‘see’ the database folder name made administrators nervous. This is now a thing of the past with the Jedox developers re-writing the security model to cater for  databases. There is now a separate security object (#_GROUP_DATABASE_DATA) that allows admins to restrict by group access to a database.

This new security development also opens the door potentially to a more multi-tenanted approach to model development. I have updated our security templates for database restriction and made them available :

Web Security Template

Excel Security Template (pw:jedox)


Subsets : Data Filters

The Jedox Data Filter (DFilter) is one of the more important filters that you can use in subsets. The Data Filter allows you to query the cube dynamically query a cube and order/filter your dimension based on this query. This is really handy  when you need to build a report based upon data, such as a top/bottom element listing, a zero suppressed report, or sorting your report based on the data.

When setting up this subset, you need to select the cube and dimensions that the dfilter will be based on. Make sure you select the right one, or you may not get the result you expect…


After you have selected the cube, the next thing to do is to select the dimension elements from each dimension for your dfilter.

To optimise performance of the data filter, ensure you specify an element for each of the dimensions . Like all filters, you can make your selections dynamic based on cells on your report:


Finally, you can select calculation options (sum, count the result, etc), specify the top or bottom count or choose how to sort the result. A completed report performing a top/bottom filter and sorting is here.

Data filters can really improve the dynamism of your reports and once you start using them , you will find that a lot of reports can benefit by the inclusion of this filter in your subsets.

Exploring Row and Column Properties

There are a whole bunch of web report properties that you can play with in Jedox Web Macros that can add a touch of professionalism to your web reports. Every small addition of function that you can add to your reports increases the likelihood that your users are going to embrace what you are giving them.

The file here illustrates some possibilities with row and column adjustments. You can set up a macro to allow the user to size the column or move it to an exact width. 

Here, I am adjusting the row  height in pixels, retrieved from another cell on the workbook:

$height = ActiveWorkbook()->names->item('example1_height')->value;
 $row = ActiveWorkbook()->names->item('example1_row')->value;

..and now the column width:

$width = ActiveWorkbook()->names->item('example3_width')->value;
 $col = ActiveWorkbook()->names->item('example3_col')->value;


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.

Macros : Named Ranges

OK. So you know have got the hang of writing a bit of php code and you can now refer and transfer data around your spreadsheet with ease. However, there is a problem here. The issue with referring to cells in Jedox Web is the same issue you get with referring to cells on a spreadsheet – a cell reference is relative and my php code is fixed: if I insert or delete a row or column on my spreadsheet, suddenly my macro stops working.

As with Excel, Jedox Web can use named ranges. By referring to a named range it means that my code will not fail if the cell moves. Jedox refers to ranges differently than Excel. The trick here is that Jedox uses a Name class, while Excel uses the Range class. Jedox has a Range class too, but not to refer to named ranges…

Download the sample here

// Referring to a Named Range

$value = ActiveWorkbook()->names->item('MyRange1')->value;

We can also create a name range on the fly:

// Creating a Named Range in code:


Using the Name class is powerful – you are really starting make your web templates bulletproof when you employ this method for referring to cells or ranges.

Macros : Cell References

The Jedox Macro Engine implements PHP to interact with the Jedox Spreadsheet. One of the most used and basic functions is referring to a cell reference in the PHP code.  You can put data into a cell on the spreadsheet, or you can read from that cell.

Download the example spreadsheet here

Reading Cells

// read a cell value:

$value = ActiveSheet()->Range('E7')->value;

return __msgbox($value, 'Returning a Cell Value', 'Info');

// or you can use

return __msgbox(ActiveSheet()->Range('E7')->value, 'Returning a Cell Value', 'Info');

Writing Cells

// Write a value into a cell:

ActiveSheet()->Range('E10')->value = "hello again";

Note, the warning in my previous post. Depending on the complexity of the calculation and the type of cell you are referring to, the calculation timing may be out when referring to cells.

For instance, if you have a =PALO.DATA in the cell that is volatile and you are using a Macro to read the cell value, depending on the order of calculation you can pick up the wrong result. If you are trying to read a value from a cell that contains a PALO.DATA formula, sometimes it is easier to just refer to the cell in the cube using the application-> command in the Macro.

Returning Cube Values

// Get a value from a cube :

$value = application()->palo_data("localhost/Demo","Sales", "All Products", "Europe", "Jan", "2011", "Actual", "Units");

Note here that the formula palo_data, not palo.data. This is because the Palo/Jedox functions implemented in the Macro engine are directly from the Jedox PHP API.  Also, the point/full stop/period is a reserved character in PHP. It is the concatenation character between strings.

Finally, an interesting/semi -advanced concept for you. Sometimes you want to retrieve a cell value that is not necessarily an intersection in the cube, but the sum of certain subsets across one or more dimensions. For example, want to retrieve the Total Unit value for a collection of top 10 Regions. Unfortunately, you cannot refer to a subset as a substitute for an element name in a PALO.DATA formula (yet). But, we can use a macro to achieve the same effect.

Returning a Subset

// Return data cell based on a subset on the Regions Dimension.

// NOTE: The PHP subset is different to the spreadsheet function in 2 ways:

// 1. Replace all . with _ (eg palo.subset becomes palo_subset)

// 2. all blank arguments need to be replaced with NULL (eg PALO_HFILTER("West",FALSE,FALSE,,,,,,) becomes PALO_HFILTER("West",FALSE,FALSE,NULL,NULL,NULL,NULL,NULL,NULL))


// Get array size for looping

$ArraySize = count($ArraySS);

for ($i=0; $i<$ArraySize; $i++)


$value = application()->PALO_DATA("localhost/Demo","Sales", "All Products", $ArraySS[$i][1], "Jan", "2010" ,"Actual" ,"Units");

$ValueTotal = $ValueTotal + $value;


ActiveSheet()->Range('E22')->value = $ValueTotal;