6 Replies Latest reply: Jun 17, 2014 1:04 PM by BillE RSS

    Flatten Hierarchy By Filtering Out Unnecessary Limbs

    BillE

      We have numerous hierarchies in our organization that faciliate various reporting views.  Some of those views are slight variations of one another.  One common theme that we have encountered is the need for the same basic view, but with less granularity in terms of rollups.

       

      Example (alphas are limbs, numerics are leafs):

       

      Master Hierarchy

       

      A

           B

                C

                     1

                     2

                D   

                     3

                     4

           E

                5

                6

       

      We need to derive a custom export that eliminates all the in-between nodes and just rolls all the leafs up to Node A.

       

      A

           1

           2

           3

           4

           5

           6

       

      This type of flattening may happen at any level within the hierarchy (not necessary all rolling up to the top level).  So we need a somewhat dynamic way to achieve this.  I am thinking that a property could be built to flag certain key levels that are required for the custom export, but still not sure how to get those key levels flagged as parents for the leafs, regardless of which level the leaf is at in the Master hierarchy.

       

      Any thoughts?    

        • 1. Re: Flatten Hierarchy By Filtering Out Unnecessary Limbs
          Denzz

          Hello BillE,

           

          You can actually design a query filter and include it in your export, the query can be customized to the levels of hierarchy that you want by means of an OR condition. Concurrently one of your OR conditions should be to extract leaf nodes.

           

          Thanks

          Denzz

          • 2. Re: Flatten Hierarchy By Filtering Out Unnecessary Limbs
            BillE

            Hi Denzz,

             

            What kind of OR condition could be written that would not only filter certain limb nodes out, but also realign the parent/child relationships accordingly.  For example:

             

            Master Hierarchy

             

            A

                 B

                      C

                           1

                           2

                      D   

                           3

                           4

                 E

                      5

                      6

             

            We need to derive a custom export that eliminates rollups C & D.

             

            A

              B 

                 1

                 2

                 3

                 4

              E 

                 5

                 6

             

            I can understand creating a formula/property to filter in or out certain rollups.  But we would also need to effectively change the parent value of 1, 2, 3, 4 to B (instead of C or D).  Are you suggesting a formula that checks the immediate parent for the filter property, and if not found, then go to grandparent, then next grandparent and so on until found?

            • 3. Re: Flatten Hierarchy By Filtering Out Unnecessary Limbs
              Denzz

              Hello Bill,

               

              Now, this might get quite complex to the point that we don't achieve it if we don't fix the hierarchy at some point.

              So if we go by fixing the level, say you need everything until the second level and then continued by the leafs

               

              The your query filter should check for-

               

              Level LessthanEqualto 2

              OR

              Leaf Equals True

               

              So this would give you the structure shown below,

              But, if we want say up to Level 2 and then leafs in a given branch but up to level 3 and leafs on another then might go beyond reach.

               

              I hope i am making some sense here , Let me know

               

              A -- Level1

                B  --Level2

                   1 --Leafs

                   2

                   3

                   4

                E 

                   5

                   6

               

              Thanks

              Denzz

              • 4. Re: Flatten Hierarchy By Filtering Out Unnecessary Limbs
                BillE

                Hi Denzz,

                 

                I understand your query filter concept above.  But still not clear on how the parent-child relationship would be adjusted accordingly.  So in your example, we could successfully filter out levels C and D from the export.  But we now need the 'parent' for Leafs 1, 2, 3, 4 to appear to be Node B in the export.  Any thoughts?

                • 5. Re: Flatten Hierarchy By Filtering Out Unnecessary Limbs
                  Denzz

                  Yes in that case we would need to create a custom derived property to skip the intermediate levels.

                   

                  You could use something like this-

                   

                  If(

                  Equals(String,PropValue(Core.Leaf),True),

                  AncestorProp(=,Core.Level,Subtract(PropValue(Core.Level),2),False,Core.Abbrev),PropValue(Core.Parent))

                   

                  Thanks,

                  Denzz

                  • 6. Re: Flatten Hierarchy By Filtering Out Unnecessary Limbs
                    BillE

                    Got it.  Thanks, Denzz!