Flatten Hierarchical data with Web Intelligence formulas
Tags:
- Original data
Level column is just determined by using formula: “=[CUST_HIER].Depth”
- List of Variables used in document
Example of formulas:
Customer - L 00
=If ([CUST_HIER].Depth = 0) Then [CUST_HIER] Else If
([CUST_HIER].Depth > 0) Then Previous([Customer - Level 00
(internal)];1;NoNull)
Customer - Level 00 (internal)
=If ([CUST_HIER].Depth = 0) Then [CUST_HIER]
Customer - L 01
=If ([CUST_HIER].Depth = 1) Then [CUST_HIER] Else If ([CUST_HIER].Depth
> 1) Then Previous([Customer - Level 01 (internal)];1;NoNull)
Customer - Level 01 (internal)
=If ([CUST_HIER].Depth = 1) Then [CUST_HIER]
Other formulas are constructed with same pattern, incrementing depth one by one.
All Variables are qualified as Detail of base Hierarchy object.
- Possible outputs based on these objects
Repeating parents
First column contains base hierarchical object, and font is same color as background, this way we keep only expand-collapse feature with flattened table. This is also mandatory to have context and so a correct order in different columns.
If this column is not needed, then simply hide it to keep it in same table.
Other columns contain non-internal variables ([Customer - L 00], [Customer - L 01] …)
Non-repeating parents
Same first column with invisible content from [CUST_HIER]
Following columns contain variables tagged as “internal”, in same order of depth ([Customer - Level 00 (internal)], [Customer - Level 01 (internal)] …)