Grouping in ASP.NET CORE Pivot Table Control

21 Dec 202220 minutes to read

NOTE

This feature is applicable only for relational data source.

Grouping is the most-useful feature in pivot table and the component automatically groups date, time, number and string. For example, the date type can be formatted and displayed based on year, quarter, month, and more. Likewise, the number type can be grouped range-wise, such as 1-5, 6-10, etc. These group fields will act as individual fields and allows users to drag them between different axes such as columns, rows, values, and filters and create pivot table at runtime.

The grouping can be enabled by setting the allowGrouping property in ejs-pivotview tag to true. To perform the grouping action via UI, right click on the pivot table’s row or column header, select “Group”, a dialog will appear in which fill the appropriate options to group the data. To ungroup,right click on the pivot table’s row or column header, select “Ungroup”.

The following are the three different types of grouping:

  • Number Grouping
  • Date Grouping
  • Custom Grouping

NOTE

Similar to Excel, only one type of grouping can be applied for a field.

<ejs-pivotview id="PivotView" width="100%" height="350" showGroupingBar="true" allowGrouping="true">
    <e-datasourcesettings dataSource="@ViewBag.DataSource" expandAll="false" enableSorting="true">
        <e-formatsettings>
            <e-field name="Amount" format="C"></e-field>
            <e-field name="Product_ID" format="N0"></e-field>
            <e-field name="Date" type="date" format="dd/MM/yyyy-hh:mm a"></e-field>
        </e-formatsettings>
        <e-rows>
            <e-field name="Date"></e-field>
        </e-rows>
        <e-columns>
            <e-field name="Product_ID" caption="Product ID"></e-field>
        </e-columns>
        <e-values>
            <e-field name="Sold" caption="Unit Sold"></e-field>
            <e-field name="Amount" caption="Sold Amount"></e-field>
        </e-values>
    </e-datasourcesettings>
</ejs-pivotview>
public ActionResult Index()
{
    var data = GetGroupData();
    ViewBag.DataSource = data;
    return View();
}

Number Grouping

Number grouping allows users to organize data, which is in number format into different ranges, such as 1-5, 6-10, etc. Number grouping can be configured via UI, by right-clicking on the number based header in the pivot table.

<ejs-pivotview id="PivotView" width="100%" height="350" showGroupingBar="true" allowGrouping="true">
    <e-datasourcesettings dataSource="@ViewBag.DataSource" expandAll="false" enableSorting="true">
        <e-formatsettings>
            <e-field name="Amount" format="C"></e-field>
            <e-field name="Product_ID" format="N0"></e-field>
        </e-formatsettings>
        <e-rows>
            <e-field name="Product_ID" caption="Product ID"></e-field>
        </e-rows>
        <e-columns>
            <e-field name="Products"></e-field>
        </e-columns>
        <e-values>
            <e-field name="Sold" caption="Unit Sold"></e-field>
            <e-field name="Amount" caption="Sold Amount"></e-field>
        </e-values>
    </e-datasourcesettings>
</ejs-pivotview>
public ActionResult Index()
{
    var data = GetGroupData();
    ViewBag.DataSource = data;
    return View();
}

Context-menu options for number grouping

Range selection

The “Starting at” and “Ending at” options are used to set the number range depending on which the headers will be grouped. For example, if the “Product_ID” field holds the number from “1001” to “1010” and the user chooses to group the number range by setting “1004” to “Starting at” and “1008” to “Ending at” options on their own. Then the specified number range will be used for number grouping and the rest will be grouped as “Out of Range”.

Range options applied for number grouping

Range interval

The “Interval by” option is used to separate the selected number data type field into range-wise such as 1-5, 6-10, etc.

For example, if the user wants to display the “Product_ID” data field with a group interval of “2” by setting the “Interval by” option on their own. The “Product_ID” field will then be grouped by the specified range of intervals, such as “1004-1005”, “1006-1007“,etc.

Grouping settings options applied for number grouping


Applied grouping settings updated in pivot table for number grouping

Number grouping can also be configured using the e-groupsettings tag through code-behind. The properties required are:

  • name: Allows user to set the field name.
  • rangeInterval: Allows user to set the interval between two numbers.
  • startingAt: Allows user to set the starting number.
  • endingAt: Allows user to set the ending number.
  • type: Allows user to set the group type. For number grouping, Number is set.

NOTE

If starting and ending numbers specified in startingAt and endingAt properties are in-between the number range, then rest of the numbers will be grouped and placed in “Out of Range” section introduced specific to this feature.

<ejs-pivotview id="PivotView" width="100%" height="350" showGroupingBar="true" allowGrouping="true">
    <e-datasourcesettings dataSource="@ViewBag.DataSource" expandAll="false" enableSorting="true">
        <e-formatsettings>
            <e-field name="Amount" format="C"></e-field>
            <e-field name="Product_ID" format="N0"></e-field>
        </e-formatsettings>
        <e-rows>
            <e-field name="Product_ID" caption="Product ID"></e-field>
        </e-rows>
        <e-columns>
            <e-field name="Products"></e-field>
        </e-columns>
        <e-values>
            <e-field name="Sold" caption="Unit Sold"></e-field>
            <e-field name="Amount" caption="Sold Amount"></e-field>
        </e-values>
        <e-groupsettings>
            <e-field name="Product_ID" type="Number" rangeInterval=2 startingAt=1004, endingAt=1008></e-field>
        </e-groupsettings>
    </e-datasourcesettings>
