Flatten Hierarchical data with Web Intelligence formulas

• 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)] …)