Introducing Multi‑Dimensional (Pivot) Tables with VTable: Concepts, Configuration, and Business Scenarios

Multi‑dimensional tables, also known as pivot tables, allow rows and columns to contain one or more dimensions so that the relationships between dimensions can be visualized, helping analysts quickly compare metrics across various business scenarios.

The core BI concepts covered include:

Dimension : an attribute used to categorize data.

Dimension hierarchy : predefined levels of a dimension (e.g., year → month, region → province).

Dimension member : a specific value of a dimension (e.g., "2021", "Beijing").

Indicator : a metric such as sales, cost, or profit.

VTable implements these concepts through a concise configuration object. The minimal configuration looks like:

const option = {
  rows: ['region', 'province'], // row dimensions
  columns: ['year', 'quarter'], // column dimensions
  indicators: ['sales', 'profit'], // metrics
  records: [
    {
      region: '东北',
      province: '黑龙江',
      year: '2016',
      quarter: '2016-Q1',
      sales: 1243,
      profit: 546
    },
    ...
  ]
};

For advanced data‑analysis needs, VTable provides a dataConfig object where you can define aggregation, sorting, filtering, totals, and derived fields. Example snippets:

/**
 * Data processing configuration
 */
export interface IDataConfig {
  aggregationRules?: AggregationRules; // aggregation logic
  sortRules?: SortRules;               // sorting logic
  filterRules?: FilterRules;           // filtering logic
  totals?: Totals;                     // subtotals / grand totals
  derivedFieldRules?: DerivedFieldRules; // derived fields
  ...
}

Aggregation example (sum, count, average):

aggregationRules: [
  {
    indicatorKey: 'TotalSales',
    field: 'Sales',
    aggregationType: VTable.TYPES.AggregationType.SUM,
    formatFun: sumNumberFormat
  },
  {
    indicatorKey: 'OrderCount',
    field: 'Sales',
    aggregationType: VTable.TYPES.AggregationType.COUNT,
    formatFun: countNumberFormat
  },
  {
    indicatorKey: 'AverageOrderSales',
    field: 'Sales',
    aggregationType: VTable.TYPES.AggregationType.AVG,
    formatFun: sumNumberFormat
  }
];

Sorting example (by indicator in descending order):

sortRules: [
  {
    sortField: 'city',
    sortByIndicator: 'sales',
    sortType: VTable.TYPES.SortType.DESC,
    query: ['办公用品', '笔']
  } as VTable.TYPES.SortByIndicatorRule
];

Filtering example (exclude specific province or category):

filterRules: [
  {
    filterFunc: (record: Record
) => {
      return record.province !== '四川省' || record.category !== '家具';
    }
  }
];

Derived fields can be created from existing data, for instance extracting year and month from a date field:

derivedFieldRules: [
  {
    fieldName: 'Year',
    derivedFunc: VTable.DataStatistics.dateFormat('Order Date', '%y', true)
  },
  {
    fieldName: 'Month',
    derivedFunc: VTable.DataStatistics.dateFormat('Order Date', '%n', true)
  }
];

The data analysis process traverses the records once, builds a dimension tree, assigns each record to the appropriate row‑column path, and computes aggregated metric values for the body cells.

When default automatic dimension trees are insufficient, custom rowTree and columnTree configurations can be supplied. A full custom‑tree example:

const option = {
  rowTree: [{
    dimensionKey: 'region',
    value: '中南',
    children: [
      { dimensionKey: 'province', value: '广东' },
      { dimensionKey: 'province', value: '广西' }
    ]
  }, {
    dimensionKey: 'region',
    value: '华东',
    children: [
      { dimensionKey: 'province', value: '上海' },
      { dimensionKey: 'province', value: '山东' }
    ]
  }],
  columnTree: [{
    dimensionKey: 'year',
    value: '2016',
    children: [
      {
        dimensionKey: 'quarter',
        value: '2016-Q1',
        children: [
          { indicatorKey: 'sales', value: 'sales' },
          { indicatorKey: 'profit', value: 'profit' }
        ]
      },
      {
        dimensionKey: 'quarter',
        value: '2016-Q2',
        children: [
          { indicatorKey: 'sales', value: 'sales' },
          { indicatorKey: 'profit', value: 'profit' }
        ]
      }
    ]
  }],
  indicators: ['sales', 'profit'],
  corner: { titleOnDimension: 'none' },
  records: [
    { region: '中南', province: '广东', year: '2016', quarter: '2016-Q1', sales: 1243, profit: 546 },
    { region: '中南', province: '广东', year: '2016', quarter: '2016-Q2', sales: 2243, profit: 169 },
    { region: '中南', province: '广西', year: '2016', quarter: '2016-Q1', sales: 3043, profit: 1546 },
    { region: '中南', province: '广西', year: '2016', quarter: '2016-Q2', sales: 1463, profit: 609 },
    { region: '华东', province: '上海', year: '2016', quarter: '2016-Q1', sales: 4003, profit: 1045 },
    { region: '华东', province: '上海', year: '2016', quarter: '2016-Q2', sales: 5243, profit: 3169 },
    { region: '华东', province: '山东', year: '2016', quarter: '2016-Q1', sales: 4543, profit: 3456 },
    { region: '华东', province: '山东', year: '2016', quarter: '2016-Q2', sales: 6563, profit: 3409 }
  ]
};

Several common business‑scenario configurations are demonstrated, such as setting different cell types for indicators (e.g., chart , sparkline ), applying distinct font sizes to dimension headers, showing row dimension names in the corner, and integrating trend analysis.

VTable can seamlessly embed VChart components, allowing chart types to be rendered directly inside table cells, which greatly enhances visual expressiveness and addresses performance concerns for pages with many charts.

For further exploration, refer to the VTable documentation, example gallery, and the linked GitHub repository.