</ejs-pivotview>
public ActionResult Index()
{
    var data = GetGroupData();
    ViewBag.DataSource = data;
    return View();
}

Applied grouping settings updated in pivot table for number grouping

Ungrouping the existing number groups

By right-clicking the appropriate header and selecting “Ungroup” from the context menu in the pivot table component, users can ungroup the applied number grouping.

output

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 via UI, by right-clicking on the date and time based header in the pivot table.

<ejs-pivotview id="PivotView" width="100%" height="350" showGroupingBar="true" allowGrouping="true">
    <e-datasourcesettings dataSource="@ViewBag.DataSource" expandAll="false" enableSorting="true">
        <e-formatsettings>
            <e-field name="Amount" format="C"></e-field>
            <e-field name="Date" type="date" format="dd/MM/yyyy-hh:mm a"></e-field>
        </e-formatsettings>
        <e-rows>
            <e-field name="Date"></e-field>
        </e-rows>
        <e-columns>
            <e-field name="Product_Categories" caption="Product Categories"></e-field>
        </e-columns>
        <e-values>
            <e-field name="Sold" caption="Unit Sold"></e-field>
            <e-field name="Amount" caption="Sold Amount"></e-field>
        </e-values>
    </e-datasourcesettings>
</ejs-pivotview>
public ActionResult Index()
{
    var data = GetGroupData();
    ViewBag.DataSource = data;
    return View();
}

Context-menu options for date grouping

Range selection

The “Starting at” and “Ending at” options are used to set the date range depending on which the headers will be grouped. For example, if the “Date” field holds the date from “01/01/2015” to “02/12/2018” and the user chooses to group the date range by setting “01/07/2015” to “Starting at” and “31/07/2017” to “Ending at” options on their own. Then the specified date range will be used for date grouping and the rest will be considered as “Out of Range”.

Range options applied for date grouping

Group interval

The “Interval by” option is used to separate the selected date fields into years, quarters, months, days, hours, minutes and seconds. For example, if the user wants to display the “Date” field with group intervals as “Years” and “Months” by selecting the “Interval by” option on their own. The “Date” field will then be separated by the specified group intervals and created as two new fields, namely “Years (Date)” which holds the date years and “Months (Date)” which holds the date months. Such fields can be used for report manipulations in the pivot table at runtime.

NOTE

When none of the Interval by options are chosen, the OK button in the dialog will be disabled, meaning that at least one interval option should be selected in order to apply the date grouping.

Group interval option applied for date grouping


Grouping settings options applied for date grouping


Applied grouping settings updated in pivot table for date grouping

Date grouping can also be configured using the e-groupsettings tag through code-behind. The properties required are:

  • name: Allows user to set the field name.
  • type: Allows user to set the group type. For date grouping, Date is set.
  • startingAt: Allows user to set starting date.
  • endingAt: Allows user to set ending date.
  • groupInterval: Allows user to set interval in year, quarter, month, day, hour, minute, or second pattern.

NOTE

From the date format “YYYY-DD-MM HH:MM:SS”, if user wants to display only year and month, then the groupInterval property should be set with Years and Months alone. Also, user can shuffle the order of year, quarter, month, day, hour, minute, or second based on their requirement and display the same in the pivot table.

<ejs-pivotview id="PivotView" width="100%" height="350" showGroupingBar="true" allowGrouping="true">
    <e-datasourcesettings dataSource="@ViewBag.DataSource" expandAll="false" enableSorting="true">
        <e-formatsettings>
            <e-field name="Amount" format="C"></e-field>
            <e-field name="Date" type="date" format="dd/MM/yyyy-hh:mm a"></e-field>
        </e-formatsettings>
        <e-rows>
            <e-field name="Date"></e-field>
        </e-rows>
        <e-columns>
            <e-field name="Product_Categories" caption="Product Categories"></e-field>
        </e-columns>
        <e-values>
            <e-field name="Sold" caption="Unit Sold"></e-field>
            <e-field name="Amount" caption="Sold Amount"></e-field>
        </e-values>
        <e-groupsettings>
            <e-field name="Date" type="Date" groupInterval="@(new string[]{ "Years", "Months"})" startingAt="2015-07-01" endingAt="2017-07-31"></e-field>
        </e-groupsettings>
    </e-datasourcesettings>
</ejs-pivotview>
public ActionResult Index()
{
    var data = GetGroupData();
    ViewBag.DataSource = data;
    return View();
}

Furthermore, in the field list UI, these date group fields Years (Date), Quarters (Date), Months (Date), etc… will be automatically grouped and displayed under the Date folder name.

