Specification: Hierarchy Fusion XL Spreadsheet Design

This document formally specifies the tabular XLSX representation of an SDMX Hierarchy (HierarchyBean) artifact as produced by the HierarchyXLSXWriterEngine. The specification is aligned with the conventions established by the existing FusionXL XLSX writer engines.

For codelist alias generation rules see codelist-aliases.md.

1. General Layout

A single worksheet contains the entire hierarchy. Content is organised into four sequential regions, each separated by exactly one blank row. All cell positioning uses the ExcelWriterEngine cursor model (row-major, auto-advancing columns).

1.1 Sheet Naming

The worksheet tab follows the standard FusionXL convention:

HCY|<AgencyId>#<Id>(<Version>)

Example: HCY|SDMX#SIMPLE_HIERARCHY(1.0)

The type code HCY is already mapped in AbstractMaintainableXLSXWriterEngine.getType() for SDMX_STRUCTURE_TYPE.HIERARCHY.

2. Region 1 — Maintainable Header

Rows 0–12. A vertical key-value layout in columns A and B.

Column A cells are styled HEAVY_EMPHASIS (bold + grey background). Column B cells contain the corresponding value as plain text.

2.1 Standard Maintainable Fields (Rows 0–10)

Written by AbstractMaintainableXLSXWriterEngine.writeStructure(). These rows are common to all FusionXL structure types.

Row	Column A (label)	Column B (value)	Source
0	`FusionXL Version`	`1.0`	Constant
1	`Type`	`Hierarchy`	`bean.getStructureType().getUrnClass()`
2	`Agency Id`	e.g. `SDMX`	`bean.getAgencyId()`
3	`Id`	e.g. `SIMPLE_HIERARCHY`	`bean.getId()`
4	`Version`	e.g. `1.0`	`bean.getVersion()`
5	`Name (<locale>)`	Localized name	`TextTypeUtil.getDefaultLocale(bean.getNames())` — locale in the label is determined dynamically from the resolved `TextTypeWrapper`
6	`Description (<locale>)`	Localized description or empty	`TextTypeUtil.getDefaultLocale(bean.getDescriptions())` — locale in the label is determined dynamically
7	`Start Date`	ISO date or empty	`bean.getValidityPeriod().getStartPeriod().getDateInSdmxFormat()` (when non-null; guarded by `hasValidityPeriod()` and `hasStartPeriod()`)
8	`End Date`	ISO date or empty	`bean.getValidityPeriod().getEndPeriod().getDateInSdmxFormat()` (when non-null; guarded similarly)
9	`Final`	`TRUE` or `FALSE`	`bean.isFinal()` — returns primitive `boolean`
10	`Structure URL`	URL string or empty	`bean.getStructureURL()`

The superclass leaves the cursor on row 11, column 0 after writing these rows.

2.2 Hierarchy-Specific Fields (Rows 11–12)

Written by HierarchyXLSXWriterEngine immediately after the super call, continuing the key-value layout.

Row	Column A (label)	Column B (value)	Source
11	`Is External Reference`	`TRUE` or `FALSE`	`bean.isExternalReference()` — returns primitive `boolean`
12	`Has Formal Levels`	`TRUE` or `FALSE`	`bean.hasFormalLevels()`

2.3 Section Separator

Row 13 is a blank row (a createNewRow() call with no cells written). This separates Region 1 from Region 2.

3. Region 2 — Levels

Conditional: This region is present only when bean.getLevel() != null (i.e. the hierarchy defines levels). When absent, the blank separator after Region 1 is followed directly by Region 3.

3.1 Section Title Row

A single cell in column A containing the text Levels, styled HEAVY_EMPHASIS.

3.2 Column Header Row

All cells styled HEAVY_EMPHASIS.

Column	Header
0	`Id`
1	`Name Locale`
2	`Name`

3.3 Data Rows

One row per level in the LevelBean linked list, traversed from the root level (bean.getLevel()) to the deepest child via level.getChildLevel().

Column	Value	Source
0	Level identifier	`level.getId()`
1	Language code	Locale of `level.getName()`, e.g. `en`
2	Level name	`level.getName()`

Levels are written in chain order (root → leaf), matching the SDMX schema ordering. Context: One Level may only have one child Level, so the levels form a single chain rather than a tree.

3.4 Section Separator

A blank row follows the last level data row.

