Search results

Grouping

Grouping data is the most-useful features in pivot table. The pivot table can automatically group dates, times, and numbers, so the date type can be formatted and displayed based on year, quarter, month, and more. The number type can be grouped in range-wise, such as 1-5, 6-10, etc. These group fields will act as other fields and allows users to drag fields between different axes such as columns, rows, values, and filters and create pivot table at runtime.

Date grouping

Date grouping allows users to organize data, which is in date format into different sections such as years, quarters, months, days, hours, minutes, and seconds. Date grouping can be configured using the groupSettings option through code-behind. The settings required to filter at initial rendering are:

  • name: Allows you to set the field name.
  • groupInterval: Allows you to set group interval to the date field.
  • startingAt: Allows you to set the starting date to group date field.
  • endingAt: Allows you to set the end date to group date field.
  • type: Allows you to set the field type. For example, date-formatted fields should be in “Date” type.
Source
Preview
index.js
index.html
var pivotTableObj = new ej.pivotview.PivotView({
    dataSourceSettings: {
        dataSource: Group_Data,
        expandAll: false,
        enableSorting: true,
        formatSettings: [{ name: 'Amount', format: 'C0' }, { name: 'Sold', format: 'N0' },
        { name: 'Date', type: 'date', format: 'dd/MM/yyyy-hh:mm a' }],
        rows: [{ name: 'Date', caption: 'Date' }],
        columns: [{ name: 'Product_ID', caption: 'Product ID' },
        { name: 'Products', caption: 'Products' }],
        values: [{ name: 'Sold', caption: 'Unit Sold' },
        { name: 'Amount', caption: 'Sold Amount' }],
        filters: [],
        groupSettings: [{ name: 'Date', type: 'Date', groupInterval: ['Years', 'Months', 'Days'], startingAt: new Date(2015, 2, 7), endingAt: new Date(2018, 1, 12) }]
    },
    showGroupingBar: true,
    height: 350
});
pivotTableObj.appendTo('#PivotTable');
<!DOCTYPE html><html lang="en"><head>
    <title>EJ2 Pivot Grid</title>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <meta name="description" content="Typescript Pivot Grid Control">
    <meta name="author" content="Syncfusion">
    <link href="index.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/ej2-base/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/ej2-buttons/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/ej2-calendars/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/ej2-dropdowns/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/ej2-grids/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/ej2-charts/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/ej2-inputs/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/ej2-lists/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/ej2-navigations/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/ej2-popups/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/ej2-pivotview/styles/material.css" rel="stylesheet">
    
    
<script src="https://cdn.syncfusion.com/ej2/dist/ej2.min.js" type="text/javascript"></script>
<script src="es5-datasource.js" type="text/javascript"></script>
</head>

<body>
    
    <div id="container">
        <div>
            <div id="PivotTable"></div>
        </div>
    </div>


<script>
var ele = document.getElementById('container');
if(ele) {
    ele.style.visibility = "visible";
 }   
        </script>
<script src="index.js" type="text/javascript"></script>
</body></html>

Number grouping

Number grouping allows users to organize data, which is in number format. Number grouping can be configured using the groupSettings option through code-behind. The settings required to filter at initial rendering are:

  • name: Allows you to set the field name.
  • rangeInterval: Allows you to set group interval to the number field.
  • startingAt: Allows you to set the starting date to group number field.
  • endingAt: Allows you to set the end date to group number field.
  • type: Allows you to set the field type. For example, number formatted fields should be in “Number” type.
Source
Preview
index.js
index.html
var pivotTableObj = new ej.pivotview.PivotView({
    dataSourceSettings: {
        dataSource: Group_Data,
        expandAll: false,
        enableSorting: true,
        formatSettings: [{ name: 'Amount', format: 'C0' }, { name: 'Sold', format: 'N0' },
        { name: 'Date', type: 'date', format: 'dd/MM/yyyy-hh:mm a' }],
        rows: [{ name: 'Date', caption: 'Date' }],
        columns: [{ name: 'Product_ID', caption: 'Product ID' },
        { name: 'Products', caption: 'Products' }],
        values: [{ name: 'Sold', caption: 'Unit Sold' },
        { name: 'Amount', caption: 'Sold Amount' }],
        filters: [],
        groupSettings: [{ name: 'Date', type: 'Date', groupInterval: ['Years', 'Months', 'Days'], startingAt: new Date(2015, 2, 7), endingAt: new Date(2018, 1, 12) },
        { name: 'Product_ID', type: 'Number', rangeInterval: 2, startingAt: 1002, endingAt: 1008 }]
    },
    showGroupingBar: true,
    height: 350
});
pivotTableObj.appendTo('#PivotTable');
<!DOCTYPE html><html lang="en"><head>
    <title>EJ2 Pivot Grid</title>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <meta name="description" content="Typescript Pivot Grid Control">
    <meta name="author" content="Syncfusion">
    <link href="index.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/ej2-base/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/ej2-buttons/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/ej2-calendars/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/ej2-dropdowns/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/ej2-grids/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/ej2-charts/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/ej2-inputs/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/ej2-lists/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/ej2-navigations/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/ej2-popups/styles/material.css" rel="stylesheet">
    <link href="//cdn.syncfusion.com/ej2/ej2-pivotview/styles/material.css" rel="stylesheet">
    
    
<script src="https://cdn.syncfusion.com/ej2/dist/ej2.min.js" type="text/javascript"></script>
<script src="es5-datasource.js" type="text/javascript"></script>
</head>

<body>
    
    <div id="container">
        <div>
            <div id="PivotTable"></div>
        </div>
    </div>


<script>
var ele = document.getElementById('container');
if(ele) {
    ele.style.visibility = "visible";
 }   
        </script>
<script src="index.js" type="text/javascript"></script>
</body></html>