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:
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:
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.
A more detailed example can be found in Report with ComboBox and 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.
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.
Note: 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.
In the typical scenario, the two DynaRanges have the same direction. You should avoid having either the right-side or the bottom edges of DynaRanges that move in the same direction “touch” each other.
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 Microchart, then the auto-width/auto height setting should not be used.
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 of the source cell value.
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 it is generally recommended not to use merged cells inside of DynaRanges.
Hyperlink transfer and DynaRange initialization
If a DynaRange depends directly on a cell, and a hyperlink is used to transfer a 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.
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.
Learn more about the DynaRanges in this free online course from Jedox Academy! Just enter your email address when prompted, and the video will begin playing.