UI Pivot Tables

UI Pivot Tables are designed for displaying information that is aggregated in two or more dimensions, like a rollup across both rows and columns. Pivot tables transform your data to show relationships between different dimensions, making complex data easier to analyze and interpret.

Pivot tables are particularly useful when:

• You need to analyze data across multiple dimensions simultaneously.
• You want to summarize large datasets into manageable insights.
• You need to perform comparative analysis between different categories.
• You want to create cross-tabulations for reporting purposes.

To create a Pivot Table, you must specify three core components:

• Row by-columns: Defines the grouping for rows (vertical organization).
• Column by-columns: Defines the grouping for columns (horizontal organization).
• Aggregations: Defines the calculations performed on data points.

Create pivot tables

To create a Pivot Table, use these API methods:

• Python: make_pivot.
• Java/Groovy: PivotTable.make.

Example: Log message analysis

In this example, we retrieve the ProcessEventLog table and create a Pivot showing the number of log messages for each Process. The rows are aggregated by Date, EffectiveUser, and ProcessInfoId. The columns are aggregated by log levels (e.g., ERROR or INFO).

import io.deephaven.api.ColumnName
import io.deephaven.pivot.PivotTable
import static io.deephaven.api.agg.Aggregation.AggCount

import io.deephaven.enterprise.acl.EdgeAclProvider
import io.deephaven.enterprise.acl.AclFilterGenerator


processEventLog=db.liveTable("DbInternal", "ProcessEventLog").where("Date >= io.deephaven.time.calendar.Calendars.calendar().minusDays(today(), 5)")
pivot=PivotTable.make(processEventLog,
        [AggCount("Count")],
        [ColumnName.of("Date"), ColumnName.of("EffectiveUser"), ColumnName.of("ProcessInfoId")],
        [ColumnName.of("Level")])

from deephaven_enterprise.pivot.pivot_table import (
    PivotTable,
    make_pivot,
)
from deephaven.agg import count_
from deephaven_enterprise.edge_acl import EdgeAclProvider
import deephaven_enterprise.acl_generator as acl_generator

process_event_log = db.live_table("DbInternal", "ProcessEventLog").where(
    "Date >= io.deephaven.time.calendar.Calendars.calendar().minusDays(today(), 5)"
)
pivot = make_pivot(
    process_event_log,
    [count_("Count")],
    ["Date", "EffectiveUser", "ProcessInfoId"],
    ["Level"],
)

Data visualization

The following table is one possible visual representation of this pivot. You can see there are a total of 33,012 log messages, of which 19,638 are INFO messages. On 2025-06-30 there were 13,619 messages, of which 5,946 were for Bob's processes and 7,673 for Alice's processes. We can see that Bob's process with the ID 658337fc-4eb4-4d3f-aef6-3c39066a59fe had 2,491 messages, of which 2,331 were INFO; 1 WARN; 158 STDOUT; and 1 STDERR.

Filters and sorts

A Pivot Table can be sorted along either the rows or columns dimension using either the by-columns or the total aggregations for that dimension. Pivot sorts work identically to rollup sorts in each dimension—each node is sorted independently, maintaining the pivot's hierarchical structure.

A Pivot Table may be filtered by row or column keys, but cannot be filtered by aggregated values. After filtering, aggregations are recomputed so that totals only include the values accepted by the filter.

If you want to filter by input columns other than the keys, you must filter the source table and re-apply the pivot.

import io.deephaven.api.SortColumn
import io.deephaven.api.ColumnName
import io.deephaven.engine.table.impl.select.WhereFilterFactory

// Sort the rows by keys
by_key = pivot.apply([SortColumn.asc(ColumnName.of("Date")), SortColumn.asc(ColumnName.of("EffectiveUser")), SortColumn.asc(ColumnName.of("ProcessInfoId"))], [], [])

// Sort the rows and columns by count, displaying the most common values on top and to the left
by_count = pivot.apply([SortColumn.desc(ColumnName.of("Count"))], [SortColumn.desc(ColumnName.of("Count"))], [])

// Only include "ERROR" and "STDERR"
some_levels = pivot.apply([], [], [WhereFilterFactory.getExpression("Level.contains(`ERR`)")])

from deephaven.table import SortDirection

# Sort the rows by keys
by_key = pivot.sort_rows(["Date", "EffectiveUser", "ProcessInfoId"])

# Sort the rows and columns by count, displaying the most common values on top and to the left
by_count = pivot.sort_rows("Count", order=SortDirection.DESCENDING).sort_cols(
    "Count", order=SortDirection.DESCENDING
)

# Only include "ERROR" and "STDERR"
some_levels = pivot_table.filter("Level.contains(`ERR`)")

Security considerations

Access Control Lists

When an ACL (Access Control List) is applied to a pivot table, it is applied when a user fetches the pivot table from the server. The ACL is applied to the source of the pivot, thus allowing the user to see the aggregated values across the permitted rows.

Example: User-specific data filtering

Extending the previous example, we add a group filter to the EffectiveUser column. This filter permits users to view only rows that have an EffectiveUser value that matches one of the groups they are a member of. Because each user is a member of a group that matches their user name, and the EffectiveUser column contains the username that a worker is running as, the effect is to display a pivot that includes only that user's processes.

def ACL = EdgeAclProvider.builder()
        .rowAcl("allusers", AclFilterGenerator.group("EffectiveUser"))
        .build()

pivot = ACL.applyTo(pivot)

ACL = (
    EdgeAclProvider.builder()
    .row_acl("allusers", acl_generator.group("EffectiveUser"))
    .build()
)

pivot = ACL.apply_to(pivot)

Related documentation

• Rollup tables.
• Access Control Lists.
• Javadoc.
• Pydoc.