4. Region 3 — Codelist Aliases

This region maps each distinct codelist referenced in the hierarchy to a short, worksheet-scoped alias. See codelist-aliases.md for the full alias generation specification.

4.1 Section Title Row

A single cell in column A containing the text Codelist Aliases, styled HEAVY_EMPHASIS.

4.2 Column Header Row

All cells styled HEAVY_EMPHASIS.

Column	Header
0	`URN`
1	`Alias`

4.3 Data Rows

One row per distinct codelist referenced by any HierarchicalCodeBean in the hierarchy tree. The set of codelists is collected by recursively traversing all HierarchicalCodeBean entries and extracting the codelist-level URN from each code reference.

Column	Value	Source
0	Truncated code reference URN — derived from `hCode.getCodeReference().getTargetUrn()` with the code-level suffix stripped (everything after the closing `)`). The resulting URN retains the `codelist.Code=` class prefix.	Derived from `hCode.getCodeReference()`
1	Generated alias	See codelist-aliases.md §4

The alias has to uniquely identify the codelist within the worksheet. Further details can be seen here: codelist-aliases.md §4.

4.4 Section Separator

A blank row follows the last alias data row.

5. Region 4 — Hierarchical Codes

The main data table. Represents the recursive HierarchicalCodeBean tree as a flat, row-per-code table.

5.1 Section Title Row

A single cell in column A containing the text Hierarchical Codes, styled HEAVY_EMPHASIS.

5.2 Column Header Row

All cells styled HEAVY_EMPHASIS. The columns consist of a fixed prefix (5 columns) followed by a dynamic suffix (one column per formal level).

Fixed columns (0–4):

Column	Header	Description
0	`Codelist Alias`	The worksheet-scoped alias (from Region 3) identifying which codelist this code belongs to. Resolves to the full codelist URN via the alias map.
1	`Code Id`	The identifier of the referenced code within its codelist — i.e. the code that this hierarchical code points to, obtained from `hCode.getCodeReference()`.
2	`Level Id`	An explicit level reference for this hierarchical code, written only when the code carries a `levelRef` override (see §5.4 below). Empty when the code's level is implicitly determined by its nesting depth.
3	`Valid From`	Optional validity start date (`SdmxDate`). The date from which this hierarchical code is considered valid. Empty if not specified.
4	`Valid To`	Optional validity end date (`SdmxDate`). The date until which this hierarchical code is considered valid. Empty if not specified.

Dynamic columns (5+):

The number of HCode columns equals max(levelCount, maxDepth), where levelCount is the number of levels defined on the hierarchy (0 if none) and maxDepth is the maximum observed tree depth, determined by pre-scanning the hierarchy tree.

Importantly, the HCode Column header names do not enforce a specific pattern and any string is technically valid. However, it is recommended to follow this naming pattern:

The column header for each dynamic column at depth position i (1-indexed) is determined by the following rule:

If hasFormalLevels = true AND a level exists at position i in the level chain: the header is HCode (<LevelName>), where <LevelName> is the human-readable name of the level. If the level has no name (null), the code falls back to using the level's identifier (level.getId()). The relevant pattern is: java TextTypeWrapper tt = TextTypeUtil.getDefaultLocale(level.getNames()); String levelName = (tt != null) ? tt.getValue() : level.getId(); header = "HCode (" + levelName + ")";
Otherwise (i.e. hasFormalLevels = false, OR hasFormalLevels = true but the depth exceeds the number of defined levels): the header falls back to the generic depth-based pattern HCode (Depth <i>).

This means: - When hasFormalLevels = false, depth-based headers are always used regardless of whether levels are defined on the hierarchy. Non-formal levels serve an advisory/descriptive purpose only and do not influence column naming. - When hasFormalLevels = true and the observed tree depth exceeds the number of defined levels, the columns up to the level count use level names and the remaining deeper columns use depth-based headers. - When no levels are defined at all, depth-based headers are used for all columns.

Example 1 — two formal levels named ROOT_LEVEL and CHILD_LEVEL, depth ≤ 2:

Column 5	Column 6
`HCode (ROOT_LEVEL)`	`HCode (CHILD_LEVEL)`

Example 2 — hasFormalLevels = false (even with levels defined), depth = 2:

Column 5	Column 6
`HCode (Depth 1)`	`HCode (Depth 2)`

