Tools – DynaRanges


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 OLAP 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 the user to expand and collapse the elements it contains. In more complex scenarios, DynaRanges can also be nested and made interdependent.

In the Tools menu, you have the option to create either a vertical or a horizontal DynaRange.

As a source you can select a formula, a subset, or an ODBC query.
Please note the Constraint for the data sources formula and ODBC query.

Horizontal DynaRange

In the example below, we selected D4:D6 and entered a horizontal DynaRange using the command “Tools – DynaRange – Horizontal DynaRange” (or alternatively, using the Horizontal DynaRange toolbar icon). We selected a subset as the source.
We then selected the Years dimension from the Demo database in the General tab and clicked on OK:

The result looks like this:

Vertical DynaRange

In this case, we selected C5:E5 and chose a vertical DynaRange. As the source we selected a subset with the following specifications:

We need to enter a function into cell D5 in order to display values from the database in the worksheet.
To do so, we selected “Query – Paste Data Function…”.

In the next dialog box, we selected the database, the cube, and activated Guess Arguments:

The result looks like this:

The following view appears by selecting “View – Quick View” or “Designer Preview”:

For performance reasons, a maximum of four elements will appear for each dimension in this quick view. A complete view is available in the Report Manager.

The list shown is based on the database dimension that was selected (in this case, Products and Years), and it can be expanded and collapsed by the user. Double-clicking on “Stationary PCs” will expand this Product Group, and show all child elements; double-clicking on “All Years” will collapse the child elements, so that only “All Years” is shown.

During this modification, the inner cells that contain the PALO.DATA formulas are also either added (during expansion) or removed (when collapsing). Also, the cell references in these formulas are automatically adjusted. Thus the reference to cell D5 inside the PALO.DATA formula in the first row of the DynaRange is modified to D6 in the second row, D7 in the third row etc. Note that 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 the user to, for example, do calculations over the whole DynaRange, e.g by adding a SUM formula.

If necessary, this cloning of cell references can be avoided by wrapping 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 DyanRange 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. For the above example, please highlight C4:D5. This data area will be referenced in the “Insert Chart – Dialog” with $. This absolute referencing is necessary for charts of DynaRanges.

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
Allowed nesting
When nesting several DynaRanges, some constellations cannot be resolved and subsequently will lead to conflicts. Nesting of the last sub-section is allowed (corresponds to the nesting on the right):

Also allowed are two Dynaranges that are dependent on each other, i.e., subset of inner DynaRange refers to outer DynaRange. Note that 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 avoided having either the right-side or the bottom edges of DynaRanges that move in the same direction “touch” each other.
Nesting more than two DynaRanges is currently not supported when the DynaRange subsets are interdependent (i.e., one DynaRange refers to another).

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.

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 of 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 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 will not directly update itself. In this scenario, you should 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 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 Batch XLSX Snapshot, however, all DynaRanges on all sheets will be calculated.

Was this post helpful?
NoYes (+2 rating, 2 votes)