Skip to content

Hierarchy nodes and hierarchy node properties

Hierarchy node properties are available as scalar variables in any projection node that uses the hierarchy node.

How to configure Hierarchy Nodes and their properties

Using an Excel model

The following tables are used to configure the Hierarchy Nodes and their properties in Excel:

  • Hierarchy:
  • Required.
  • Normally found on the Hierarchy sheet.
  • If this table is missing, it is assumed that the legacy format is used.
  • HierarchyLevelSettings:
  • Optional.
  • Normally found on the HierarchySettings sheet.
  • HierarchyPropertySettings:
  • Optional.
  • Normally found on the HierarchySettings sheet.

The Hierarchy table

Group Country Project Properties → foo bar
Southern Africa South Africa A 1 a
Southern Africa South Africa B 2 b
Southern Africa Namibia A 3 c
Southern Africa Namibia B 4 d
Southern Africa Namibia C 5 e
Southern Africa Botswana A 6 f
Southern Africa Botswana B 7 g
Southern Africa Botswana C 8 h
Southern Africa Botswana D 9 i
  • Headers ending in → separate the table into named sections.
  • Columns before the section header are the hierarchy level names.
  • The optional Properties → section contains property columns.
  • Other sections are ignored, so you could add something like Notes →, and everything to the right of that will have no effect.
  • Blank hierarchy cells indicate a part of the hierarchy tree that has no further branches.

The HierarchyLevelSettings table

This table is optional, and empty by default. In this case, the hierarchy levels are numbered N through 1 from left to right as they appear in the Hierarchy table.

Name hLevel

If you need to change the default hierarchy level numbers, configure every level exactly once in this table.

  • Name matches the headers from the left-most section of the Hierarchy table.
  • hLevel must be unique integers.
Name hLevel
Group 2
Country 5
Project 3

The HierarchyPropertySettings table

This table is optional, and empty by default.

Use the singular HierarchyPropertySettings name when the workbook contains a Hierarchy table. The plural HierarchyPropertiesSettings name is used only by the legacy format and is ignored when a Hierarchy table is present.

Name ResolveMulti
  • Name matches the headers from the Properties → section of the Hierarchy table.
  • Not every property needs a row here.
  • ResolveMulti chooses an aggregation method.
  • The available choices are Error, Min, Max, Mean, FirstOccurrence, and LastOccurrence.
  • Error produces NA() when values differ.
  • The other methods select or aggregate the values in hierarchy-row order.
  • The default value for ResolveMulti is Error.
Name ResolveMulti
foo FirstOccurrence
bar LastOccurrence

Legacy format: named ranges

Workbooks without a Hierarchy table continue using: - Named range HierarchyTreeInclHeaders - Named range HierarchyPropertiesInclHeaders - Named range HierarchyLevels - Table HierarchyPropertiesSettings

Support for this format will be removed in Autory 4.

Using a JSON or YAML model

Using a JSON model or YAML model is more flexible than using the Excel model, but more verbose, and therefore a bit harder to write manually.

There are three keys:

  • hierarchy_nodes: A list of objects representing the hierarchy nodes. Each object contains:
    • name: The name of the node, which must be unique among the node's siblings.
    • level: The level of the node.
    • uuid: A unique identifier for the node. This must be unique within the entire hierarchy.
    • properties: Key-value pairs representing the hierarchy properties. The values are stored as formula strings. E.g., dates are represented as "DATE(2020,12,31)".
  • hierarchy_inheritance: A list of objects representing the parent-child relationships between the nodes. Each object contains:
    • parent: The unique identifier of the parent node.
    • child: The unique identifier of the child node.
  • hierarchy_levels: A list of objects representing the levels of the hierarchy. Each object contains:
    • level: The level number.
    • name: The level name.

The easiest way to understand how this works, is through the examples below.

Examples

A simple hierarchy without properties

Say the Hierarchy table looks like this:

Level3 Level2 Level1 Properties →
TopNode NodeA NodeA1
TopNode NodeA NodeA2
TopNode NodeB NodeB1
TopNode NodeB NodeB2

The equivalent YAML model would look like this:

