6 Replies Latest reply on Feb 17, 2020 9:40 AM by Rohit Yeruva

    Hierarchy acting oddly

    Mike Raper

      Okay.  So I'm working on a file with some test data.  In it, there are multiple test items, and the items are grouped into different competencies.  So, there are two columns; competency, and item.


      The structure looks like this:








      So, what I would expect is that if I create a hierarchy of competency/item, if I build a table the competencies would ONLY show rows with the items that are in that competency.  In other words, if I have a table and use that hierarchy, in the rows for awareness, I should only see the four awareness items when I "explode" the table to show both competency and items. 


      But, what I'm seeing instead is all the items in the competency rows, and scores only on the ones where they match.  So again, as an example, in the row for awareness, I get ALL the items, including the commo ones, but only the aware01-04 show scores. 


      Any suggestions?  I can't share the workbook, as it's under an NDA.  But I've never seen a hierarchy act like this.

        • 1. Re: Hierarchy acting oddly
          Rohit Yeruva

          Hey Mike,


          Please find a workbook attached. Is this something that you are looking for?



          1 of 1 people found this helpful
          • 2. Re: Hierarchy acting oddly
            Mike Raper



            Sort of, but not exactly.  Here's what I'm trying to do; I'm using the super-store book to demonstrate.


            In this highlight table, I have the category and region in a cross-tab set up, with sales in my cells.  Category is a hierarchy, with sub-category under it.  So, with category selected, here's what I get:




            Now, if I click on the drill-down plus sign next to cateogry, I get this:




            See how the sub-category is ONLY showing the sub-categories that nest under a particular category?   I didn't have to do anything to make this work; it's just a natural reaction to setting up this table.  No formulas needed.  My problem in my actual workbook is that using this exact set up, what I get is ALL sub-categories showing up under each category, but with so many of them blank (because they have no values), I get a lot of dead space.  It would be like this example above, but with all the technology items showing up in the row for furniture, but with no data.  Make sense?  I can't figure out why it works here in this example, but with an identical set up in my other workbook it doesn't. 



            • 3. Re: Hierarchy acting oddly
              Soham Wadekar

              Hi Mike,


              I could not replicate your issue.

              I think there might be some Aware items in Communication which is giving you the undesirable hierarchy.

              • 4. Re: Hierarchy acting oddly
                Rohit Yeruva

                Hey Mike,


                How did you set up your data? Used any joins? Can you at least show us your database schema? It's hard to pinpoint your problem without looking at your data.



                • 5. Re: Hierarchy acting oddly
                  Mike Raper

                  I finally got it to work.  I really don't know why it didn't at first.  But I removed the hierarchy, put the two dimensions on rows, and it looked like I wanted.  Then I re-created the hierarchy and it behaved the right way.  Really odd, but hey, it works so I'm good now!  Thanks for the help!

                  • 6. Re: Hierarchy acting oddly
                    Rohit Yeruva

                    Awesome! Glad you figured it out. Now, can please close the thread by selecting a correct answer.