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;

Extending Web Batch Reporting

Batch reporting is a critical process in some organisations. The ability to print multiple copies of the same report for different parts of the organisation for example, allows a controlled distribution of reports via a central source.  Jedox provides a built in Batch Reporter. It allows you to spin off multiple copies of the report via specific dimensions of the data (for instance, by Cost Centre, Region, Division etc).

The standard Batch process only prints to pdf, and can only deliver the reports via email. With the help of some php script and web macro, we can take a simple web report and allow it to be printed as a batch in some different ways. We can get a series of reports (not just one) generated. For example, one report per region element. We can also allow that report to be printed as either an xlsx or wss file directly to the filesystem.

See the example here.

Steps to get the report to work:

  1. Report Directory: Enter in a path that the Jedox Web server can see (normally a local path). For the moment, this directory must exist. Although directories are not created as part of this process, that could be by extending the php code further.
  2. Report Title. This will be used as the first part of the filename that gets generated in the batch process. The second part of the filename will be the dimensional elements that you are looping through. Also select the checkbox if you would like values only generated by the process.
  3. Reporting Filename. Ignore this, as it will be generated automatically as part of the process.
  4. File Type. Select xlsx or wss
  5. List of Elements. Under the chart, there is a hidden column which contains a list of elements that I want to loop through. Put a zero in cell A1 to unhide the column. It is a named range, so if you want more elements, just insert some rows in your report within the grey area to extend the range:


Once you have done the above, run the macro by clicking the batch button. If you get a dialog box telling you that the macro is complete, then usually the process has run successfully.


If you do not get that dialog, then something has gone wrong. Normally, the problems is related to insufficient permissions to write out the file or that the directory does not exist. This useful extension to the built in reporting can itself be extended. If you wanted to loop through more than one dimensional list, you could have multiple nested loops in the php macro generating the reports you required.

Better beverage budgeting with Jedox

De Bortoli Logo

De Bortoli Wines streamline budgeting with Jedox

De Bortoli Wines is a third generation family wine company, established in 1928. The winemaking philosophy is that great wine begins in the vineyard. With vineyards in some of Australia’s best known wine regions including the Yarra, King and Hunter Valleys. The Riverina produces the acclaimed Noble One and world-class premium varietal wines.

Anyone with experience in food and beverage production knows modelling these processes can be complex. From raw materials with expiry dates, to average costs versus standard cost for different parts along the supply chain. Fortunately, De Bortoli have some of the best and brightest working with them: their internal team actually developed a Jedox plug-in for the data integration tool Kettle. In this interview, we speak with Management Accountant Ian McLain, and Solutions Architect, Pieter van der Merwe about streamlining budgeting with Jedox.


Naked Data: Has Jedox made your budgeting easier?

Ian McLain, Management Accountant: Jedox Web has opened up a lot of new avenues. The branches can now enter their data directly and we can see them as they are updated and understand where in the workflow they are up to. Then the branch managers can review their input. It has cut out all that to-ing and fro-ing. The whole process is streamlined.

Pieter van der Merwe, Solutions Architect: Jedox Web has made a big difference. We used to send Excel spreadsheets out to the branches, and then get them back filled in, and then have to import these and make adjustments. But everyone gets access to Jedox Web, so it’s made a huge difference to our data collection and saved a lot of time.

Naked Data: How has your planning process benefited with Jedox?

Ian McLain, Management Accountant: Our pricing model has really benefited. We’re so much more precise now. We’re budgeting and planning by litres, we have a price list, discount and rebate structures based on our customer groups. For budgeting, we used to just take the budget from the year before and add on 3 percent. Now we can work in much more precise measurements, the results are more accurate. We can break down to any level of detail for variance analyses. Instead of trying to figure out whether or not we are under budget, we can see WHY we are or aren’t, we can see whether the discounts are too high, or if the litres produced are too low – or if the cost of goods is too high.

Pieter van der Merwe, Solutions Architect: Finance used to come to us with a general idea of what they wanted and then there would be back and forth as we tried to get an idea of what they wanted, now Finance comes to us with a model and says ‘build this’, and we can model it straight away in Jedox, linking to any operational data sources.

Naked Data: Does this mean you can model business processes more easily with Jedox?

Ian McLain, Management Accountant: Yes. It’s such a free format. For instance, at the moment we are building our own labour model. We used to just budget labour the same way as all the other departments, which did not provide enough detail to allow for meaningful analysis. Now we can extract straight off the payroll package, upload it straight into Jedox, and then have budgets right down to individual staff in the budget model. It’s fantastic for building your budget models on real time issues rather than dumping stuff out of the general ledger.

Naked Data: How quickly do you get to insight with Jedox?

Ian McLain, Management Accountant: Previously we used to update journals in the GL and then we would have to wait overnight for everything to update. We couldn’t get real-time data. That changed with Jedox, we now get info on the fly. When I’m in sales meetings, sales managers ask me if I can incorporate certain things into reports and plans, and I can do it there on the spot.

Naked Data: What next for De Bortoli and Jedox?

Ian McLain, Management Accountant: The writeback is useful for more than just budgeting. We are using this feature to record monthly comments on variances. At the moment we’re doing monthly forecasting as part of the budgeting cycle, but our next priority is rolling forecasting with Jedox.



Snapshot:​ ​​​ ​​Jedox budgeting & ​f​orecasting​ ​solution for beverage ​produc​er De Bortoli Wines.​Source​ systems​ include MFG Pro​.​