Tools – DynaRanges

image_pdfimage_print

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.

As a source, you can select a formula, a subset, or an ODBC query. Note 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, locate the DynaRanges menu item in the Tools menu and select the type of DynaRange, as shown below:

Horizontal DynaRange

In the example below, we selected cells D4:D6 and created 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 and clicked Paste. 

In the next dialog box, we selected the database, the cube, and the PALO.DATA function. Then we 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 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 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 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

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.

Allowed nesting

When nesting several DynaRanges, some constellations cannot be resolved and subsequently will lead to conflicts. Nesting of the last sub-section as shown below is allowed:

Also allowed are two DynaRanges that are dependent on each other, i.e., the subset of the inner DynaRange refers to the 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.

image_pdfimage_print