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.
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 https://www.php.net/manual/en/migration70.php.
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 calls by different workbook activities
There are four special functions which will be started by different workbook activities.
| Special function | Workbook activity |
| __before_open() | This function is executed after a workbook has been loaded in the Spreadsheet Server, but before it is being processed by the frontend.
For example, it can be used to initialize @variables on which a subset in a DynaRange depends. |
| __open() | This function is executed after the workbook has been processed by the frontend server, and initial calculation of functions has been executed. |
| __load() | This function is executed at the very end of the loading procedure, after the workbook has been loaded in the user's Web browser. Note that the __load() function is also executed when changing to a different sheet in the current workbook, if that sheet is opened for the first time while the workbook has been opened. The __load function will not be executed by navigating back to a sheet which had already been opened, but will be executed again if the entire workbook is closed, and re-opened. |
| __close() | This function is executed when the workbook is closed. |
To use them, simply define a new macro like this:
function __open() {
// macro definition here
}You do not have to assign these functions to a form element, or call it in a cell. They will automatically be executed every time the described workbook activity happens. The four 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:
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.
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 September 27, 2022