Date fields grouped and displayed under the Date folder

Ungrouping the existing date groups

By right-clicking the appropriate header and selecting “Ungroup” from the context menu in the pivot table component, users can ungroup the applied date grouping.

output

Custom Grouping

Custom grouping can group any data type, such as date, time, number and string, into a custom field based on the user’s needs. It can be configured via the UI by right-clicking on any header in the pivot table.

<ejs-pivotview id="PivotView" width="100%" height="350" showGroupingBar="true" allowGrouping="true">
    <e-datasourcesettings dataSource="@ViewBag.DataSource" expandAll="false" enableSorting="true">
        <e-formatsettings>
            <e-field name="Amount" format="C"></e-field>
            <e-field name="Product_ID" format="N0"></e-field>
            <e-field name="Date" type="date" format="dd/MM/yyyy-hh:mm a"></e-field>
        </e-formatsettings>
        <e-rows>
            <e-field name="Date"></e-field>
        </e-rows>
        <e-columns>
            <e-field name="Product_ID" caption="Product ID"></e-field>
        </e-columns>
        <e-values>
            <e-field name="Sold" caption="Unit Sold"></e-field>
            <e-field name="Amount" caption="Sold Amount"></e-field>
        </e-values>
    </e-datasourcesettings>
</ejs-pivotview>
public ActionResult Index()
{
    var data = GetGroupData();
    ViewBag.DataSource = data;
    return View();
}

In order to create custom grouping in the pivot table, a minimum of two headers belonging to a specific field should be chosen. To select more than one header, press and hold the CTRL key or hold the SHIFT key and click the appropriate row or column headers. Once selection is done, right-click and select “Group”.

Context-menu options for custom grouping

In the dialog, the “Field caption” is the alias name of the new custom field that will be used to shown up in the pivot table component.

Caption applied for custom grouping

The “Group Name” option helps to set the name of the header to hold the other selected headers. For example, if the user wants to group headers such as “Gloves”, “Jerseys” and “Shorts” in the “Products” field by setting the top level name as “Clothings” to “Group Name” on their own. The selected headers are then grouped under the name “Clothings” in the pivot table.

Grouping settings applied for custom grouping


Applied grouping settings updated in pivot table for custom grouping

User can also apply new custom grouping options to an existing custom field by right-clicking on the custom group header in the pivot table. For example, if the user wants to create a new custom group for the current custom group headers such as “Bottles and Cages”, ”Cleaners” and “Fenders” by setting the top level name as “Accessories” to “Group Name” on their own. The selected headers will then be grouped in the pivot table under the name “Accessories” with a new custom field called “Product category 1”.

Context-menu options for nested custom grouping


Grouping settings applied for nested custom grouping


Applied grouping settings updated in pivot table for custom grouping

Custom grouping can also be configured using the e-groupsettings tag through code-behind. The properties required are:

  • name: Allows user to set the field name.
  • caption: Allows user to set the caption name for custom grouping field.

  • type: Allows user to set the group type. For custom grouping, Custom is set.

  • Custom groups can be added using e-customGroups tag in the e-groupsettings tag through code-behind. The properties required are:

  • groupName: Allows user to set the group name (or title) for selected headers.
  • items: It allows to set the headers which needs to be grouped from display.

NOTE

When the groupName with the headers listed in items in the e-customGroups tag is grouped by the defined groupName and the rest is grouped by its own name in the pivot table.

<ejs-pivotview id="PivotView" width="100%" height="350" showGroupingBar="true" allowGrouping="true">
    <e-datasourcesettings dataSource="@ViewBag.DataSource" expandAll="false" enableSorting="true">
        <e-formatsettings>
            <e-field name="Amount" format="C"></e-field>
            <e-field name="Product_ID" format="N0"></e-field>
        </e-formatsettings>
        <e-rows>
            <e-field name="Products"></e-field>
        </e-rows>
        <e-columns>
            <e-field name="Product_ID" caption="Product ID"></e-field>
        </e-columns>
        <e-values>
            <e-field name="Sold" caption="Unit Sold"></e-field>
            <e-field name="Amount" caption="Sold Amount"></e-field>
        </e-values>
        <e-groupsettings>
            <e-field name="Products" type="Custom" caption="Product catergory">
                <e-customgroups>
                    <e-group groupName="Clothings" items="@(new string[] { "Gloves", "Jerseys", "Shorts" })"></e-group>
                </e-customgroups>
            </e-field>
        </e-groupsettings>
    </e-datasourcesettings>
</ejs-pivotview>
public ActionResult Index()
{
    var data = GetGroupData();
    ViewBag.DataSource = data;
    return View();
}

Applied grouping settings updated in pivot table for custom grouping

Ungrouping the existing custom groups

By right-clicking the appropriate header and selecting “Ungroup” from the context menu in the pivot table component, users can ungroup the applied custom grouping.

NOTE

When a specific field is removed from the report after ungrouping, its custom group fields will also be removed from the pivot table.

output