Tag Archives: Macros

ETL from Excel for Jedox 6

A quick update for those who have been using the Excel spreadsheet we posted a while back for running ETL processes. Due to some fundamental changes in Jedox 6, that spreadsheet requires some additional information (such as username and password) to run loads and jobs. You can find the latest one here.

Hope you like it. Please let us know what you think.





ETL from Excel

hair salon accountingMonth ends are like haircuts. Towards the end of the process, there are a lot of little snips and clips. Enter month-end adjusting journal into ERP, run Jedox Integrator, rinse, repeat. The adjustments can go on for a long time, so an easy way to process them is critical. Having a custom Jedox web page to run jobs helps, but sometimes customers are so wedded to their Excel environments, that they would rather a “one stop shop” in a spreadsheet.

Necessity, as always, is the mother of invention.  For various customers we have created from time to time excel driven ETL processes. We have used a couple of approaches but this latest one uses SOAP calls and web services to execute the jobs, just like the web macros do. This approach has many advantages : It is fast (as it uses the native Jedox ETL api), it is clean (you are not trying to build batch files on the fly, for example) and you can circumvent security issues (like Impersonation) when trying to talk to the server remotely.

Jorge Mendoza, our legendary Excel expert put the following files together. The Excel file is more of an ETL control centre. It allows you to refresh all jobs, review any object (including variables) and run , stop and check the status of jobs. It is pretty cool.

2015-02-27 17_14_09-ETL.xlsm - Excel

Instead of using this interface, you can attach the following function to your own code to run a job of your choice. As long as you have the module in the file, you are good to go:

CallETL(sServer As String, sProject As String, sType As String,
sName As String, Optional vVariables As Variant)

The vVariables is a 2 dimensional array, specifying the variable name and the variable value you want to pass.

You can download the Excel file here. You can also download the .bas file and import it into any excel vba project. You can get the .bas file here. We tested this on Jedox 5.1 SR3, but not on any other versions.

The final point is to get this to work on your server from a client machine, you must ensure that the ETL port is available outside the server. By default, is it not.

On the server, open the tomcat/conf/server.xml file. Search for “7775”. This is the default ETL port. Change it from “” to “”. This will not allow you to communicate via web services to this port.

NOTE: What we are doing here is essentially opening up an unsecured port on the server. If this is an issue in your environment, you can restrict access to a specific  IP address, password protect the Excel file, or both. In Jedox 6, a new security layer will be introduced around ETL, which will mitigate this issue.

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;

Macros : An Introduction

The Jedox Web Macro Engine is one powerful beast. It is based on PHP Hypertext Pre-processor (PHP5) and it enables power users and Jedox BI developers to write PHP code which is executed within Jedox Web’s Core run-time. What this basically means is it gives you the power of something like VBA on the web. It allows you to script event driven macros that can be executed by the user on demand.


Why on earth would you want to write macro scripts in Jedox web? For all the same reasons that you would write VBA in an Excel workbook. It helps provide an additional layer of sophistication, flexibility and power to the users of your reports.

Before you get started with Macros, a couple of points to note:

  • You cannot publish an Excel workbook with VBA macros to the web and expect Jedox to parse and re-write the VBA into PHP. This may sound obvious to some, but it is a common assumption that a lot of people make.
  • The debugging capability is limited in the Macro interface and there is no debugging niceties that you have in VBA, like a Watch window or an Object Explorer. There a some basic debugging capabilities, but you generally have to code and debug by hand. Once you get into the swing of it, it is straight forward.
  • When debugging code, a quick tip is to keep the core.log open in Notepad++ or some other editor. The Core log will tell you where you error is occurring and give you a bit more information as to why. This is handy, as often you will run your macro and nothing will happen on the user interface.
  • Like Excel, the Jedox Web spreadsheet has a different calculation routine to the Macro engine. This means that sometimes (especially when referring to cells on a spreadsheet), macro calculation may be out of sync with the cell calculation. There are a number of ways around this, but for the moment, it is just important to recognise that this can sometimes be an issue.
  • In terms of debugging, the message box is your friend. Use it to view variables and array values at run-time and certain points in the code.


  • Macros can be assigned to all Form Elements (check boxes, date pickers, buttons, drop lists). These objects, when accessed by the user, triggers the macro. As well as this, you can define your own UDF’s (User Defined Functions) which can be embedded in cells as formulas on a spreadsheet. In addition, you can actually distribute your own custom functions into the Macro Core engine, to make them available server wide.

If you come to Jedox web from the world of PHP, you are in luck. The Macro Engine gives you complete freedom in terms of the PHP you can write, as well as additionally implementing an extension to refer to spreadsheets. Existing PHP extensions can be added, which allow you to extend your application way beyond just reporting off cubes.

If you come to Jedox web via VBA (like me and I suspect a vast number of people reading this) , PHP can be a little daunting at first, but if you persevere you quickly get the hang of it.

I will be publishing a few examples over the next little while. Some examples are simple and some may be a little more advanced. If you are new to macros, then this may be a good place to start.