Macro Engine Overview

Related links: Macro Engine Configuration, Macro Examples

Jedox Web is a powerful framework for building feature-rich web applications, based on the concept of web spreadsheets. It is a client-server architecture, where the client runs in a web browser. With over two hundred spreadsheet functions and many built-in components like charts, micro charts, form elements, DynaRanges, framesets, and widgets, there are almost limitless ways to create extensive applications with Jedox Web.

However, sometimes even all this is not enough for a demanding Jedox Web user, which is why Jedox Web comes with integrated scripting support called Macro Engine (ME). ME is based on PHP: Hypertext Preprocessor, and it enables a workbook designer to write PHP code that is executed within Jedox Web’s Core runtime.

As of Jedox 2021.3, PHP Macro Engine in Jedox Spreadsheet Server uses PHP version 7. Note that the PHP 5 engine is being deprecated (as announced in Jedox 2021.1 Release Notes) and will be removed in a future version.

The Macro Engine offers the core PHP functionality as well as a number of specific language elements (classes, functions, and global variables) to provide capabilities related to Jedox Web applications.

For an overview of the functions and objects exposed to Macro Engine, please refer to our Macro Engine API Documentation. You can also download this documentation as a zip archive.

Hint: the Macro Editor can be opened from a spreadsheet in Jedox Designer with the keyboard shortcut ALT + F11.

Cross-version compatibility

Macro Engine scripts are written in PHP and have to be compliant with the PHP version used by the Macro Engine. They also can make use of Jedox-specific PHP functions, which may be dependent on the Jedox version in use. New versions of Jedox may include new versions of PHP in the Macro Engine, and thus may require manual adjustment of script code. Additionally, scripts developed in and working with a current version of Jedox may not work in a previous version.

Jedox uses PHP version 7. Custom scripts written for PHP 5 may require modifications, depending on their complexity. Sample scripts shipped by Jedox (e.g. for Drillthrough) do not require any changes. A list of required changes is available in the PHP documentation

User-defined functions

The definition of functions in ME is very similar to writing Visual Basic Applications (VBA) in Microsoft Excel. For example, Excel Object Workbook is translated to Jedox Web Object Workbook, and instead of using VBA syntax, you use PHP syntax.

The following naming rules apply to user-defined functions in Jedox:

  • A function name can be used only once.
  • You cannot create two macro functions with the same name.
  • A function or method in Macro Editor may not have the same name as a spreadsheet function that is already used in the report.

Note: if a spreadsheet cell holds a PALO.DATA formula, and a value is set in this cell via a macro, the formula will not be overwritten. Instead, the set value is sent to OLAP for writeback.

Functions available as spreadsheet formulas

You can use the Macro Editor to write user-defined functions for the workbook, and these functions can be used like any other integrated functions. Functions defined this way are available in the current workbook; i.e., they can be used on each sheet in the workbook, but they are not available in other workbooks. Macro functions beginning with an underscore cannot be called as spreadsheet functions. This constraint only applies to functions defined in a workbook macro, not to globally registered, user-defined functions.

Function call when a workbook is opened or closed

There are two special functions to run a macro when a workbook is opened or closed: __open() and __close(). To use them, simply define a new macro like this:

function __open() {
// macro definition here

You do not have to assign this function to a form element, or call it in a cell. It will automatically be executed every time you open the workbook. Both functions use the same syntax.

Server-wide functions

Imagine a scenario where you define a complex custom spreadsheet function that calculates average monthly income based on input values, and everybody from your team would like to use it in their workbooks. Copying this functionality into every workbook would be one solution, but a more elegant one would be to make those functions “server wide”.

See Macro Engine Configuration for an example of a server-wide function in action. In macro_engine_config.xml are the entries to use the function INCOMECALC() server wide.

Starting Macros with form elements

Macros can also be started with form elements. The macro will be started by clicking on a button, changing the argument in a ComboBox, or by setting it on/off with a check box.

Go to Insert - Button. You can create a button in any spreadsheet cell:

ComboBox in Excel screenshot

You can assign a macro to this button with a right click. Similarly, you can assign macros to ComboBoxes and Checkboxes.If a macro is assigned to a button, with a right click you can edit or unassign the macro.

Assign macro button screenshot

For examples of macros that use form elements, see Macro Examples.

Using Modules

In a macro with multiple modules that reference each other, you must use the module() function in combination with require|include[_once]. For example, to use a function from Module2 in Module1, the following code must be put in Module1:

require module("Module2");

Updated August 25, 2021