15 Replies Latest reply: Apr 7, 2015 5:23 PM by Francois Tremblay RSS

    Unable to create hierarchy in PivotTable

      This is how my dataset looks like:

       

      ItemType       ItemCategory         ItemName

      ItemType1      ItemCat1             Item1

      ItemType1      ItemCat1             Item2

      ItemType1      ItemCat2             Item3

      ItemType2      ItemCat1             Item4

      ItemType2      ItemCat2             Item5

      ItemType2      ItemCat2             Item6

      ItemType3      ItemCat1             Item7

      ItemType3      ItemCat2             Item8

      ItemType3      ItemCat1             Item9

       

      I am trying to display this in PivotTable so that i can get tree view kind of structure with ItemCategory as Row rather than column.

       

      ItemType       ItemCat1       ItemCat2

      -ItemType1     Item1          Item3

                           Item2

       

      -ItemType2      Item4          Item5

                                             Item6

       

      -ItemType3      Item7          Item9

                           Item8

       

      To achieve this, this is what i did.

       

      I created ItemType and ItemCategory as Dimensions and ItemName as Expression. But it does not show me the data in the ItemName column.

       

      I created ItemType, ItemCategory and ItemName as Dimensions, but it does not let me drag and drop ItemCategory column on top of ItemName. It however shows the data.

       

      I created ItemType, ItemCategory and ItemName as Dimensions and ItemName as Expression, it allows me to drag and drop the ItemCategory on top of ItemName expression column but the result is not what i am looking for.

       

      What am i missing here?