In Jedox Web spreadsheets, DynaRanges allow the spreadsheet designer to build a dynamic table, the content of which is based on a dimension from an In-Memory database (or another data source). The DynaRange fetches its source data dynamically at runtime and automatically modifies the structure of the spreadsheet. It also allows you to expand and collapse the elements it contains. In more complex scenarios, DynaRanges can also be nested and made interdependent.
As a source, you can select a formula, a subset, or an ODBC query. Check also the Constraint for the data sources formula and ODBC query.
Below are some examples to illustrate simple vertical and horizontal DynaRanges. To create a DynaRange, go to Insert and select the type of DynaRange, as shown below:
Horizontal DynaRange
The example below allows you to create a horizontal DynaRange:
- Highlight the cells D4:D6. Go to Insert. Select the Horizontal DynaRange icon. The DynaRange properties window is displayed, allowing you to select the Source, Direction, Display, and Border settings.
- Select a Subset as a source. The DynaRange list editor window is displayed.
- Select the Years dimension from the Demo database.
- Click Ok. The result looks like this:
Vertical DynaRange
The example below allows you to create a vertical DynaRange:
- Highlight the cells C5:E5. Go to Insert > Select the Vertical DynaRange icon. The DynaRange properties window is displayed, allowing you to select the Source, Direction, Display, and Border settings.
- Select a Subset as a source. The DynaRange list editor window is displayed.
- Select the Products dimension from the Demo database.
- Enter a function in D5 to display values from the database in the worksheet. Select Query > Paste Data Function and click Paste.
- In the next dialog box, select the database, the cube, and the PALO.DATA function.
- Enable the Guess Arguments option:
- Click Paste. The result looks like this:
You can visualize the following view by selecting Design > Designer preview:
For performance reasons, a maximum of four elements will appear for each dimension in this preview. A complete view is available with the button Open user mode or in Reports.
The list shown is based on the database dimension that was selected (in this case, Products and Years). You can expand or collapse it by clicking on each parent element. Clicking on Stationary PCs expands this product group and shows all child elements; clicking on All Years collapses the child elements and only All Years is shown.
During this modification, the inner cells that contain the PALO.DATAC formulas are either added (during expansion) or removed (when collapsing). Also, the cell references in these formulas are automatically adjusted. The reference to cell D5 inside the PALO.DATAC formula in the first row of the DynaRange is modified to D6 in the second row, D7 in the third row, etc.
Note: this modification is done regardless of whether absolute ($) or relative cell references are used.
Cell references from outside the DynaRange that point inside the DynaRange are "cloned" when the DynaRange is loaded, or expanded/collapsed. For example, a reference to cell "=D5" in the above example would then become a list of references to all cells within the DynaRange: =D5,D6,D7,D8,E5,E6... This allows you, for example, to do calculations over the whole DynaRange, e.g. by adding a SUM formula.
If necessary, you can avoid this cell reference cloning of cell references the cell reference inside of a NOEXPAND() formula. However, using this formula will not prevent the automatic rewriting of references inside of the DynaRange itself.
References that point from cells inside the DynaRange to cells outside of the DynaRange are not modified when the DynaRange is loaded or expanded/collapsed. They will always reference the same cell from all cells inside of the DynaRange.
More detailed examples can be found in Example: Report with ComboBox and DynaRanges, Example: Report with ListBox and DynaRanges, and Example: Report with ComboBoxes and two interdependent vertical DynaRanges.
Charts of DynaRanges
Charts of DynaRanges are also possible in Jedox Web. Continuing with the above example, we highlighted C4:D5. This data area will be referenced in the Insert Chart > Dialog with $. This absolute referencing is necessary for DynaRanges charts.
Charts in Jedox Web usually detect automatically whether the source data is structured by rows or columns. However, when used on DynaRanges, in some cases the original detection in designer mode is different from the structure of data in user mode. In these cases, it is necessary to manually set the Group data by... option in the Source data dialog of the chart.
Constraints when using DynaRanges
A combination of overlapping DynaRanges may lead to unwanted side effects. As columns and rows are expanded/collapsed within one DynaRange, the content of the other DynaRange may be altered in unpredictable ways. Below are some suggestions for controlling the behavior of DynaRanges in your spreadsheets.
Nesting more than two DynaRanges in the same direction (i.e. vertically or horizontally) is currently not supported.
It is not possible to check with the ISERROR function if a DynaRange object returns an error such as #VALUE!, because the error would be treated as a string.
When using the "parents below children" option in a DynaRange, a cell reference to a cell in the Dynarange that is used in a formula outside of the DynaRange (e.g., in a SUM() formula) will not always be correctly adjusted.
Calculation of Conditional Formats on Cells covered by a DynaRange using the "Parents below children" setting is currently not fully supported and may lead to unexpected formatting results.
When changing the name of a named range which is used in the source formula of a DynaRange, the DynaRange formula must be manually updated afterward.
When two DynaRanges have the same direction, make sure the “edges” of the DynaRanges that expand in the same direction do not overlap.
Allowed nesting
When nesting several DynaRanges, some constellations cannot be resolved and subsequently will lead to conflicts. You can still use nesting of the last sub-section as shown below:
You can also nest two DynaRanges that are dependent on each other, i.e., the subset of the inner DynaRange refers to the outer DynaRange. When referring from the formula of the dependent DynaRange to its "parent", you have to use the parent's name (for example, "DynaRange1"), not a cell reference. Cell references in DynaRange source formulas are static, meaning they are not rewritten as the DynaRange expands.
For best practices, when combining two Dynaranges that expand in the same direction (vertical or horizontal), if possible, the DynaRange that returns the smaller number of elements should be placed outside the DynaRange that returns more elements. This reduces the number of calculations of the used source formulas. It also reduces the amount of the necessary changes to the sheet layout when the Dynaranges are calculated in the User Mode.
Using automatic row height and column width
You can set the column width and the row height for horizontal and for vertical DynaRanges. These values are used in a report view when you expand hierarchies. With "auto", the column widths and the row heights are adjusted to the cell entries. If the DynaRange contains a Micro chart, then the auto-width/auto-height setting should not be used.
If the DynaRange contains cells with longer texts, where text wrapping is set in the cell format, the row-height in the DynaRange will be automatically adjusted. However, this automatic adjustment of the row height will only occur if either the user enters a value in a cell in this row, or if some change is made on the report (e.g. a change in a combobox) which prompts the DynaRange to recalculate, for example because the DynaRange formula depends on the selected value in the combobox. If the combobox does not directly relate to the DynaRange, the row height will not be adjusted on change of the combobox (or another dynamic control), even if this prompts longer or shorter texts to be shown. The same applies for column width in horizontal DynaRanges.
Worksheet references
References to cells on worksheets that contain DynaRanges are not dynamic if the reference contains the sheet name (for example =Sheet1!A1). This means that the reference will initially work, but will not show changes in the source cell value.
Merged cells
DynaRange operations use various mechanisms of copying and moving sheet structures (initializing, expanding, and collapsing). In combination with merged cells, this may lead to unwanted side effects. Therefore, using merged cells inside DynaRanges is not supported.
Hyperlink transfer and DynaRange initialization
If a DynaRange depends directly on a cell, and a hyperlink is used to transfer value to this cell, the DynaRange does not directly update itself. In this scenario, you must transfer the value in the hyperlink into a variable, and make the DynaRange directly dependent on the variable.
Sheet Layout
If you reference contents from the Worksheet in the source formula for the subset (for example, a named range specifying a cell that contains connection/database), make sure to place these contents in area that is not affected by expanding/collapsing the DynaRange; that is, above and/or to the left of the DynaRange. Otherwise, the changing layout of the worksheet when expanding/collapsing the DynaRange may lead to undesired side effects.
When exporting reports to Excel, only the DynaRanges on the first sheet of the report will be calculated. When using a Batch XLSX Snapshot, however, all DynaRanges on all sheets will be calculated.
Updated September 27, 2022