Pivot View Multi-Level Hierarchies

You can easily create multi-level hierarchies in your pivot view in v9.1.

Let’s connect to our example data source - a view called vDMPrep2 on one of our SQL Servers:

If you create a grid on this view you will simply have the All member and then a flat list for each of the available hierarchies – as you can see when I expand Order Date:

This would allow me to select days individually, but a hierarchy with year and month groupings would give a lot more flexibility.Version 9.1 lets you quickly and easily create hierarchies on your data, as shown below. Once you have inserted the initial Grid, go to Mange Connections and you will see a ‘Pivot View’ connection. This is the XLCubed view of the flat SQL based data.

Edit the Pivot View connection to define some multi-level hierarchies:

Let’s set up a date hierarchy with Year, Quarter and Month levels based on Order Date. To do this, drag OrderDate from the right-hand side of the window to the Order Date hierarchy. You can do this as many times as required – once for each level you wish to create.

We will use Year from the pre-defined calculations and then repeat the process for Quarter and Month name. We will also define a hierarchy for Product, which drills from Product Category down to Model. As this is not date-related, we do this by simply dragging ‘Model’ across onto the Product Category dimension. If you accidentally drag it above rather than below ‘Product Category’ these can be re-positioned afterwards.

Finally, let’s include Country as a level on the Region hierarchy – we will tidy up by deleting the Country hierarchy on the original Pivot view. The newly created hierarchies are now available when you add a new Grid or Edit the originally added Grid. Let’s design a new grid with Region and Measures as Filters, Order Date on Columns and Product Categories on Rows.

Drill down into the order date and you’ll see the Years, Quarters and Months as defined as well as Product Categories with a Model level.