Hierarchy in PowerPivot with Calendar table
In order to use create hierarchy field with your Calendar Table, you will first have to extract the necessary date component. If you have no idea what’ I’m referring to, I’ve talk about in the previous part of Calendar Table with DAX articles.
If you are dealing with many dimension table (AKA database table) with many fields in it, in a way hierarchy can help makes it easier for you to manage those fields into one. I guess you know how crazy it can be scrolling up and down searching for that field while building report using Pivot Table of creating visualization while using Power View.
Why you need to create hierarchy?
Hierarchy in a way is about group the relevant fields into one field. Example if you have geographic data or date data, you can add the hierarchy that had the year at the top, follow by quarter then month (Example: Year [2010] à Quarter [Q1] à Month [January]). Meanwhile, for the geographic data you can add the region at the top, then follow by country then states (Example: Region [Asia]à Country [Malaysia]à State [Kuala Lumpur]). Creating the hierarchy can help you simplified the view and the number of fields being displayed in the Pivot Table field list, and also you can create the coolest type of drill down/up report. Don’t understand how drilling up or down works? You gotta continue reading to see how cool your report is turning out to be.
To work with hierarchy alone is not going to be enough. I will also walk you through how to hide some of the column from the client view (you know those fields where you will not be needing while creating report, usually are helper columns), so you are not drowning yourself in those fields from the tables in Data Model.
Creating Date field hierarchy
Since we’re still in the middle of talking about date dimension table, so the example is definitely date related.
After extracting all the date component (If you have been following me since Part 1), you should have a calendar as shown below:
To create the hierarchy, you will have to change your Data Model window’s view to Diagram View.
This is how the diagram view of your calendar table looks like.
Right click at the field that will be sitting at the most top and select “Create Hierarchy”
Create a name for the hierarchy
Gradually add all the field that to the “Period” hierarchy. You can do it by drag and drop or right click and select from the existing hierarchy.
After you have finish adding the fields to the hierarchy, this is how is should look like.
Now close the Data Model window, back to the Excel sheet and insert a pivot table. check out the “Period” field from the Pivot Table Field list.
The rest of the fields that are not being grouped in the hierarchy can be found in the “More fields” section. If you were to expand the “More Fields” section, you should be able to find all the fields from Date table.
Editing the Hierarchy
If you would like to change the position of those fields in the hierarchy field, you can simple drag and drop the position, or once you right click the field select move up or down to reposition those fields.
Renaming and deleting Hierarchy
You can also rename the hierarchy field name by right clicking the Hierarchy and “Rename”.
Create Pivot Report with Hierarchy Field
This is the most awesome part after creating the hierarchy. It makes the process of creating report so much easier. Let’s say, I’m going to create a report showing my sales over the starting of the transaction until the last date of the transaction.
Simply drag the “Period” field into the ROWS/ COLUMNS in the Pivot Table builder area. You should be able to get the result as shown in the figure below:
Drill Down report
Notice while you are selecting the field in the Pivot Table, there is a drill down command being enabled in the Analyze Tab? That the awesome part of it!!!!
After selecting the “Drill Down” button, the whole report now showing the Sales by Quarter. If just now your cursor was selecting at the FY2011 and the drill down report will be the report based on FY2011. For my version of report, it is now showing the report of the Sales by Financial Quarter by FY2011.
After drilling it down, you can also select “Drill Up” to go back up.
Hide fields from Client Tools
Alright, come to the last part of clearing up the client view. Client view which mean the area where you build reports, Pivot Table field pane. Back to the Data Model window, you can do this either in the Data View or Diagram View.
The reason why we created the hierarchy field in the first pace is to group the necessary field in one, which also means the other fields are not going to be needed in report creation. Hence, to clearing up the view while building report, I will usually hide them.
Right click the field you wish to hide and select “Hide from Client Tools”.
After hiding them fields, you should see the column turns semi-transparent. This means you have successfully hid them from the Client View.
To unhide them, simply right click and select “Unhide from Client Tools”
