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.