Pivot View Multi-Level Hierarchies
In this example we will connect to SQL Server.
Creating a grid on this connection will prompt us to define the query. This will then automatically create a pivot view (alternatively, right-click on the connection in the connection screen and select 'Add Pivot View').
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, for example when we expand Order Date. This would allow us to select days individually, but a hierarchy with year and month groupings would give a lot more flexibility.
Editing a Pivot View
Go to Manage 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.
This will open the Pivot View Definition Dialog.
Drag and drop fields from the right side over to the desired hierarchy. For example, we can define the Product hierarchy by dragging ‘Model’ across onto the Product Category dimension.
Levels can be re-positioned by dragging and dropping levels above/below each other.
Right click on a hierarchy or level to rename or delete it.
Let’s set up a date hierarchy with Year, Quarter and Month levels based on Order Date.
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.
Right click to use the pre-defined calculations and then repeat the process for Quarter and Month name.
Adding a Grid
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.