DynaRanges constitute a dynamic area in a Jedox Web spreadsheet, allowing the user to build reports which dynamically and automatically adjust their layout to changing database contents. When viewing a spreadsheet which contains a DynaRange in Report Manager, the DynaRange is expanded in either horizontal or vertical direction. Each element in the source list of the DynaRange (for example, a subset of a OLAP database dimension) then represents one copied instance of the DynaRange area; for example, if a vertical DynaRange encloses two cells within a single row of the worksheet, and its source list contains five elements, these two cells are “cloned” into 5 rows in report mode.
Cell References in DynaRanges
When using cell references in formulas inside DynaRanges, these cell references are treated in a special way as the DynaRange is first activated, and later expanded or collapsed by the user, in Report Mode.
References to other cells within the DynaRange are dynamically modified, regardless of which type of cell reference (absolute i.e. with $ characters, or relative) is used. A reference to cell $C$5 (with $C$5 being a cell inside the DynaRange) is rewritten to $C$6 in row 6, $C$7 in row 7 etc. This is of course very helpful, for example, if you want to use PALO.DATA() formulas in the DynaRange, as it allows you to dynamically get the data for each referenced element of the DynaRange.
On the other hand, cell references to cells outside of the DynaRange are not re-written, but instead copied as-is in all rows/columns of the activated DynaRange. This in turn allows you to reference static content, such as a cell with connection/database information, or the OLAP cube name. The following screenshot shows the effect in the designer preview of a worksheet:
The simple cell references =$C$5 in column D are dynamically rewritten, each then pointing to cell in column C within their “own” row: =$C$6, =$C$7 etc. Meanwhile, the cell references in column E, pointing (relatively) to A5, are not rewritten. Each cell in column E continues to fetch the value from A5.
Conditional Formatting in DynaRanges
If a Conditional Formatting rule is defined on cell inside of a DynaRange, the rule is also adapted when the DynaRange expands. The way in which the rule is handled depends on the “Applies to” range defined for the rule.
If the “Applies to” range covers all rows of a vertically defined DynaRange, or all columns of a horizontally defined DynaRange, then for this rule, only the “Applies to” range is modified (enlarged or reduced); the rule formula itself, including cell references in it, is not modified. If the rule uses cell references which should adapt automatically to the expanding DynaRange, then the references must be defined as (fully or partially) relative.
In the following example a rule is defined with the “Applies to” range =$D$5:$E$5. The rule should trigger (change the cell background color to green), if the left-most cell of the DynaRange (C5 in Designer Mode) contains the value “2013”: =$C5=”2013″.
Note that the reference is partially relative, with regards to the row:
As the DynaRange – based on a standard “Years” dimension in a Jedox OLAP database – is activated, the “Applies to” range of the rule is automatically adjusted, now covering all rows of the activated DynaRange. Because the row in the cell reference (5) is relative, the lookup is not always done to row 5, but instead row 5, 6, 7 etc.; and because the column reference ($C) is absolute, cells in all columns of the DynaRange lookup the value in column C:
If the cell reference had been fully absolute, $C$5, all cells in the DR would have indeed checked the value of C5, and thus, the Conditional Format would not have been triggered on any cell.
Note that the relativeness of the cell reference comes into play in this scenario regardless of whether the referenced cell is located inside or outside of the DynaRange. In the following example, the CF rule (relatively) references cell A1. As the Apply-Range of the rule is modified during DynaRange activation, each cell in the DynaRange doesn’t check the value in A1; instead, they all check the value of the cell four rows above, and three columns to the left of their own position:
Cloning of Conditional Formatting rules
If the CF rule inside the DynaRange does not cover all rows or columns, this modification of the “Applies to” is not performed. Instead, the rule is cloned for each copied instance of the first row. In following screenshot, the DynaRange from the previous example was modified to now cover two rows:
If the DynaRange is activated, the Conditional Formatting rule is “cloned”, meaning that copies of the rule are created. The cell references inside the rule now are automatically adjusted, similar to references in cells:
The cell references are adjusted during the cloning regardless of whether they are (fully or partially) relative. However, it is still recommended to define relativeness in a proper way in this scenario.
If the cell reference would now point to a cell outside of the DynaRange, it would not have been adjusted during the cloning process, regardless of whether it is relative or absolute. This behavior is also similar to references in cells, as shown in the first screenshot.
In previous versions of Jedox, the “cloning” of Conditional Formatting rules was always applied on DynaRanges, regardless of the “Applies to” range of the rule. This could lead to hundreds of inefficient rules being created on larger DynaRanges, resulting in decreased performance, and high memory usage. The modification of the Apply-range was introduced in Jedox 6.0 SR2. However, it implies that the type of cell references in rules must be handled with greater care, where in previous versions, a reference within the DynaRange that was (incorrectly) defined as fully absolute would still be modified during the cloning process of the rule.