Example 3 — two formal levels but tree depth = 3 (codes exceed level count):

Column 5	Column 6	Column 7
`HCode (ROOT_LEVEL)`	`HCode (CHILD_LEVEL)`	`HCode (Depth 3)`

5.3 Data Rows — Tree Flattening

Each node produces exactly one row.

The representation is independent of the ordering of rows. However, it recommended to write the rows in depth-first traversal order (pre-order) to preserve a natural reading flow where parent codes appear before their children. The ancestor path is accumulated during traversal and passed down to child invocations.

Fixed columns:

Column	Value	Source	Required
0	Codelist alias	Resolved from Region 3 alias map via the code's codelist URN	Always
1	Code identifier within its codelist	`hCode.getCodeId()` — the referenced code's identifier (see §7.11 for the distinction between this and `hCode.getId()`)	Always
2	Level identifier	`hCode.getLevel(false).getFullIdPath(false)` if present; empty otherwise	Optional
3	Validity start date	`hCode.getValidFrom()` formatted as ISO date; empty if null	Optional
4	Validity end date	`hCode.getValidTo()` formatted as ISO date; empty if null	Optional

The HCode columns encode the path from the root to the current node in the hierarchy tree. These columns use the hierarchical code's own identifier (hCode.getId() from IdentifiableBean), which may differ from the referenced code's identifier (hCode.getCodeId()). See §7.11 for details on this distinction.

For a code at depth d (0-indexed, where root codes are depth 0):
Columns 5 through 5+d−1 contain the code identifiers of each ancestor, written into the column corresponding to that ancestor's level.
Column 5+d contains the current code's identifier.
Columns beyond 5+d are left empty — no cell value is written. The cells must not contain placeholder values such as "-", "NA", or "N/A". They are simply blank. This means that nodes at shallower depths will have trailing empty cells in the HCode columns, while nodes at the maximum depth will populate all HCode columns.

The ancestor path is accumulated during recursive traversal and passed down to child invocations.

Worked example (SIMPLE_HIERARCHY):

Row	Codelist Alias	Code Id	HCode (ROOT_LEVEL)	HCode (CHILD_LEVEL)
1	`CL_SIMPLE`	`ROOT`	`ROOT`
2	`CL_SIMPLE`	`CHILD1`	`ROOT`	`CHILD1`
3	`CL_SIMPLE`	`CHILD2`	`ROOT`	`CHILD2`

Row 1: Root code ROOT at depth 0 — populates only the first HCode column (ROOT). The second HCode column (CHILD_LEVEL) is left empty because this node's depth (0) is less than the maximum depth (1). No placeholder value is written.
Rows 2–3: Children of ROOT at depth 1 — the parent's id (ROOT) is repeated in the ROOT_LEVEL column, and the child's own id populates the CHILD_LEVEL column. Both HCode columns are populated because these nodes are at the maximum depth.

5.4 Explicit Level References (Level Id Column)

The SDMX information model provides two mechanisms for assigning a level to a hierarchical code:

Implicit (by nesting depth): When hasFormalLevels = true, each code is automatically assigned to the level whose position in the level chain matches the code's nesting depth. A root code maps to Level 1, its children to Level 2, and so on. No Level Id value is written — the assignment is derived structurally.
Explicit (per-code override): A HierarchicalCodeBean may carry a levelRef string that directly references a level defined in the hierarchy. This is accessed via hCode.getLevel(false) — the false parameter ensures only explicitly-set references are returned, not depth-derived defaults.

An explicit level reference is only necessary when the nesting depth of the hierarchical code does not correspond to the nesting depth of the level to which it belongs (i.e. the hierarchical code is to skip down a level).

Importantly, if both implicit levels (via hasFormalLevels = true) and explicit level references are present, the explicit reference takes precedence and overrides the implicit depth-based assignment. This allows for flexible hierarchies where most codes follow a regular level structure but specific codes can deviate as needed.

Use cases for explicit level linking:

Level skipping: A code nested at depth 1 may logically belong to depth 3 (e.g. a country listed directly under a "World" root, skipping the continent level). The explicit reference overrides the depth-derived assignment.
Informal level hierarchies (hasFormalLevels = false): When the hierarchy does not enforce formal levels but levels are still defined for advisory/descriptive purposes, individual codes can opt into level membership via explicit references. In this case there is no automatic depth-based assignment, so any level association must be explicit.