hierarchy_nodes:
  - name: TopNode
    uuid: TopNode
    level: 3
    properties: { }
  - name: NodeA
    uuid: NodeA
    level: 2
    properties: { }
  - name: NodeB
    uuid: NodeB
    level: 2
    properties: { }
  - name: NodeA1
    uuid: NodeA1
    level: 1
    properties: { }
  - name: NodeA2
    uuid: NodeA2
    level: 1
    properties: { }
  - name: NodeB1
    uuid: NodeB1
    level: 1
    properties: { }
  - name: NodeB2
    uuid: NodeB2
    level: 1
    properties: { }

hierarchy_levels:
  - level: 3
    name: Level3
  - level: 2
    name: Level2
  - level: 1
    name: Level1

hierarchy_inheritance:
  - parent: TopNode
    child: NodeA
  - parent: TopNode
    child: NodeB
  - parent: NodeA
    child: NodeA1
  - parent: NodeA
    child: NodeA2
  - parent: NodeB
    child: NodeB1
  - parent: NodeB
    child: NodeB2

The following hierarchy will be available in the model:

TopNodehLevel = 3NodeAhLevel = 2NodeBhLevel = 2NodeA1hLevel = 1NodeA2hLevel = 1NodeB1hLevel = 1NodeB2hLevel = 1
TopNodehLevel = 3NodeAhLevel = 2NodeBhLevel = 2NodeA1hLevel = 1NodeA2hLevel = 1NodeB1hLevel = 1NodeB2hLevel = 1

A hierarchy with a single node

Say the Hierarchy table looks like this:

Level1 Properties →
TopNode

The equivalent YAML model would look like this:

hierarchy_nodes:
  - name: TopNode
    uuid: TopNode
    level: 1
    properties: { }

hierarchy_levels:
  - level: 1
    name: Level1

hierarchy_inheritance: [ ]

The following hierarchy will be available in the model:

TopNodehLevel = 1
TopNodehLevel = 1

A hierarchy with properties

Say the Hierarchy table looks like this:

Level3 Level2 Level1 Properties → Prop1 Prop2 Prop3 Prop4
TopNode NodeA NodeA1 1 5 9 13
TopNode NodeA NodeA2 2 6 10 14
TopNode NodeB NodeB1 3 7 11 15
TopNode NodeB NodeB2 4 8 12 16

And the HierarchyPropertySettings table on HierarchySettings looks like this:

Name ResolveMulti
Prop1 FirstOccurrence
Prop2 Mean
Prop4 Max

The equivalent YAML model would look like this:

hierarchy_nodes:
  - name: TopNode
    uuid: TopNode
    level: 3
    properties:
      Prop1: "1"
      Prop2: "6.5"
      Prop3: ""
      Prop4: "16"
  - name: NodeA
    uuid: NodeA
    level: 2
    properties:
      Prop1: "1"
      Prop2: "5.5"
      Prop3: ""
      Prop4: "14"
  - name: NodeB
    uuid: NodeB
    level: 2
    properties:
      Prop1: "3"
      Prop2: "7.5"
      Prop3: ""
      Prop4: "16"
  - name: NodeA1
    uuid: NodeA1
    level: 1
    properties:
      Prop1: "1"
      Prop2: "5"
      Prop3: "9"
      Prop4: "13"
  - name: NodeA2
    uuid: NodeA2
    level: 1
    properties:
      Prop1: "2"
      Prop2: "6"
      Prop3: "10"
      Prop4: "14"
  - name: NodeB1
    uuid: NodeB1
    level: 1
    properties:
      Prop1: "3"
      Prop2: "7"
      Prop3: "11"
      Prop4: "15"
  - name: NodeB2
    uuid: NodeB2
    level: 1
    properties:
      Prop1: "4"
      Prop2: "8"
      Prop3: "12"
      Prop4: "16"

hierarchy_levels:
  - level: 3
    name: Level3
  - level: 2
    name: Level2
  - level: 1
    name: Level1

hierarchy_inheritance:
  - parent: TopNode
    child: NodeA
  - parent: TopNode
    child: NodeB
  - parent: NodeA
    child: NodeA1
  - parent: NodeA
    child: NodeA2
  - parent: NodeB
    child: NodeB1
  - parent: NodeB
    child: NodeB2

The following hierarchy will be available in the model:

