Feature Overview

The Pivot Table Drill-Down feature enables deep data exploration and root-cause analysis. By configuring drill-down conditions, you can link the drill-down logic to the pivot table’s SQL query, row groupings, column slices, and filters, and then progressively navigate from aggregated figures to underlying detail records. This improves both analysis accuracy and efficiency.

• Deep analysis from summary to detail: Drill down from a pivot cell to the underlying detailed dataset with inherited conditions.
• Condition inheritance and consistency: Drill-down results automatically reflect the pivot’s grouping/slicing/filtering logic, reducing manual rework.

Use Cases

• Cross-dimension investigation: When an aggregated value looks abnormal, drill down to view the underlying records and pinpoint the source (e.g., unusual task priority distribution within a specific iteration).
• Interactive exploration: With row groupings and column slices configured, clicking a pivot cell triggers drill-down to retrieve detailed records that match the selected context (e.g., all low-priority tasks within a specific iteration).
• Complex condition linkage: When multiple filters and grouping fields are applied (e.g., filtering by iteration and status), drill-down inherits these conditions to keep results logically consistent and avoid repetitive manual filtering.

Pivot tables support drill-down to help users perform deep analysis and identify the specific causes behind issues. The following explains how to configure drill-down for column data in a pivot table.

1. Factors That Affect Drill-Down

Before configuring drill-down, it is important to understand what factors influence drill-down results.

In addition to the base SQL query, a pivot table can be designed with Row Groupings, Column Slices, and Filters. All three will affect the data shown in each cell.

Therefore, when configuring drill-down, you must consider both the SQL query logic and the impact introduced by Row Groupings, Column Slices, and Filters, as follows:

1.1 In the original SQL query, any WHERE conditions, table joins, or other logic related to the target column fields must also be replicated in the drill-down SQL.

1.2 If Row Groupings are configured, the drill-down conditions must include the row grouping fields.

1.3 If Column Slices are configured, the drill-down conditions must include the slice fields.

1.4 If Filters are configured, the drill-down conditions must include the filter fields.

1.5 If a filter field duplicates a row grouping or slice field (e.g., both use “Iteration ID”), configure that duplicated field only once in the drill-down conditions.

图1

2. Drill-Down Example Walkthrough

Using the pivot table from the first section of this article as an example, the query SQL is:

select t1.execution executionid, t2.id execution, t2.status, t1.id,t1.pri
from zt_task t1 
left join zt_project t2 on t1.execution=t2.id
where t1.deleted='0' and t2.type='sprint'
and if($execution='' and $status='', 1=2, if($execution='', 1=1, t2.id =$execution) and if($status='', 1=1, t2.status=$status)) 

The purpose of this SQL is to join tasks with iterations and retrieve:

• Iteration ID
• Iteration name
• Iteration status
• Task ID under the iteration
• Task priority

Two query filters are configured:

• Iteration filter (using the iteration ID field)
• Iteration status filter (using the iteration status field)

Two levels of row groupings are configured:

• Iteration ID
• Iteration name

Task IDs are counted, and the task priority field is used as the slice field.

图2

After the pivot table design is completed, the next step is to configure drill-down.

图3

After saving, click Save Settings in the drill-down step to view the drill-down effect in the pivot table.

图4