Dashboards

PivotGrid

Updated February 28, 2026

The PivotGrid component enables cross-tabular pivot analysis on dashboard data. Users can drag and drop fields between Row, Column, Data, and Filter areas at runtime to reshape the report layout. Data can be summarized with calculated fields (both text and decimal), conditionally formatted, drilled down to the underlying adapter results, and exported to PDF, XLS, or XLSX.

All data adapter types are supported. The most common pattern uses a CubeViewMD adapter, which flattens cube dimensions into a multi-dimensional fact table ideal for pivoting.

PivotGrid Properties

Property	Description
RowFields	Comma-separated list of column names from the results table to place in the Row area by default
ColumnFields	Comma-separated list of column names from the results table to place in the Column area by default
DataFields	Comma-separated list of measure column names to assign as default data values. Multiple measures are separated by commas.
FieldGroups	Comma-separated list of column names to group together as a default
SaveState	When True, enables a Save button on the PivotGrid so users can persist their layout preferences
ShowToggleSizeButton	When True, shows a toggle button to resize the component at runtime

Field Areas

The PivotGrid organizes data into four areas. The initial assignment is defined by the RowFields, ColumnFields, and DataFields properties, but users can rearrange fields at runtime by dragging them between areas.

diagramPivotGrid Field Areas

Loading diagram...

Data Adapter Integration

The PivotGrid works with all adapter types. The adapter determines what columns are available for the Row, Column, Data, and Filter areas.

Adapter Type	Use Case
CubeViewMD	Most common. Flattens cube dimensions into columns (Entity, Account, Time, etc.) with Amount as the measure. Ideal for multi-dimensional financial analysis.
SQL	Custom relational data pivoted by any columns. Useful for non-cube data like planning inputs or operational metrics.
Custom	Business Rule-generated DataTable pivoted by computed or derived columns. Maximum flexibility.
CubeView	Standard CubeView results. Less commonly used with PivotGrid since CubeViewMD provides better dimension separation.

CubeViewMD Columns

When using a CubeViewMD adapter, the following dimension columns are available for field assignment:

Entity, Consolidation, Scenario, Time, View, Account, Flow, Origin, IC, UD1, UD2, UD3, UD4, UD5, UD6, UD7, UD8

The measure column is typically Amount. Configure the PivotGrid with dimension columns in RowFields/ColumnFields and Amount in DataFields to create a financial pivot report.

CubeViewMD Example

A financial analysis PivotGrid showing entity-level results over time.

CubeViewMD Adapter — FinancialAnalysisAdapter:

References a CubeView that returns multi-dimensional data with Entity, Account, Time, and Amount columns

PivotGrid Properties:

RowFields: Entity
ColumnFields: Time
DataFields: Amount

diagramCubeViewMD to PivotGrid Pipeline

Loading diagram...

The initial layout shows Entity on rows and Time on columns with Amount summed. At runtime, the user can drag the Account field from the available fields list into the Column area to add an Account breakdown, or move Entity to the Filter area to focus on a single entity.

Calculated Fields

Calculated fields can be added at design time or by users at runtime. Two types are available:

Text calculated fields — Derive new text values from existing columns
Decimal calculated fields — Perform numeric calculations on measure values

Calculated fields are displayed alongside source data in the pivot and participate in the same grouping and filtering as other fields.

Conditional Formatting

Rule-based formatting can be applied to cells based on their data values. This is configured through the PivotGrid's conditional formatting settings and supports highlighting cells that meet specific criteria (e.g., negative amounts in red, values above a threshold in green).

Drill-Down

Double-clicking a cell in the PivotGrid opens the underlying data adapter results table, showing the individual rows that were aggregated into that cell value. This lets users investigate the detail behind any summary number without leaving the dashboard.

Export

The PivotGrid output can be exported to:

PDF — Formatted document for printing or sharing
XLS — Excel 97-2003 format
XLSX — Excel Open XML format

LargeDataPivotGrid

The LargeDataPivotGrid is a variant designed for large datasets and external database tables. Unlike the standard PivotGrid, it performs pivot processing on the server rather than the client, and supports paging to manage large result sets.

Key Differences

Feature	PivotGrid	LargeDataPivotGrid
Processing	Client-side	Server-side
Data Source	Any data adapter	Direct table connection only
Table Types	N/A (uses adapters)	Application table, BI Blend table, or external database table
Aggregation	Multiple types per measure	Single type per measure (Sum, Min, or Max)
Calculated Fields	Yes	No
Paging	No	Yes (up to 3,000 rows per page)
Drag-and-Drop	Yes	Yes
Export	PDF, XLS, XLSX	PDF, XLS, XLSX

LargeDataPivotGrid Properties

Property	Description
Table Connection	Choose between Application table, BI Blend database table, or external database table
RowFields	Comma-separated column names for the Row area
ColumnFields	Comma-separated column names for the Column area
DataFields	Comma-separated measure column names
FilterFields	Comma-separated column names for the Filter area
WhereClause	SQL WHERE clause to filter the data before pivoting
DataAggregation	Aggregation to apply to data fields
ExcludedFields	Columns to hide from the user (they exist in the table but are not available for pivoting)
PagingSize	Number of rows per page (maximum 3,000). Higher values show more data but may impact performance.
SaveState	When True, users can save their layout preferences

When to Use LargeDataPivotGrid

Use LargeDataPivotGrid instead of PivotGrid when:

The source table contains thousands or more rows
The data comes from an external database table
Server-side processing is needed for performance
You are reporting on BI Blend data stored in a database table

⚠️Warning

PivotGrid and LargeDataPivotGrid layouts are saved per dashboard location, not per component. When copying a dashboard that contains a PivotGrid or LargeDataPivotGrid, the copy gets its own separate saved layout. Changes to the layout in one copy do not affect the other.

Data Adapters — CubeViewMD adapter for multi-dimensional fact tables used with PivotGrid
CubeView and BI Viewer Components — BI Viewer internal pivot tables vs standalone PivotGrid
GridView — Flat tabular display alternative for non-pivot read-only data
Relational Tables — Custom Application tables as LargeDataPivotGrid data sources