The features and capabilities of Power View have definitely improved since its release. One of my favorite features is the new drill-through ability in either a Matrix or Chart. In this post I will walk you through building a drill-through Matrix. If you prefer to watch a video instead of reading the blog here you go:

Open Power View

To get started you must first open Power View. As most of you know Power View can be opened in a couple of ways:

I will be using SharePoint 2013 for the purposes of this demonstration.

Add Fields

Once Power View is open select the fields that you want to display in the report. When you are adding the fields you should add them in the order that you expect to drill. For example, if want to see data at the Country, State and City levels, the fields should be added in that order. Once those fields are added, then you should add the additive or measureable value(s) from the Measure Group or Fact Table. These additions by default create a table on the Power View design surface.

As you can see there are several repeatable values and the only way to view the total is to scroll to the bottom of table using the scroll bar.

Change to Matrix

Next you will need to convert the Table to a Matrix. To do so, ensure that the Design tab is selected in the Ribbon, then select Matrix in the Visualization section.

At this point you will see the removal of the repeatable values and the addition of subtotals at each level.

Add Drill-through

To add the drill capability locate the icon in the ribbon labeled Show Levels

. Click the drop down arrow located to the right and select Enable drill down on rows from the menu.

The rows will immediately aggregate up to the highest level or to the level of the field that appears first in the Rows box located in the field list.

In the case of this example the data will roll up to Country.

Double-click Row or Column

Finally, to interact (drill-through) simply double-click a Country in the list and now a list of States will be displayed. If you double-click again a list of Cities will be displayed. To drill-up, click the up arrow that will appears.

All done. It’s just that simple. The same capability is available with charts also. To test it out change the matrix to a chart and double click one of the items displayed on the chart.

Talk to you soon,

Patrick LeBlanc, founder www.sqllunch.com