ASP.NET MVC

Search results

Field List in ASP.NET MVC Pivot Table control

08 Apr 2021 / 7 minutes to read

The pivot table provides a built-in Field List similar to Microsoft Excel. It allows to add or remove fields and also rearrange them between different axes, including column, row, value, and filter along with sort and filter options dynamically at runtime.

The field list can be displayed in two different formats to interact with pivot table. They are:

In-built Field List (Popup): To display the field list icon in pivot table UI to invoke the built-in dialog.
Stand-alone Field List (Fixed): To display the field list in a static position within a web page.

To enable the field list in pivot table UI, set the ShowFieldList property in PivotView class to true. A small icon will appear on the top left corner of the pivot table and clicking on this icon, field list dialog will appear.

The field list icon will be displayed at the top right corner of the pivot table, when grouping bar is enabled.

razor

fieldlist.cs

@Html.EJS().PivotView("PivotView").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(); 
    })).ShowFieldList(true).Render()

public ActionResult Index()
{
    var data = GetPivotData();
    ViewBag.DataSource = data;
    return View();
}

output
output

Stand-alone Field List (Fixed)

The field list can be rendered in a static position, anywhere in web page layout, like a separate component. To do so, you need to set RenderMode property to Mode.Fixed in PivotFieldList.

To make field list interact with pivot table, you need to use the UpdateView and Update methods for data source update in both field list and pivot table simultaneously.

razor

Static.cs

@using Syncfusion.EJ2.PivotView

@Html.EJS().PivotView("PivotView").Height("300").EnginePopulated("onGridEnginePopulate").Render()
<br />
@Html.EJS().PivotFieldList("Static_FieldList").RenderMode(Mode.Fixed).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").Caption("Year").Add(); columns.Name("Quarter").Add();
 }).Values(values =>
 {
     values.Name("Sold").Caption("Units Sold").Add(); values.Name("Amount").Caption("Sold Amount").Add();
 })).EnginePopulated("onFieldListEnginePopulate").Render()

<style>
    #Static_FieldList {
        width: 400px;
    }
</style>

<script>
    var pivotObj; var fieldlistObj;
    function onGridEnginePopulate(args) {
        pivotObj = document.getElementById('PivotView').ej2_instances[0];
        fieldlistObj = document.getElementById('PivotFieldList').ej2_instances[0];
        if (fieldlistObj) {
            fieldlistObj.update(pivotObj);
        }
    }
    function onFieldListEnginePopulate(args) {
        pivotObj = document.getElementById('PivotView').ej2_instances[0];
        fieldlistObj = document.getElementById('PivotFieldList').ej2_instances[0];
        fieldlistObj.updateView(pivotObj);
    }
</script>

public ActionResult Index()
{
    var data = GetPivotData();
    ViewBag.DataSource = data;
    return View();
}

output

Invoking dynamic Field List (Customized)

Also, you can display the field list dialog independently through other means. For example, you can invoke the field list dialog on an external button click. To do so, set RenderMode property to Mode.Popup and since on button click, field list dialog will be invoked.

Meanwhile, you can display the field list dialog at specific target element within a webpage using target property. By default, the target value is null, which refers the document.body element.

Moreover, to make field list interact with pivot table, you need to use the updateView and update methods for data source update in both field list and pivot table simultaneously.

The below sample code illustrates the field list dialog invoked on an external button click.

razor

Popup.cs

@using Syncfusion.EJ2.PivotView

@Html.EJS().Button("fieldlistbtn").Content("Field List").IsPrimary(true).Render()

<div id="Popup_FieldList"></div>

@Html.EJS().PivotView("PivotView").Height("300").EnginePopulated("onGridEnginePopulate").Render()

@Html.EJS().PivotFieldList("PivotFieldList").RenderMode(Syncfusion.EJ2.PivotView.Mode.Popup).Target("#Popup_FieldList").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").Caption("Year").Add(); columns.Name("Quarter").Add();
 }).Values(values =>
 {
     values.Name("Sold").Caption("Units Sold").Add(); values.Name("Amount").Caption("Sold Amount").Add();
 })).EnginePopulated("onFieldListEnginePopulate").Render()

<style>
    .e-toggle-field-list {
        display: none !important;
    }
</style>

