Hierarchies are one of the long awaited features that will now be available in the latest version of PowerPivot. Hierarchies are useful because it allows a single click for a user to bring in all the fields that exist in a natural table hierarchy.  Normally the user would have to drag in all fields individually so this can be a time saver especially if it is an 8 level hierarchy. 

Hierarchies are very simple to create and easy to interact with in the reporting layer as well.  To get started you must be in the Diagram View of the PowerPivot Window.  Find the table you wish to create the hierarchy on and either right-click on it or click the Create Hierarchy button shown below. 

After the hierarchy is created you will immediately be able to rename it.  After renaming it you drag the fields from the table down to the hierarchy that was just created.  You can also rename the fields so they are more user friendly by right-clicking on each individual field.  Here I’ve renamed my hierarchy and all my fields to something my users are more familiar with.

Let’s take a look at some of the options you are given when right-clicking on fields:

·         Hide Source Column Name:  Removes the source column name that appears in parenthesis next to the column name that you have in the hierarchy.  This only removes it from the diagram view because the source column is never shown in the reporting layer.

·         Move Up:  Moves a field to a higher level in the hierarchy.

·         Move Down:  Moves a field to a lower level in the hierarchy.

·         Remove from Hierarchy:  Takes to field completely out of the hierarchy.

·         Rename:  Allows you to rename a hierarchy field to something more user friendly.

When you are happy with the hierarchy you have created you can use it in the reporting layer of excel.  The hierarchy interacts with the pivot table just like it would if it had come from an Analysis Services cube.