The writer calls hCode.getLevel(false) and writes the value of level.getFullIdPath(false) into column 2 only when a non-null result is returned. When the level is implicitly derived (the common case with formal levels), column 2 remains empty.

6. Complete Spreadsheet Example

For reference, the complete row-by-row layout of the SIMPLE_HIERARCHY example:

Row	A	B	C	D	E	F	G
0	`FusionXL Version`	`1.0`
1	`Type`	`Hierarchy`
2	`Agency Id`	`SDMX`
3	`Id`	`SIMPLE_HIERARCHY`
4	`Version`	`1.0`
5	`Name (en)`	`Simple Hierarchy Example`
6	`Description (en)`
7	`Start Date`
8	`End Date`
9	`Final`	`TRUE`
10	`Structure URL`
11	`Is External Reference`	`FALSE`
12	`Has Formal Levels`	`TRUE`
13	(blank)
14	`Levels`
15	`Id`	`Name Locale`	`Name`
16	`Level1`	`en`	`ROOT_LEVEL`
17	`Level2`	`en`	`CHILD_LEVEL`
18	(blank)
19	`Codelist Aliases`
20	`URN`	`Alias`
21	`urn:sdmx:org.sdmx.infomodel.codelist.Code=EXAMPLE:CL_SIMPLE(1.0)`	`CL_SIMPLE`
22	(blank)
23	`Hierarchical Codes`
24	`Codelist Alias`	`Code Id`	`Level Id`	`Valid From`	`Valid To`	`HCode (ROOT_LEVEL)`	`HCode (CHILD_LEVEL)`
25	`CL_SIMPLE`	`ROOT`				`ROOT`
26	`CL_SIMPLE`	`CHILD1`				`ROOT`	`CHILD1`
27	`CL_SIMPLE`	`CHILD2`				`ROOT`	`CHILD2`

7. (Edge) Cases

The following cases illustrate various cases worth discussing. The writer implementation must handle all of these cases correctly.

7.1 No Levels defined

Region 2 (Levels) is omitted entirely — no title row, no column headers, no data rows.
The HCode dynamic columns (5+) use depth-based headers (HCode (Depth 1), HCode (Depth 2), …) determined by pre-scanning the maximum tree depth.
This is a straightforward application of the general rule in §5.2: since no levels exist, every column falls back to depth-based naming.

7.2 Empty Hierarchy (No Hierarchical Codes)

The SDMX schema requires a hierarchy to contain at least one hierarchical code. The Java information model (HierarchyBeanImpl) does not enforce this minimum cardinality — getHierarchicalCodeBeans() can return an empty list at runtime without throwing a validation error. Therefore, while a schema-valid hierarchy will always contain at least one hierarchical code, the writer should still handle the empty case defensively.

If bean.getHierarchicalCodeBeans() returns an empty list, the writer throws an SdmxException indicating that the hierarchy is invalid due to missing hierarchical codes.

7.3 Multiple Codelists Referenced

A hierarchy can reference codes from different codelists. Region 3 will contain multiple alias rows, and Region 4 data rows will use different alias values in column 0. Example: HCL_TEST_AREA references both CL_AREA and CL_ACTIVITY.

7.4 Alias Collisions

When multiple referenced codelists share the same identifier (e.g. same id but different agency or version), the alias generator appends numeric suffixes (_1, _2, …). See codelist-aliases.md §4.2 for the full collision resolution specification.

7.5 Deep Nesting (3+ Levels)

A hierarchy with N formal levels and a maximum tree depth D produces max(N, D) HCode columns. When D > N, the first N columns use level-name headers (HCode (<LevelName>)) and columns N+1 through D fall back to depth-based headers (HCode (Depth <i>)). Each data row's HCode cells trace the full ancestor path from root to the current node. Column count grows linearly with tree depth. The flattening algorithm is identical regardless of depth.

7.6 Multiple Root Codes

A hierarchy may have multiple top-level HierarchicalCodeBean entries (multiple roots). Each root and its subtree is written sequentially in the order returned by bean.getHierarchicalCodeBeans(). The depth-first traversal applies independently within each root's subtree.

7.7 Validity Dates on Hierarchical Codes

HierarchicalCodeBean.getValidFrom() and getValidTo() are optional SdmxDate fields. When present, columns 3 and 4 contain formatted date strings. When absent (null), the cells are left empty.

