The Grouping Bar option in pivot table automatically populates fields from the bound data source and allows end users to drag fields between different axes such as columns, rows, values, and filters, and create pivot table at runtime. It can be enabled by setting the ShowGroupingBar
property in PivotView
class to true.
Similar to Field List, Grouping Bar UI also comes with basic interactions like,
The grouping bar provides some additional options to customize it’s UI using
GroupingBarSettings
property.
@Html.EJS().PivotView("PivotView").Width("100%").Height("300").DataSourceSettings(dataSource => dataSource.DataSource((IEnumerable<object>)ViewBag.DataSource).ExpandAll(false).EnableSorting(true).AllowLabelFilter(true).AllowValueFilter(true)
.FormatSettings(formatsettings =>
{
formatsettings.Name("Amount").Format("C0").MaximumSignificantDigits(10).MinimumSignificantDigits(1).UseGrouping(true).Add();
}).Rows(rows =>
{
rows.Name("Country").Add(); rows.Name("Products").Add();
}).Columns(columns =>
{
columns.Name("Year").Caption("Year").Add(); columns.Name("Quarter").Add();
}).Values(values =>
{
values.Name("Sold").Caption("Units Sold").Add(); values.Name("Amount").Caption("Sold Amount").Add();
})).ShowGroupingBar(true).Render()
public ActionResult Index()
{
var data = GetPivotData();
ViewBag.DataSource = data;
return View();
}
The Grouping Bar has an option to filter members of particular fields at runtime in pivot table. In-order to filter members in a field, click the filter icon and check/uncheck members that needs to be displayed. By default, filter icon besides each field is enabled in the grouping bar. To disable the filter icon, set the property ShowFilterIcon
in PivotViewGroupingBarSettings
class to false.
By default, the filter icon is enabled in the grouping bar.
@using Syncfusion.EJ2.PivotView
@Html.EJS().PivotView("PivotView").Width("100%").Height("300").GroupingBarSettings( new PivotViewGroupingBarSettings {
ShowFilterIcon =false }).DataSourceSettings(dataSource => dataSource.DataSource((IEnumerable<object>)ViewBag.DataSource).ExpandAll(false).EnableSorting(true)
.FormatSettings(formatsettings =>
{
formatsettings.Name("Amount").Format("C0").MaximumSignificantDigits(10).MinimumSignificantDigits(1).UseGrouping(true).Add();
}).Rows(rows =>
{
rows.Name("Country").Add(); rows.Name("Products").Add();
}).Columns(columns =>
{
columns.Name("Year").Add(); columns.Name("Quarter").Add();
}).Values(values =>
{
values.Name("Sold").Caption("Units Sold").Add(); values.Name("Amount").Caption("Sold Amount").Add();
})).ShowGroupingBar(true).Render()
public ActionResult Index()
{
var data = GetPivotData();
ViewBag.DataSource = data;
return View();
}
To disable the filter icon for a specific field, set the property ShowFilterIcon
to false to the corresponding field in PivotViewDataSourceSettings
.
In the below sample, the filter icon of “Quarter” and “Products” fields have been hidden.
@using Syncfusion.EJ2.PivotView
@Html.EJS().PivotView("PivotView").Width("100%").Height("300").DataSourceSettings(dataSource => dataSource.DataSource((IEnumerable<object>)ViewBag.DataSource).ExpandAll(false).EnableSorting(true)
.FormatSettings(formatsettings =>
{
formatsettings.Name("Amount").Format("C0").MaximumSignificantDigits(10).MinimumSignificantDigits(1).UseGrouping(true).Add();
}).Rows(rows =>
{
rows.Name("Country").Add(); rows.Name("Products").ShowFilterIcon(false).Add();
}).Columns(columns =>
{
columns.Name("Year").Add(); columns.Name("Quarter").ShowFilterIcon(false).Add();
}).Values(values =>
{
values.Name("Sold").Caption("Units Sold").Add(); values.Name("Amount").Caption("Sold Amount").Add();
})).ShowGroupingBar(true).Render()
public ActionResult Index()
{
var data = GetPivotData();
ViewBag.DataSource = data;
return View();
}
The Grouping Bar has an option to order members of a particular fields either in ascending or descending at runtime. In order to sort a field, click the sort icon and to reverse its sort direction, once again click the same sort icon. By default, the sort icon besides each field is enabled in the grouping bar and members will be arranged in ascending order. To disable the sort option, set the property ShowSortIcon
in PivotViewGroupingBarSettings
class to false.
By default, the sort icon is enabled in the grouping bar.
@using Syncfusion.EJ2.PivotView
@Html.EJS().PivotView("PivotView").Width("100%").Height("300").GroupingBarSettings( new PivotViewGroupingBarSettings {
ShowSortIcon =false }).DataSourceSettings(dataSource => dataSource.DataSource((IEnumerable<object>)ViewBag.DataSource).ExpandAll(false).EnableSorting(true)
.FormatSettings(formatsettings =>
{
formatsettings.Name("Amount").Format("C0").MaximumSignificantDigits(10).MinimumSignificantDigits(1).UseGrouping(true).Add();
}).Rows(rows =>
{
rows.Name("Country").Add(); rows.Name("Products").Add();
}).Columns(columns =>
{
columns.Name("Year").Add(); columns.Name("Quarter").Add();
}).Values(values =>
{
values.Name("Sold").Caption("Units Sold").Add(); values.Name("Amount").Caption("Sold Amount").Add();
})).ShowGroupingBar(true).Render()
public ActionResult Index()
{
var data = GetPivotData();
ViewBag.DataSource = data;
return View();
}
To disable the sort icon for a specific button, set the property ShowSortIcon
to false to the corresponding field in PivotViewDataSourceSettings
.
In the below sample, the sort icon of “Quarter” and “Country” fields have been hidden.
@using Syncfusion.EJ2.PivotView
@Html.EJS().PivotView("PivotView").Width("100%").Height("300").DataSourceSettings(dataSource => dataSource.DataSource((IEnumerable<object>)ViewBag.DataSource).ExpandAll(false).EnableSorting(true)
.FormatSettings(formatsettings =>
{
formatsettings.Name("Amount").Format("C0").MaximumSignificantDigits(10).MinimumSignificantDigits(1).UseGrouping(true).Add();
}).Rows(rows =>
{
rows.Name("Country").ShowSortIcon(false).Add(); rows.Name("Products").Add();
}).Columns(columns =>
{
columns.Name("Year").Add(); columns.Name("Quarter").ShowSortIcon(false).Add();
}).Values(values =>
{
values.Name("Sold").Caption("Units Sold").Add(); values.Name("Amount").Caption("Sold Amount").Add();
})).ShowGroupingBar(true).Render()
public ActionResult Index()
{
var data = GetPivotData();
ViewBag.DataSource = data;
return View();
}
The Grouping Bar has an option to remove any field at runtime. To remove a field, just click the remove icon. By default, the remove icon besides each field is enabled in the grouping bar. To disable the remove icon, set the property ShowRemoveIcon
in PivotViewGroupingBarSettings
class to false.
By default, the remove icon is enabled in the grouping bar.
@using Syncfusion.EJ2.PivotView
@Html.EJS().PivotView("PivotView").Width("100%").Height("300").GroupingBarSettings( new PivotViewGroupingBarSettings {
ShowRemoveIcon =false }).DataSourceSettings(dataSource => dataSource.DataSource((IEnumerable<object>)ViewBag.DataSource).ExpandAll(false).EnableSorting(true)
.FormatSettings(formatsettings =>
{
formatsettings.Name("Amount").Format("C0").MaximumSignificantDigits(10).MinimumSignificantDigits(1).UseGrouping(true).Add();
}).Rows(rows =>
{
rows.Name("Country").Add(); rows.Name("Products").Add();
}).Columns(columns =>
{
columns.Name("Year").Add(); columns.Name("Quarter").Add();
}).Values(values =>
{
values.Name("Sold").Caption("Units Sold").Add(); values.Name("Amount").Caption("Sold Amount").Add();
})).ShowGroupingBar(true).Render()
public ActionResult Index()
{
var data = GetPivotData();
ViewBag.DataSource = data;
return View();
}
To disable the remove icon for a specific button, set the property ShowRemoveIcon
to false to the corresponding field in PivotViewDataSourceSettings
.
In the below sample, the remove icon of fields “Year”, “Sold” and “Products” have been hidden.
@using Syncfusion.EJ2.PivotView
@Html.EJS().PivotView("PivotView").Width("100%").Height("300").DataSourceSettings(dataSource => dataSource.DataSource((IEnumerable<object>)ViewBag.DataSource).ExpandAll(false).EnableSorting(true)
.FormatSettings(formatsettings =>
{
formatsettings.Name("Amount").Format("C0").MaximumSignificantDigits(10).MinimumSignificantDigits(1).UseGrouping(true).Add();
}).Rows(rows =>
{
rows.Name("Country").Add(); rows.Name("Products").ShowRemoveIcon(false).Add();
}).Columns(columns =>
{
columns.Name("Year").ShowRemoveIcon(false).Add(); columns.Name("Quarter").Add();
}).Values(values =>
{
values.Name("Sold").Caption("Units Sold").ShowRemoveIcon(false).Add(); values.Name("Amount").Caption("Sold Amount").Add();
})).ShowGroupingBar(true).Render()
public ActionResult Index()
{
var data = GetPivotData();
ViewBag.DataSource = data;
return View();
}
The Grouping Bar has an option to drag-and-drop fields between row, column, value and filter axes in-order to change report at runtime. By default, all fields are available for drag-and-drop operation in the grouping bar. To disable these fields, set the property AllowDragAndDrop
in PivotViewGroupingBarSettings
class to false. This will prevent end user from changing the current report.
@using Syncfusion.EJ2.PivotView
@Html.EJS().PivotView("PivotView").Width("100%").Height("300").GroupingBarSettings( new PivotViewGroupingBarSettings {
AllowDragAndDrop =false }).DataSourceSettings(dataSource => dataSource.DataSource((IEnumerable<object>)ViewBag.DataSource).ExpandAll(false).EnableSorting(true)
.FormatSettings(formatsettings =>
{
formatsettings.Name("Amount").Format("C0").MaximumSignificantDigits(10).MinimumSignificantDigits(1).UseGrouping(true).Add();
}).Rows(rows =>
{
rows.Name("Country").Add(); rows.Name("Products").Add();
}).Columns(columns =>
{
columns.Name("Year").Add(); columns.Name("Quarter").Add();
}).Values(values =>
{
values.Name("Sold").Caption("Units Sold").Add(); values.Name("Amount").Caption("Sold Amount").Add();
})).ShowGroupingBar(true).Render()
public ActionResult Index()
{
var data = GetPivotData();
ViewBag.DataSource = data;
return View();
}
To disable dragging for a specific button, set the property AllowDragAndDrop
to false to the corresponding field in PivotViewDataSourceSettings
.
In the below sample, the drag and drop of the fields “Year” and “Products” have been restricted.
@using Syncfusion.EJ2.PivotView
@Html.EJS().PivotView("PivotView").Width("100%").Height("300").DataSourceSettings(dataSource => dataSource.DataSource((IEnumerable<object>)ViewBag.DataSource).ExpandAll(false).EnableSorting(true)
.FormatSettings(formatsettings =>
{
formatsettings.Name("Amount").Format("C0").MaximumSignificantDigits(10).MinimumSignificantDigits(1).UseGrouping(true).Add();
}).Rows(rows =>
{
rows.Name("Country").Add(); rows.Name("Products").AllowDragAndDrop(false).Add();
}).Columns(columns =>
{
columns.Name("Year").AllowDragAndDrop(false).Add(); columns.Name("Quarter").Add();
}).Values(values =>
{
values.Name("Sold").Caption("Units Sold").Add(); values.Name("Amount").Caption("Sold Amount").Add();
})).ShowGroupingBar(true).Render()
public ActionResult Index()
{
var data = GetPivotData();
ViewBag.DataSource = data;
return View();
}
End user can perform calculations over a group of values using the aggregation option. The value fields bound to the field list, appears with a dropdown icon, helps to select an appropriate aggregation type at runtime. On selection, the values in the Pivot Table will be changed dynamically. By default, the icon to set aggregation type is enabled in the grouping bar. To disable this icon, set the property ShowValueTypeIcon
in PivotViewGroupingBarSettings
class to false. To know more about aggregation, refer
here.
@using Syncfusion.EJ2.PivotView
@Html.EJS().PivotView("PivotView").Width("100%").Height("300").GroupingBarSettings( new PivotViewGroupingBarSettings {
AllowDragAndDrop =false }).DataSourceSettings(dataSource => dataSource.DataSource((IEnumerable<object>)ViewBag.DataSource).ExpandAll(false).EnableSorting(true)
.FormatSettings(formatsettings =>
{
formatsettings.Name("Amount").Format("C0").MaximumSignificantDigits(10).MinimumSignificantDigits(1).UseGrouping(true).Add();
}).Rows(rows =>
{
rows.Name("Country").Add(); rows.Name("Products").Add();
}).Columns(columns =>
{
columns.Name("Year").Add(); columns.Name("Quarter").Add();
}).Values(values =>
{
values.Name("Sold").Caption("Units Sold").Add(); values.Name("Amount").Caption("Sold Amount").Add();
})).ShowGroupingBar(true).Render()
public ActionResult Index()
{
var data = GetPivotData();
ViewBag.DataSource = data;
return View();
}
To disable the dropdown icon for a specific button, set the property ShowValueTypeIcon
to false to the corresponding field in PivotViewDataSourceSettings
.
In the below sample, the dropdown icon of field “Sold” is hidden.
@using Syncfusion.EJ2.PivotView
@Html.EJS().PivotView("PivotView").Width("100%").Height("300").DataSourceSettings(dataSource => dataSource.DataSource((IEnumerable<object>)ViewBag.DataSource).ExpandAll(false).EnableSorting(true)
.FormatSettings(formatsettings =>
{
formatsettings.Name("Amount").Format("C0").MaximumSignificantDigits(10).MinimumSignificantDigits(1).UseGrouping(true).Add();
}).Rows(rows =>
{
rows.Name("Country").Add(); rows.Name("Products").Add();
}).Columns(columns =>
{
columns.Name("Year").Add(); columns.Name("Quarter").Add();
}).Values(values =>
{
values.Name("Sold").Caption("Units Sold").ShowValueTypeIcon(false).Add(); values.Name("Amount").Caption("Sold Amount").Add();
})).ShowGroupingBar(true).Render()
public ActionResult Index()
{
var data = GetPivotData();
ViewBag.DataSource = data;
return View();
}
The property
ShowFilterIcon
,ShowSortIcon
,ShowValueTypeIcon
andAllowDragAndDrop
in fields ofPivotViewDataSourceSettings
are applicable for both grouping bar and field list.
The event OnFieldDropped
fires on whenever a field is dropped over an axis.
It has following parameters - DroppedAxis
, DroppedField
and DataSourceSettings
. In this sample we have modified the DroppedField
caption based on the DroppedAxis
.
@using Syncfusion.EJ2.PivotView
@Html.EJS().PivotView("PivotView").Width("100%").Height("300").DataSourceSettings(dataSource => dataSource.DataSource((IEnumerable<object>)ViewBag.DataSource).ExpandAll(false).EnableSorting(true)
.FormatSettings(formatsettings =>
{
formatsettings.Name("Amount").Format("C0").MaximumSignificantDigits(10).MinimumSignificantDigits(1).UseGrouping(true).Add();
}).Rows(rows =>
{
rows.Name("Country").Add(); rows.Name("Products").Add();
}).Columns(columns =>
{
columns.Name("Year").Add(); columns.Name("Quarter").Add();
}).Values(values =>
{
values.Name("Sold").Caption("Units Sold").Add(); values.Name("Amount").Caption("Sold Amount").Add();
})).ShowGroupingBar(true).OnFieldDropped("onFieldDropped").Render()
<script>
function onFieldDropped(args) {
args.droppedField.caption = args.droppedField.name + " --> " + args.droppedAxis;
}
</script>
public ActionResult Index()
{
var data = GetPivotData();
ViewBag.DataSource = data;
return View();
}
The event FieldDragStart
fires whenever a field drag starts from its axis. It allows the user to restrict the drag operation based on its parameters. It has the following parameters
fieldName
: It holds the name of the appropriate field.fieldItem
: It holds the complete definition of the appropriate field mentioned in data source settings.axis
: It holds the axis name where the draggable field lies.cancel
: It is a boolean property and by setting this to true, user can restrict the field from dragging.In the below sample, the drag operation for the fields in row axis alone is restricted.
@using Syncfusion.EJ2.PivotView
@Html.EJS().PivotView("PivotView").Width("100%").Height("300").DataSourceSettings(dataSource => dataSource.DataSource((IEnumerable<object>)ViewBag.DataSource).ExpandAll(false).EnableSorting(true)
.FormatSettings(formatsettings =>
{
formatsettings.Name("Amount").Format("C0").MaximumSignificantDigits(10).MinimumSignificantDigits(1).UseGrouping(true).Add();
}).Rows(rows =>
{
rows.Name("Country").Add(); rows.Name("Products").Add();
}).Columns(columns =>
{
columns.Name("Year").Add(); columns.Name("Quarter").Add();
}).Values(values =>
{
values.Name("Sold").Caption("Units Sold").Add(); values.Name("Amount").Caption("Sold Amount").Add();
})).ShowGroupingBar(true).FieldDragStart("fieldDragStart").Render()
<script>
function fieldDragStart(args) {
if(args.axis === 'rows') {
args.cancel = true;
}
}
</script>
public ActionResult Index()
{
var data = GetPivotData();
ViewBag.DataSource = data;
return View();
}
The event FieldDrop
fires whenever a field is dropped into an axis. It allows the user to restrict the drop operation based on its parameters. It has the following parameters
fieldName
: It holds the name of the appropriate field.dropField
: It holds the complete definition of the appropriate field mentioned in data source settings.draggedAxis
: It holds the axis name from where dragging was started.dropAxis
: It holds the axis name where the field is dropped.dropPosition
: It holds the dropped index among other existing fields in the axis.dataSourceSettings
: It holds complete pivot report.cancel
: It is a boolean property and by setting this to true, user can restrict the field from being dropped.In the below sample, dropping of any fields in value axis alone is restricted.
@using Syncfusion.EJ2.PivotView
@Html.EJS().PivotView("PivotView").Width("100%").Height("300").DataSourceSettings(dataSource => dataSource.DataSource((IEnumerable<object>)ViewBag.DataSource).ExpandAll(false).EnableSorting(true)
.FormatSettings(formatsettings =>
{
formatsettings.Name("Amount").Format("C0").MaximumSignificantDigits(10).MinimumSignificantDigits(1).UseGrouping(true).Add();
}).Rows(rows =>
{
rows.Name("Country").Add(); rows.Name("Products").Add();
}).Columns(columns =>
{
columns.Name("Year").Add(); columns.Name("Quarter").Add();
}).Values(values =>
{
values.Name("Sold").Caption("Units Sold").Add(); values.Name("Amount").Caption("Sold Amount").Add();
})).ShowGroupingBar(true).FieldDrop("fieldDrop").Render()
<script>
function fieldDrop(args) {
if(args.dropAxis === 'values') {
args.cancel = true;
}
}
</script>
public ActionResult Index()
{
var data = GetPivotData();
ViewBag.DataSource = data;
return View();
}
The event FieldRemove
fires when removing any field from their axis. It helps the user to limit the elimination of a field based on its parameters. It has the following parameters
fieldName
: It holds the name of the field to be removed.fieldItem
: It holds the complete definition of the appropriate field mentioned in data source settings.axis
: It holds the name of the axis from where it is to remove the field.dataSourceSettings
: It holds complete pivot report.cancel
: It is a boolean property and by setting this to true, user can restrict the field from removing.In the below sample, the field “Country” could not be removed from report by any UI operations.
@using Syncfusion.EJ2.PivotView
@Html.EJS().PivotView("PivotView").Width("100%").Height("300").DataSourceSettings(dataSource => dataSource.DataSource((IEnumerable<object>)ViewBag.DataSource).ExpandAll(false).EnableSorting(true)
.FormatSettings(formatsettings =>
{
formatsettings.Name("Amount").Format("C0").MaximumSignificantDigits(10).MinimumSignificantDigits(1).UseGrouping(true).Add();
}).Rows(rows =>
{
rows.Name("Country").Add(); rows.Name("Products").Add();
}).Columns(columns =>
{
columns.Name("Year").Add(); columns.Name("Quarter").Add();
}).Values(values =>
{
values.Name("Sold").Caption("Units Sold").Add(); values.Name("Amount").Caption("Sold Amount").Add();
})).ShowGroupingBar(true).FieldRemove("fieldRemove").Render()
<script>
function fieldRemove(args) {
if(args.fieldName === 'Country') {
args.cancel = true;
}
}
</script>
public ActionResult Index()
{
var data = GetPivotData();
ViewBag.DataSource = data;
return View();
}
The event AggregateMenuOpen
fires while clicking dropdown icon of the value field button UI. It allows to customize the aggregate types to be displayed in the dropdown menu. It has the following parameters
fieldName
: It holds the name of the field that opens the aggregate menu.aggregateTypes
: It holds the aggregation types set for a field.cancel
: It is a boolean property and by setting this to true, dropdown menu won’t be displayed.In the below sample, the aggregate types of the field “Amount” has been customized in it’s dropdown menu.
@using Syncfusion.EJ2.PivotView
@Html.EJS().PivotView("PivotView").Width("100%").Height("300").DataSourceSettings(dataSource => dataSource.DataSource((IEnumerable<object>)ViewBag.DataSource).ExpandAll(false).EnableSorting(true)
.FormatSettings(formatsettings =>
{
formatsettings.Name("Amount").Format("C0").MaximumSignificantDigits(10).MinimumSignificantDigits(1).UseGrouping(true).Add();
}).Rows(rows =>
{
rows.Name("Country").Add(); rows.Name("Products").Add();
}).Columns(columns =>
{
columns.Name("Year").Add(); columns.Name("Quarter").Add();
}).Values(values =>
{
values.Name("Sold").Caption("Units Sold").Add(); values.Name("Amount").Caption("Sold Amount").Add();
})).ShowGroupingBar(true).AggregateMenuOpen("aggregateMenuOpen").Render()
<script>
function aggregateMenuOpen(args) {
if(args.fieldName === 'Amount') {
args.aggregateTypes = ['Sum','Avg','Max'];
}
}
</script>
public ActionResult Index()
{
var data = GetPivotData();
ViewBag.DataSource = data;
return View();
}
The events
AggregateMenuOpen
,FieldRemove
,FieldDrop
,FieldDragStart
andOnFieldDropped
are applicable for both grouping bar and field list.