Jedox Dynamic Array allows you to dynamically insert data into an Excel spreadsheet by spilling multiple values into adjacent cells, the contents of which are based on dimensions, elements, and Subsets from a Jedox In-Memory (OLAP) Database. If the array contains consolidated elements, you can expand or collapse them directly in the spreadsheet. Dynamic Arrays are similar to DynaRanges in Jedox Web; the main difference is that DynaRanges can be nested, allowing for more complex report structures, while Dynamic Arrays cannot.
Creation
To create a Dynamic Array, select the option from the Custom Reports section in the ribbon or in the task pane. Choose the database and dimension, and either manually select the desired elements or, if available, select a stored Subset. Once you have selected the source, you can insert the array either vertically or horizontally. The array utilizes the formula PALO.DARRAY.
Interaction
If the array consists of consolidated elements, you can expand/collapse them directly in the spreadsheet. For convenience, you can enable the click area. Note that the ability to expand/collapse elements from stored Subsets is bound to the definition of the Subset.
If there are populated cells to the right of the vertically pasted array formula or below the horizontally pasted array formula, these cells can interact dynamically with the expand/collapse actions of the array. This range is called extended interaction area.
By default, the argument is set to zero (“0”), meaning that only the array itself will expand, with no adjacent interaction.
You can manually increase the number to the desired amount of adjacent rows/columns, which will then spill dynamically as the array expands. It is recommended to use a reasonable number (e.g. up to 10) to avoid performance issues.
If there are populated cells parallel to the expansion area of the array, but not parallel to the formula (e.g. the colored cells in the screenshot above), these cells will be overwritten as soon as you include this row/column in the extended interaction area.
Updating the array
If the data source of the array is derived from a stored Subset where all elements are selected at once (i.e. “select all”) and not individually, then the array will automatically update when the Subset is changed, either by interacting with the array or by updating the sheet/workbook. If only some elements from the Subset are used or all elements are selected individually, the array will not be updated automatically.
To edit the array, right-click on an array cell and select Jedox -> Dynamic Array from Excel's context menu or select the option from the ribbon.
Furthermore, you can copy/cut and paste the Dynamic Array (with all its definitions, i.e. function, source, and related settings) to another area of the sheet or to another sheet within the workbook.
Videos
Notes
- Dynamic Arrays are only available in the Add-in for Excel 365 and are not compatible with Web Spreadsheets, just as DynaRanges are only available in Jedox Web and are not compatible with the add-in.
- The import of spreadsheets containing the PALO.DARRAY formula into Jedox Web is not supported and will display the error #NAME in the corresponding cells.
- If there is data in any cell in the direction of the expansion, the array cannot be spilled and a blue line will appear showing the error #SPILL!
- The "show symbols" option is not compatible with Dynamic Arrays and should be deactivated. Otherwise it could break the formula.
- Nesting Dynamic Arrays is not supported.
- It is not possible to copy/cut and paste Dynamic Array into another workbook.
For more information, see Custom Reports in Add-in for Excel 365
Updated April 14, 2025