4 Replies Latest reply on Feb 17, 2020 7:41 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:

       

       

      Competency
      Item
      Awareness

      Aware01

      AwarenessAware02
      AwarenessAware03
      AwarenessAware04
      CommunicationCommo01
      CommunicationCommo02
      CommunicationCommo03

       

       

      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?

           

          //Rohit

          • 2. Re: Hierarchy acting oddly
            Mike Raper

            Rohit,

             

            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:

             

            1.png

             

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

             

            2.png

             

            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. 

             

            Thanks!

            • 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.

                 

                //Rohit