Show TOC

Function documentationCellKey Range Locate this document in the navigation structure

 

Define a CellKey range to overwrite a member ID used in the ColKey or RowKey ranges.

Features

  • The optional CellKey range represents a range of cells that you can use to redefine the members that apply to the corresponding cells for their respective dimensions. Corresponding cells are the cells in the data range that have a corresponding position relative to the top-left corner of the CellKeyRange.

  • The CellKey range specifies which cells have, for one or more dimensions, a current view that is cell-specific. This functionality allows you to override the current view, or part of it, for any cell in the data range, as defined by the page keys, the column keys, and the row keys.

  • You can use the CellKey range only on static reports (reports that do not include EvDRE-driven expansions). The CellKeyRange must be equal to or smaller than the data range for which it redefines the current view for a cell.

Activities

To define the CellKey range, from Interface for Excel, open the EvDRE report or input schedule you are building. In the CellKey range rows of the Key range parameter table, enter a range of cells that contain the cell-specific members.

Example

In the following example, the first column retrieves the values of each month of 2007 while the second column retrieves the values of each month of 2008. You achieve this result by using a CellKey range made up of only one Row, combined with a Row Expansion. Since the report contains a Row expansion, the expansion automatically creates an expanded copy of the CellKey range to the right of the report Data Range. This copy is the actual CellKey range that the function uses to define the key of the cells of the data range.

The first cell of the original CellKey range (G2) contains a formula that has been copied into the expanded CellKey range to the right. The result is a key that varies row by row based on the PERIOD property of the corresponding row key, while the YEAR is controlled by the content of cell E2. The example below shows how this technique can be used to build an inter-company matching report. In column H of the example the inter-company member selected in ROWS need to be swapped with the corresponding entity member, while the inter-company member must be the one corresponding to the entity selected in PAGE.

The result is obtained placing the correct formula in the second column (H) of the CellKey range G3:H3, and letting the expansion build the correct keys in column K. A more efficient way to build an inter-company matching report is to use two EVDRE functions overlaid on the same data range.