A whole slew of improvements to Power View have arrived today with the preview release of Office 2013. It’s not just that Power View is now component of Excel on the desktop but that Power View itself can do many more things than in its original release as part of Reporting Services 2012. Here’s just one example: drill down.
Drill down (and drill up) is the ability to negotiate a vertical hierarchy. For example, it’s very easy to understand a calendar as a hierarchy – a year inherits four quarters, each of which inherit three months, each of which inherit around thirty days. Generally a dashboard tool allows a user to visualise data grouped by the top of the hierarchy and then use some simple mechanism like a click to drill down through any path. Power View now offers this facility – one step on the road to an analytic/dashboarding tool maybe?
Here’s how to achieve it.
For a start we need to create something that actually is a hierarchy*. This can be done in PowerPivot’s Data Model designer. It’s easy to do:
- After you’ve imported a table that contains hierarchical data, switch to Diagram View.
- Click the Create Hierarchy button on the table’s title.
- Drag each attribute from the table to the right place in the hierarchy.
- Give the hierarchy and its attributes a useful, meaningful name.
Power View (and any other client tool you use this model with) will now be able to use this hierarchy.
This causes a new tab to appear in the workbook, with the Power View interface. For this example I have just added some of the tables from the Adventure Works DW sample database and created a SUM measure on the [InternetSales].[SalesAmount] field. With this measure checked, the visualisation changed to a column chart, and the Calendar hierarchy ticked, Power View builds this view for me:
Power View automatically (and instantly because this is PowerPivot data) redraws the chart at the next level down. You can keep going to until you reach the bottom level of the hierarchy.
To drill up, hover your mouse pointer over the chart and three tiny icons appear in the top left corner. The first of these is the drill up command. It only needs a single click.
Drill down also works with other types of object. With a matrix, a double click on a row or column header goes down one level. A drill up is done by single-clicking the up arrow icon on the left hand side of the first header cell.
All this functionality works with Power View in SharePoint 2013 as well as in Excel on the desktop.
* OK, you don’t really need to define a hierarchy in your data model because Power View will treat any attributes placed on the axis as a hierarchy. But it’s good practice to create hierarchies in your data source for consistency.