Best Practices for Jedox Spreadsheets

Jedox Web is a browser-based spreadsheet program that reads and writes data directly from the Jedox In-Memory DB (OLAP).

With Jedox Web, you can apply styles and colors to spreadsheets, as well as insert PALO functions, pictures, and dynamic elements such as DynaRanges. You can also add several types of form elements, like Comboboxes, Lists, and Buttons.

To get started, see Jedox Spreadsheet Basics.

Jedox Spreadsheets have limits that apply when pasting Views or expanding elements. Following best practices increases performance and productivity while avoiding error messages.

Below you will find tips, common issues, and solutions for using Jedox Web.

See also Jedox Web Spreadsheets FAQ for more information.

In-Memory DB Functions

  • We recommend using the PALO.DATA, PALO.DATAC, or PALO.DATAV functions in Jedox Spreadsheets. For more information, see Inserting PALO Functions into Spreadsheets.

  • For PALO.DATAC(), there are two usage constraints: DATAC() cannot be used in cyclic calculations, and it is not possible to use two explicitly nested DATAC functions in one formula, such as PALO.DATAC(...,PALO.DATAC(...)). The inner function must be used in another cell, then referenced from the outer function.

  • PALO.DATA formulas that refer to an empty cell return a value of 0, not an error.

  • The length of element lists retrieved from the OLAP server in Jedox Spreadsheets (for example, PALO.Subset feeding a combo box) is limited to 65,536 elements by default. This limit can be optionally changed by contacting Jedox Support.

Formatting and Design

See also Jedox Spreadsheet Basics

  • worksheet elements (charts, images, form elements) will not be moved dynamically if a row / column is hidden or if there is a change of the sheet layout.

  • If cell content is aligned to the right in Jedox Spreadsheets, it is not possible to set indent values. In this case, the control for indent setting is disabled.

  • Marking multiple, unconnected cells / cell areas is not possible.

  • In the Conditional Formatting Rules Manager, changes in order sorting are accepted only when "Show formatting rules for: This worksheet" is selected. If you have "Show formatting rules for: Current Selection" set, changes will not be accepted, although the Apply button remains active.

    See more information in Conditional Formatting.

  • Cell references cannot point to sheet names that include DynaRanges.

  • You can copy values from external tables and paste them into cells of a Jedox Spreadsheet. Your user mode will affect copy/paste behavior, as described below:

    In Designer Mode: if you paste into cells containing PALO.DATA functions, then the preservation of the functions works only in the visible area. If the paste area is larger than the visible area, then the existing PALO.DATA functions in the non-visible area will be overwritten and the inserted values will not be written back to the cube.

    In User Mode: the pasting will only be performed on the currently visible cells due to technical limitations, even if the area is larger than the visible area. Cells in the invisible area will be untouched. The preservation of PALO.DATA functions work in the visible area. If the report contains any frozen panes, the paste operation will not cross the pane edges.

Jedox Design System for Reports

To help you design functional and attractive reports, we have developed the Jedox Models & Reports Design System. This system provides guidelines, reusable UI components, and other resources to assist you in creating models and reports in Jedox more efficiently and professionally.

Performance

  • In Jedox Spreadsheets, you should avoid formatting entire rows, columns, or worksheets if possible, since these actions are very memory intensive. Read more about formatting in Jedox Spreadsheet Basics.

  • Avoid using the auto-fitting option on a large number of rows if there is also a large number of columns in the final report, for example via the expansion of a horizontal DynaRange, or the other way round. We recommend setting the exact width and height.

  • When designing spreadsheets, avoid constructions where a large number of formulas include references to very large ranges of cells (e.g. tens of columns * hundreds of rows), especially if the referenced cells themselves contain other formulas. For example, such a situation could occur when using functions such as VLOOKUP() or HLOOKUP() inside of a DynaRange that expands to many rows, where the Lookup function include a reference to a large range of cells. In these scenarios, the number of Lookup functions multiplies with the amount of referenced cell formulas, implying a very large amount of memory to be used and potentially slower calculation performance.

Updated April 14, 2025