Skip to main content

Reading out a hierarchy with the cell reference function

With the new cell reference function (Zeilenbezug), arcplan has created a simple way to read out the current dimension element of a hierarchy. With the introduction of this function, the coding for this problem becomes more straightforward and comprehensible. In the blog, the current solution is explained and compared with the new solution (with the new cell reference function Zeilenbezug).  

Initial situation

The following hierarchy is given.




The objective is to read out the current dimension element of the hierarchy and to depict this in a flat list. The hierarchy object depicted in the figure is designated with the object description hierarchy (Hierarchie). In the following blog, all Hierarchie object references refer to this hierarchy object.    

Current option with two construction objects

The current option consists of 3 steps. Two construction objects are created for this -one for the rows and one for the columns and then the reference formula is applied to it.

Step 1

The index for the columns is determined here. The index for the columns corresponds to the hierarchy depth of the Hierarchie.

In order to determine this index, a column object is created in arcplan. This column object is renamed column index (Spaltenindex). Under the menu item calculation (Berechnung), the stage function [EBENE] is used, in reference to the Hierarchie, in order to determine the depth of the hierarchy (see the following figure). 



Step 2

The index for the rows is determined here. The row index corresponds to only one enumeration from 1 to the quantity of various elements available. A column object is created for this as well and this is designated row index (Zeilenindex). The calculation of this object is implemented with the formulas enumeration [AUFZÄHLEN] and quantity [ANZAHL], in reference to the Spaltenindex (see the following figure).    



Step 3

In the final step, both construction objects are used to read out the final respective element in the hierarchy. To this end, another column object is create which is calculated using the reference formula. A part of the Hierarchie is referred to with the reference. Thanks to the determined row and column indices, this referenced part corresponds to the desired depiction (see the following figure).



More elegant option with the new cell reference function

Now, using the new cell reference formula (Zeilenbezug), the desired result can be facilitated with only one calculation. To accomplish this, we create an object and execute the cell reference with a calculation. The cell reference expects 4 parameters. For one, the object that reference is made to. In this example, this is the Hierarchie. Another parameter which is expected is from the left [VONLINKS] or from the right [VONRECHTS]. For this example, VONLINKS is the correct parameter because the index calculation begins with the country. Then the initial column index and final column index are submitted. In this example, the initial column index and the final column index are the same and correspond to the depth of the hierarchy, which, in turn, is read out with the EBENE function.




Both programming options result in the desired depiction. However, with this new row referencing function, we save on objects and still obtain a straightforward solution.