<script>
    var pivotObj; var fieldlistObj;
    function onGridEnginePopulate(args) {
        pivotObj = document.getElementById('PivotView').ej2_instances[0];
        fieldlistObj = document.getElementById('PivotFieldList').ej2_instances[0];
        if (fieldlistObj) {
            fieldlistObj.update(pivotObj);
        }
    }
    function onFieldListEnginePopulate(args) {
        pivotObj = document.getElementById('PivotView').ej2_instances[0];
        fieldlistObj = document.getElementById('PivotFieldList').ej2_instances[0];
        fieldlistObj.updateView(pivotObj);
    }
    document.getElementById('fieldlistbtn').onclick = function () {
        fieldlistObj = document.getElementById('PivotFieldList').ej2_instances[0];
        fieldlistObj.dialogRenderer.fieldListDialog.show();
    };
</script>

public ActionResult Index()
{
    var data = GetPivotData();
    ViewBag.DataSource = data;
    return View();
}

output

Add or remove fields

Using check box besides each field, end user can select or unselect to add or remove fields respectively from the report at runtime.

output

Remove specific field(s) from displaying

When a data source is bound to the component, fields will be automatically populated inside the Field List. In such case, user can also restrict specific field(s) from displaying. To do so, set the appropriate field name(s) in ExcludeFields property belonging to PivotViewDataSourceSettings class.

razor

fieldlist.cs

@Html.EJS().PivotView("PivotView").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(); 
    }).ExcludeFields(ViewBag.Excludefields)).ShowFieldList(true).Render()

public ActionResult Index()
{
    var data = GetPivotData();
    ViewBag.DataSource = data;
    ViewBag.Excludefields = new string[] { "Products", "Amount" };
    return View();
}

output

Re-arranging fields

In-order to re-arrange, drag any field from the field list and drop it into the column, row, value, or filter axis using the drag-and-drop holder. It helps end user to alter the report at runtime.

output

Filtering members

Using the filter icon besides each field in row, column and filter axes, members can be either included or excluded at runtime. To know more about member filtering, refer here.

output
output
output

Sorting members

Using the sort icon besides each field in row and column axes, members can be arranged either in ascending or descending order at runtime. To know more about member sorting, refer here.

output
output

Calculated fields

The calculated field support allows end user to add a new calculated field based on the available fields from the bound data source using basic arithmetic operators. To enable this support in Field List UI, set the AllowCalculatedField property in PivotView class to true in pivot table. Now a button will be seen automatically inside the field list UI which will invoke the calculated field dialog on click. To know more about calculated field, refer here.

output
output
output

Changing aggregation type of value fields at runtime

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. To know more about aggregation, refer here.

output

output
output

Defer layout update

Defer layout update support to update the pivot table only on demand and not during every user action. To enable this support in Field List UI, set the AllowDeferLayoutUpdate property in PivotView class to true in pivot table. Now a check box inside Field List UI will be seen in checked state, allowing pivot table to update only on demand. To know more about defer layout, refer here.

output

It can also be viewed in toolbar by setting ShowFieldList and ShowToolbar properties in PivotView class to true. Also, include the item FieldList within the Toolbar property in PivotView class. When toolbar is enabled, field list icon will be automatically added into the toolbar and the icon won’t appear on top left corner in the pivot table component.

razor

fieldlist.cs

@using Syncfusion.EJ2.PivotView

@Html.EJS().PivotView("pivotview").Width("100%").Height("300").ShowToolbar(true).ShowFieldList(true).AllowExcelExport(true).AllowNumberFormatting(true).AllowConditionalFormatting(true).AllowPdfExport(true).ShowToolbar(true).AllowCalculatedField(true).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();
})
).Toolbar(new List<string>() { "FieldList" }).Render()

public ActionResult Index()
{
    var data = GetPivotData();
    ViewBag.DataSource = data;
    return View();
}

output

Set caption to fields which isn’t bound to the report

One can set the caption to all fields from the data source even if it is not bound to the actual report. It can be achieved using the EnginePopulated event. On doing so, caption of the respective field will be displayed in both grouping bar and field list.

In the sample, we have set caption to the fields Year and Quarter dynamically.

razor

fieldlist.cs

@Html.EJS().PivotView("PivotView").Height("300").EnginePopulated("onGridEnginePopulate").DataSourceSettings(dataSource => dataSource.DataSource((IEnumerable<object>)ViewBag.DataSource)
    .FormatSettings(formatsettings => 
    { 
        formatsettings.Name("Amount").Format("C0").MaximumSignificantDigits(10).MinimumSignificantDigits(1).UseGrouping(true).Add(); 
    }).Rows(rows => 
    { 
        rows.Name("Country").Add();
    }).Columns(columns => 
    { 
        columns.Name("Products").Add();
    }).Values(values => 
    { 
        values.Name("Sold").Caption("Units Sold").Add(); 
        values.Name("Amount").Caption("Sold Amount").Add(); 
    })).ShowFieldList(true).Render()


     <script>
    var pivotObj;
    function onGridEnginePopulate(args) {
        pivotObj = document.getElementById('PivotView').ej2_instances[0];
         Object.keys(pivotObj.engineModule.fieldList).forEach((key, index) => {
                if (key === 'Quarter') {
                    pivotObj.engineModule.fieldList[key].caption = 'Production Quarter Year';
                }
                else if (key === 'Year') {
                    pivotObj.engineModule.fieldList[key].caption = 'Production Year';
                }
      }); 

    }