7.8 Explicit Level References on Hierarchical Codes

See §5.4 for the full description of implicit vs explicit level assignment. As an edge case: when hasFormalLevels = true and levels are defined on the hierarchy, some codes may carry explicit levelRef overrides that populate column 2 while most codes leave it empty. The writer must handle both states within the same hierarchy.

7.9 Single Root, No Children

A hierarchy with exactly one root code and no children produces a single data row. Only the first HCode column is populated; all subsequent HCode columns are blank.

7.10 Internationalization (Multiple Locales)

The specification currently shows a single locale (en) for level names and the maintainable header. If a hierarchy or its levels have names in multiple languages, additional locale-specific rows or columns may be needed. The standard maintainable header already supports this via multiple Name (<locale>) / Description (<locale>) rows. Level names in Region 2 follow the same Name Locale + Name column pattern, supporting one locale per row. Multiple locales for the same level would require multiple rows or an extended column layout — the exact strategy should match the convention used by other XLSX writers in the codebase.

7.11 Hierarchical Code Id vs Referenced Code Id

The HierarchicalCodeBean has its own id (from IdentifiableBean) which serves as the node identifier within the hierarchy tree. It separately references an external code via getCodeReference(). These two identifiers may differ (even though in most real world applications, they are the same). The Code Id column (column 1) should contain the referenced code's identifier, while the HCode columns should contain the hierarchical code's own id.

7.12 Shared Child Across Multiple Roots

A HierarchicalCodeBean may appear as a child under more than one root (or under more than one parent in general). Since the XLSX format flattens the tree via depth-first traversal, a shared code will produce one row per occurrence — each with a different breadcrumb path in the HCode columns reflecting its position within its respective subtree. The same Code Id value will appear in column 1 on multiple rows, but the HCode columns will differ because the ancestor path is different for each occurrence.

Example: A hierarchy with roots ROOT1 and ROOT2, where CHILD2 appears under both:

Codelist Alias	Code Id	HCode (Depth 1)	HCode (Depth 2)
`CL1`	`ROOT1`	`ROOT1`
`CL1`	`CHILD1`	`ROOT1`	`CHILD1`
`CL1`	`CHILD2`	`ROOT1`	`CHILD2`
`CL1`	`ROOT2`	`ROOT2`
`CL1`	`CHILD2`	`ROOT2`	`CHILD2`
`CL1`	`CHILD3`	`ROOT2`	`CHILD3`

Note that CHILD2 / Code Id = CHILD2 appears on rows 3 and 5 with different breadcrumb paths.

7.13 Uneven Tree Depth

A hierarchy may contain subtrees of different depths. For example, one root may have children and grandchildren (depth 3) while another root has no children at all (depth 1). The total number of HCode columns is determined by the maximum depth across all subtrees in the hierarchy. Nodes in shallower subtrees will have trailing empty cells in the HCode columns beyond their own depth.

Example: A hierarchy with a deep root (DEEP_ROOT → MID → LEAF) and a shallow root (SHALLOW_ROOT):

Codelist Alias	Code Id	HCode (Depth 1)	HCode (Depth 2)	HCode (Depth 3)
`CL1`	`DEEP_ROOT`	`DEEP_ROOT`
`CL1`	`MID`	`DEEP_ROOT`	`MID`
`CL1`	`LEAF`	`DEEP_ROOT`	`MID`	`LEAF`
`CL1`	`SHALLOW_ROOT`	`SHALLOW_ROOT`

The writer must pre-scan the entire hierarchy tree to determine the maximum depth before writing the HCode column headers. The number of dynamic columns equals the maximum depth, regardless of how deep any individual subtree is.

7.14 Non-Formal Levels (`hasFormalLevels = false` with levels defined)

A hierarchy may define levels (bean.getLevel() != null) while having hasFormalLevels = false. In this configuration, levels serve an advisory or descriptive purpose — they are not formally enforced by the hierarchy structure. The Levels region (Region 2) is still written so the level metadata is preserved, but the level names must not be used for HCode column headers. Instead, all HCode columns use the depth-based naming pattern (HCode (Depth 1), HCode (Depth 2), …).

This distinction matters because non-formal levels do not guarantee a one-to-one correspondence between nesting depth and level position. Using level names as column headers would imply a formal mapping that does not exist, which could be misleading to users.