TopNodehLevel = 3Prop1 = 1Prop2 = 6.5Prop3 =Prop4 = 16NodeAhLevel = 2Prop1 = 1Prop2 = 5.5Prop3 =Prop4 = 14NodeBhLevel = 2Prop1 = 3Prop2 = 7.5Prop3 =Prop4 = 16NodeA1hLevel = 1Prop1 = 1Prop2 = 5Prop3 = 9Prop4 = 13NodeA2hLevel = 1Prop1 = 2Prop2 = 6Prop3 = 10Prop4 = 14NodeB1hLevel = 1Prop1 = 3Prop2 = 7Prop3 = 11Prop4 = 15NodeB2hLevel = 1Prop1 = 4Prop2 = 8Prop3 = 12Prop4 = 16
TopNodehLevel = 3Prop1 = 1Prop2 = 6.5Prop3 =Prop4 = 16NodeAhLevel = 2Prop1 = 1Prop2 = 5.5Prop3 =Prop4 = 14NodeBhLevel = 2Prop1 = 3Prop2 = 7.5Prop3 =Prop4 = 16NodeA1hLevel = 1Prop1 = 1Prop2 = 5Prop3 = 9Prop4 = 13NodeA2hLevel = 1Prop1 = 2Prop2 = 6Prop3 = 10Prop4 = 14NodeB1hLevel = 1Prop1 = 3Prop2 = 7Prop3 = 11Prop4 = 15NodeB2hLevel = 1Prop1 = 4Prop2 = 8Prop3 = 12Prop4 = 16

An unbalanced hierarchy

Say the Hierarchy table looks like this:

Level3 Level2 Level1 Properties →
TopNode NodeA NodeA1
TopNode NodeA NodeA2
TopNode NodeB
TopNode NodeB

The equivalent YAML model would look like this:

hierarchy_nodes:
  - name: TopNode
    uuid: TopNode
    level: 3
    properties: { }
  - name: NodeA
    uuid: NodeA
    level: 2
    properties: { }
  - name: NodeB
    uuid: NodeB
    level: 2
    properties: { }
  - name: NodeA1
    uuid: NodeA1
    level: 1
    properties: { }
  - name: NodeA2
    uuid: NodeA2
    level: 1
    properties: { }

hierarchy_levels:
  - level: 3
    name: Level3
  - level: 2
    name: Level2
  - level: 1
    name: Level1

hierarchy_inheritance:
  - parent: TopNode
    child: NodeA
  - parent: TopNode
    child: NodeB
  - parent: NodeA
    child: NodeA1
  - parent: NodeA
    child: NodeA2

The following hierarchy will be available in the model:

TopNodehLevel = 3NodeAhLevel = 2NodeBhLevel = 2NodeA1hLevel = 1NodeA2hLevel = 1
TopNodehLevel = 3NodeAhLevel = 2NodeBhLevel = 2NodeA1hLevel = 1NodeA2hLevel = 1

A hierarchy with empty or missing properties

Say the Hierarchy table looks like this:

Level3 Level2 Properties → Prop1 Prop2 Prop3 Prop4
TopNode NodeA 1 9
TopNode NodeA 6 10
TopNode NodeB 3 7 15
TopNode NodeB 16

And the HierarchyPropertySettings table on HierarchySettings looks like this:

Name ResolveMulti
Prop1 Mean
Prop2 Mean
Prop3 Mean
Prop4 Mean

The equivalent YAML model would look like this:

hierarchy_nodes:
  - name: TopNode
    uuid: TopNode
    level: 2
    properties:
      Prop1: ""
      Prop2: ""
      Prop3: ""
      Prop4: ""
  - name: NodeA
    uuid: NodeA
    level: 1
    properties:
      Prop1: ""
      Prop2: ""
      Prop3: "9.5"
      Prop4: ""
  - name: NodeB
    uuid: NodeB
    level: 1
    properties:
      Prop1: ""
      Prop2: ""
      Prop3: ""
      Prop4: "15.5"

hierarchy_levels:
  - level: 2
    name: Level2
  - level: 1
    name: Level1

hierarchy_inheritance:
  - parent: TopNode
    child: NodeA
  - parent: TopNode
    child: NodeB

The following hierarchy will be available in the model:

TopNodehLevel = 2Prop1 =Prop2 =Prop3 =Prop4 =NodeAhLevel = 1Prop1 =Prop2 =Prop3 = 9.5Prop4 =NodeBhLevel = 1Prop1 =Prop2 =Prop3 =Prop4 = 15.5
TopNodehLevel = 2Prop1 =Prop2 =Prop3 =Prop4 =NodeAhLevel = 1Prop1 =Prop2 =Prop3 = 9.5Prop4 =NodeBhLevel = 1Prop1 =Prop2 =Prop3 =Prop4 = 15.5