</script>

public IActionResult Index()
{
    var data = GetPivotData();
    ViewBag.DataSource = data;
    return View();
}

output

Events

EnginePopulated

The EnginePopulated event is available in both Pivot Table and Field List.

The event EnginePopulated is triggered in field list whenever the report gets modified. The updated report is passed to the pivot table via UpdateView method written within this event to refresh the same.
Likewise, EnginePopulated event is triggered in pivot table whenever the report gets modified. The updated report is passed to the field list via Update method written within this event to refresh the same.

The event EnginePopulated is triggered after engine is populated. It has following parameters - DataSourceSettings, PivotFieldList and PivotValues.

Note: This event is not required for Popup field list since it is a in built one.

razor

Static.cs

@using Syncfusion.EJ2.PivotView

@Html.EJS().PivotView("PivotView").Height("300").EnginePopulated("onGridEnginePopulate").Render()
<br />
@Html.EJS().PivotFieldList("Static_FieldList").RenderMode(Mode.Fixed).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").Caption("Year").Add(); columns.Name("Quarter").Add();
 }).Values(values =>
 {
     values.Name("Sold").Caption("Units Sold").Add(); values.Name("Amount").Caption("Sold Amount").Add();
 })).EnginePopulated("onFieldListEnginePopulate").Render()

<style>
    #Static_FieldList {
        width: 400px;
    }
</style>

<script>
    var pivotObj; var fieldlistObj;
    function onGridEnginePopulate(args) {
        pivotObj = document.getElementById('PivotView').ej2_instances[0];
        fieldlistObj = document.getElementById('PivotFieldList').ej2_instances[0];
        if (fieldlistObj) {
            fieldlistObj.update(pivotObj);
        }
    }
    function onFieldListEnginePopulate(args) {
        pivotObj = document.getElementById('PivotView').ej2_instances[0];
        fieldlistObj = document.getElementById('PivotFieldList').ej2_instances[0];
        fieldlistObj.updateView(pivotObj);
    }
</script>

public ActionResult Index()
{
    var data = GetPivotData();
    ViewBag.DataSource = data;
    return View();
}

FieldListRefreshed

The event FieldListRefreshed is triggered whenever there is any change done in the field list UI. It has following parameter - DataSourceSettings and PivotValues. It allows user to identify each field list update. This event is applicable only for static field list.

razor

Popup.cs

@using Syncfusion.EJ2.PivotView

   @Html.EJS().PivotView("PivotGrid").Height("300").DataSource(dataSource => dataSource.Data((IEnumerable<object>)ViewBag.Data).FieldListRefreshed("fieldListRefreshed").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(); 
    })).ShowFieldList(true).Render()

<script>
    function fieldListRefreshed(args) {
        //Triggers, whenever field list get refreshed.
    }
</script>

public ActionResult Index()
{
    var data = GetPivotData();
    ViewBag.DataSource = data;
    return View();
}

FieldDropped

The event OnFieldDropped fires whenever a field is dropped in an axis. It has following parameters - DroppedAxis, DroppedField and DataSourceSettings. In this illustration, we have modified the DroppedField caption through this event at runtime.

razor

dropped.cs

@using Syncfusion.EJ2.PivotView

@Html.EJS().PivotView("PivotView").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();
 })
 ).ShowFieldList(true).OnFieldDropped("onFieldDropped").Render()

<script>
    var pivotGridObj;
    function onFieldDropped(args) {
        args.droppedField.caption = args.droppedField.name + " --> " + args.droppedAxis;        
    }
</script>

public ActionResult Index()
{
    var data = GetPivotData();
    ViewBag.data = data;
    return View();
}

output

Field List in ASP.NET MVC Pivot Table control

In-built Field List (Popup)

Stand-alone Field List (Fixed)

Invoking dynamic Field List (Customized)

Add or remove fields

Remove specific field(s) from displaying

Re-arranging fields

Filtering members

Sorting members

Calculated fields

Changing aggregation type of value fields at runtime

Defer layout update

Show field list using toolbar

Set caption to fields which isn’t bound to the report

Events

EnginePopulated

FieldListRefreshed

FieldDropped

See Also