Advertisements

Use hierarchies in PivotTables

Recall from my previous post Create a hierarchy in PowerPivot Data Model, We will continue from where we left our earlier post.

Now we have a Sports hierarchy and Locations hierarchy, we can add them to PivotTables or Power View, and quickly get results that include useful groupings of data.

Prior to creating hierarchies, you had to add individual fields to the PivotTable, and arrange those fields how you wanted them to be viewed.

In this post we will use the hierarchies created in the previous post to quickly refine our PivotTable.

Open the Excel workbook which we used in our previous post and Insert PivotTable as shown in below screen.
ExcelData-17

  • Add field in the PivotTable Medal from Medal Table in the FILTERS area, and Count of Medal from Medal Table in the VALUES area. Your nearly empty PivotTable should look like the following screen.

ExcelData-18

  • From the PivotTable Fields area, drag SDE from the Events table to the ROWS area. Then drag Locations from the Hosts table into the COLUMNS area. Just by dragging those two hierarchies, your PivotTable is populated with a data, all of which is arranged in the hierarchy we defined in the previous steps. Your screen should look like the following screen.

ExcelData-19

  • You can expand any of those Sports in the PivotTable, which is the top level of the SDE hierarchy, and see information in the next level down in the hierarchy (discipline). If a lower level in the hierarchy exists for that discipline, you can expand the discipline to see its events. You can do the same for the Location hierarchy, the top level of which is Season, which shows up as Summer and Winter in the PivotTable.

ExcelData-20

ExcelData-21
By dragging those two hierarchies, you quickly created a PivotTable with interesting and structured data that you can drill into, filter, and arrange.

I will come up with more details and other options on this topic in my upcoming posts. Till then keep learning and practicing.

Advertisements

Important

Most of the contents you find in this blog will be either inherited from MSDN or Navision Developer IT Pro Help. Some places images are also directly taken from these sites. Purpose is simple to try those stuffs and re-produce adding few things as per my understanding to make easy understanding for others and quick reference.

Here nothing under my own brand or authorship of the content. At any point of time we are just promoting Microsoft stuffs nothing personnel with same.

Hope stuffs used here will not violate any copyright agreement with them. In case by mistake or in-intestinally it happens and the Microsoft feels these should not be used Microsoft have full right to inform me about same and will be glad to take down any such content which may be violating the norms.

Purpose is to promote Navision and share with community.

FB Profile

Like FB Page

%d bloggers like this: