Kendo jQuery PivotGrid: Show hierarchy levels in Fields

[Moh]

I want to compare two data types side-by-side in one PivotGrid. Conceptually the Fields tree should look like:

Field

DATA 0
  ├─ Area Data 0
  └─ Period Data 0

DATA 1
  ├─ Area Data 1
  └─ Period Data 1

Measures
  • Value Data 0
  • Value Data 1

Then on Columns I’d place Area → Period under DATA 0 (and/or DATA 1) to get a hierarchical header.

Question

• Is showing expandable hierarchy levels in the Fields panel supported with client-side data?

• If yes, what’s the correct schema?

• If not, what’s the recommended approach (e.g., model each level as its own dimension), and would PivotGrid v2 change this?

Product & version

• Kendo UI for jQuery – PivotGrid (classic, not v2)

• Reproduced on CDN builds 2023.2.718 and 2024.2.514

• Browser: Chrome (latest)

[Nikolay]

Hi Moh,

Hierarchical Fields Panel Support

• Expandable hierarchy levels in the Fields panel are not supported with client-side (local) data in the classic Kendo UI for jQuery PivotGrid. The Fields panel will display all dimensions as flat, separate fields. Nested or expandable trees—such as grouping "Area Data 0" and "Period Data 0" under "DATA 0"—cannot be defined or visualized in the configurator when using local JSON data.
• When connecting to an OLAP cube via XMLA, you can work with actual hierarchies—dimensions, levels, hierarchies are discoverable through schema.hierarchies, schema.levels, schema.dimensions, etc., and the Field Chooser can display true hierarchical structures.

Schema and Recommended Approach

• Schema: Since hierarchical grouping is not supported locally, you should define each field (such as "Area Data 0", "Period Data 0", "Area Data 1", "Period Data 1") as separate dimensions in your data source. Your fields panel will show each as an individual, non-nested item.
• Recommended Approach:
  • Model each hierarchy level as its own dimension.
  • To compare two data types side-by-side, include all relevant fields in your data source and assign them as separate dimensions and measures.
  • You can arrange dimensions in the Columns or Rows area to create multi-level headers, but these will be flat, not expandable trees.

Example Configuration

dataSource: new kendo.data.PivotDataSource({
  data: [
    { dataType: "DATA 0", area: "Area Data 0", period: "Period Data 0", value: 100 },
    { dataType: "DATA 1", area: "Area Data 1", period: "Period Data 1", value: 200 }
    // ... more data
  ],
  schema: {
    cube: {
      dimensions: {
        "DataType": { caption: "Data Type", uniqueName: "DataType", fields: [{ name: "dataType" }] },
        "Area": { caption: "Area", uniqueName: "Area", fields: [{ name: "area" }] },
        "Period": { caption: "Period", uniqueName: "Period", fields: [{ name: "period" }] }
      },
      measures: {
        "Value": { field: "value", aggregate: "sum" }
      }
    }
  }
})

• This approach will show "Data Type", "Area", and "Period" as separate fields in the Fields panel.

PivotGrid v2 Status

• PivotGrid v2 does not introduce support for expandable hierarchical fields in the configuration panel with client-side data. Hierarchical expand/collapse in the Fields panel still requires a server-side (OLAP) data source.

Summary

If you're stuck with flat data, the most practical approach is to model each "DATA" element and sub-level as its own flat dimension, then rely on the user placing them in the proper order in Rows or Columns. Alternatively, you might consider moving toward an OLAP backend if hierarchical field selection is critical.

Regards,
Nikolay
Progress Telerik