Drill Down

Introduction

Drill down is the process of going from a higher level of grouping to a lower and more detailed level of grouping. This process can repeat by "drilling down" to the next level of details. Drill up is the opposite process of going back to a higher level of grouping.

Drill down is supported in Pivot and Chart views.

Drill Down Example

Using the Payments table of the Demo data source, we created a chart that shows the number of payments by Product Category. As you can see in the picture, the majority of payments were for the Home category. We can drill down to the Sub Category by clicking on the pie slice and using the drill down button.

After we drill down we see a new view, the drill view, that shows the next level of details:

The action we performed is called "focus and drill down". It combines two actions:

• Focus - we focused on the Home category by adding it to the filter (showing only data for the Home category)
• Drill Down - we now break down the number of payments by Sub Category.

Drill Down in Pivot

In a Pivot View, you can drill down using the context menu, by clicking on a category and selecting a drill-down option, if available.

In our example, selecting "Drill down to Sub Category" will add the Sub Category to our view as the second column to show more detail.

We could also select "Focus on Home and drill down to Sub Category". That option will act similar to what we saw in the chart example above. It will focus on the Home category by adding a filter to only show data for "Home". It will then replace the Category column with Sub Category. Here's the result of focus and drill down:

Defining the Drill-Down Hierarchy

A drill-down hierarchy can be defined in one of two ways:

• table - you can define a drill-down hierarchy for a table. Once the hierarchy is defined for the table, it will enable drill-down whenever a field from this hierarchy is used as a category in a Pivot or Chart.
• view - you can define drill-down hierarchies for a pivot of chart field. This definition only effects this view and provides a more flexible way of managing drill-down for a view.

Table Drill-Down Hierarchy

The drill-down hierarchy is the list of fields that mark the drill-down path. In our example above, the product "Category" field is at the top of the hierarchy. The next field is "Sub Category". The hierarchy can have more than two fields, for example, a third field in this example is the product "Name" field.

The drill-down hierarchy is defined in the table that has the drill-down fields. All the fields in the drill-down hierarchy must be of the same table. In our example, even though our view was on payments, the drill-down fields came from the Product table and therefore the drill-down hierarchy is defined on the Product table.

You can define a drill-down hierarchy for any of the tables in any of your data sources except the Demo data source. You start by selecting a data source (Data -> Browse data sources and tables) and select the table that has the drill-down fields. Then click the "Hierarchies" button.

Click "Add" to add a new hierarchy. Give your hierarchy a name, and select fields. To select more than two fields, use the "Add Level" button.

Multiple Drill-Down Hierarchies

You can define more than one drill-down hierarchy, however a field can only be part of one such hierarchy.

View Drill-Down Hierarchy

A more flexible way of defining a drill-down hierarchy is to define it for a view field. This has the following advantages:

• the hierarchy is tailored to the needs of this particular view
• the hierarchy can include fields from multiple tables
• you can define multiple drill-down hierarchies to provide multiple drill-down paths

To define drill-down hierarchies, use the field settings dialog in a Pivot or Chart field. If drill-down is available for that field, you'll see a Drill Down button.

Clicking the Drill Down button brings up a dialog similar to the Hierarchies dialog we saw for tables.

You add or edit a hierarchy using the Add or Edit buttons

Date/Time Field and Drill-Down Hierarchies

A Date/Time field automatically defines the following drill-down hierarchy:

Year -> Quarter -> Month -> Week (Monday) -> Day

It means that this hierarchy is always defined. You don't need to define it.

In some data sources, the weekly grouping is not available.

Mashup View Drill Down

A mashup view field can have drill-down under specific conditions:

• A View Drill-Down hierarchy, as described above, must be defined for each of the mashup input views. The Drill-Down Hierarchy must have the same name in each of the input views and the fields in the drill-down hierarchy must have the same label.

• A Date/Time field provides automatic drill down as described above

⇦ SQL Expression Field

⇧ Table of Contents

⇨ Binning