Search results

Getting started with ASP.NET Core Pivot Table control

Starting with v16.2.0.x, if you reference Syncfusion assemblies from trial setup or from the NuGet feed, you also have to include a license key in your projects. Please refer to this link to know about registering Syncfusion license key in your ASP.NET MVC application to use our components.

Prerequisites

The official prerequisites to create and run an ASP.NET Core 2.x application on Windows environment are described in the .NET Core documentation website.

Create ASP.NET Core web application

Step 1: Choose File > New > Project… in the Visual Studio menu bar.

new project in aspnetcore2.x

Step 2: Select Installed > Visual C# > .NET Core and choose the required .NET Framework in the drop-down.

Step 3: Select ASP.NET Core Web Application and change the application name, and then click OK.

The Essential JS 2 supports 4.5+ .NET Framework in the ASP.NET Core application. i.e. The minimum target framework is 4.5 for Syncfusion ASP.NET Core (Essential JS 2).

aspnetcore2.x project template

Step 4: Choose .NET Core with ASP.NET Core 2.0 and select Web Application(Model-View-Controller), and then click OK. The web application project is now created with default ASP.NET Core template.

aspnetcore2.x web application template

Configure Essential JS 2 in the application

Step 1: Add the Syncfusion.EJ2.AspNet.Core NuGet package to the new application by using the Nuget Package Manager. Right-click the project and select Manage NuGet Packages….

Refer to this article to learn more details about installing Essential JS 2 NuGet packages in various OS environment.

aspnetcore2.x manage nuget packages

Step 2: Search the Syncfusion.EJ2.AspNet keyword in the Browse tab and install Syncfusion.EJ2.AspNet.Core NuGet package in the application.

aspnetcore2.x install nuget packages

The Essential JS 2 package will be included in the project, after the installation process is completed.

Note: The Syncfusion.EJ2.AspNet.Core NuGet package has dependencies, Newtonsoft.Json for JSON serialization and Syncfusion.Licensing for validating Syncfusion license key.

Step 3: Open ~/Views/_ViewImports.cshtml file and import the Syncfusion.EJ2 package.

@addTagHelper *, Syncfusion.EJ2

Step 4: Add the client-side resources through CDN or local npm package in the <head> element of the ~/Views/Shared/_Layout.cshtml layout page.

<head>
    ....
    ....

    <!-- Syncfusion Essential JS 2 Styles -->
    <link rel="stylesheet" href="https://cdn.syncfusion.com/ej2/material.css" />

    <!-- Syncfusion Essential JS 2 Scripts -->
    <script src="https://cdn.syncfusion.com/ej2/dist/ej2.min.js"></script>
</head>

Step 5: Add the Essential JS 2 Script Manager at the end of <body> element in the ~/Views/Shared/_Layout.cshtml layout page.

<body>
    ....
    ....
    <!-- Syncfusion Essential JS 2 ScriptManager -->
    <ejs-scripts></ejs-scripts>
</body>

Browser compatibility

Polyfills are required to use the Pivot Table in Internet Explorer 11 browser. Refer the documentation for more details.

Adding component to the application

Add the below code to your Index.cshtml view page which is present under Views/Home folder, to initialize the pivot table component with sample OLAP data source.

tagHelper
pivottable.cs
<ejs-pivotview id="PivotView" height="300">
    <e-datasourcesettings dataSource="@ViewBag.DataSource" expandAll="false" enableSorting="true">
        <e-formatsettings>
            <e-field name="Amount" format="C0" useGrouping="true"></e-field>
        </e-formatsettings>
        <e-rows>
            <e-field name="Country"></e-field>
            <e-field name="Products"></e-field>
        </e-rows>
        <e-columns>
            <e-field name="Year" caption="Year"></e-field>
            <e-field name="Quarter"></e-field>
        </e-columns>
        <e-values>
            <e-field name="Sold" caption="Units Sold"></e-field>
            <e-field name="Amount" caption="Sold Amount"></e-field>
        </e-values>
    </e-datasourcesettings>
</ejs-pivotview>
public IActionResult Index()
{
    var data = GetPivotData();
    ViewBag.DataSource = data;
    return View();
}

public List<PivotData> GetPivotData()
{
    List<PivotData> pivotData = new List<PivotData>();
    pivotData.Add(new PivotData { Sold = 31, Amount = 52824, Country = "France", Products = "Mountain Bikes", Year = "FY 2015", Quarter = "Q1" });
    pivotData.Add(new PivotData { Sold = 51, Amount = 86904, Country = "France", Products = "Mountain Bikes", Year = "FY 2015", Quarter = "Q2" });
    pivotData.Add(new PivotData { Sold = 90, Amount = 153360, Country = "France", Products = "Mountain Bikes", Year = "FY 2015", Quarter = "Q3" });
    pivotData.Add(new PivotData { Sold = 25, Amount = 42600, Country = "France", Products = "Mountain Bikes", Year = "FY 2015", Quarter = "Q4" });
    pivotData.Add(new PivotData { Sold = 27, Amount = 46008, Country = "France", Products = "Mountain Bikes", Year = "FY 2016", Quarter = "Q1" });
    return pivotData;
}

public class PivotData
{
    public int Sold { get; set; }
    public double Amount { get; set; }
    public string Country { get; set; }
    public string Products { get; set; }
    public string Year { get; set; }
    public string Quarter { get; set; }
}

Adding fields to row, column, value and filter axes

Now that pivot table is initialized and assigned with sample data, will further move to showcase the component by organizing appropriate fields in row, column, value and filter axes.

In e-datasourcesettings tag, four major axes - Rows, Columns, Values and Filters plays a vital role in defining and organizing fields from the bound data source, to render the entire pivot table component in a desired format.

Rows – Collection of fields that needs to be displayed in row axis of the pivot table.

Columns – Collection of fields that needs to be displayed in column axis of the pivot table.

Values – Collection of fields that needs to be displayed as aggregated numeric values in the pivot table.

Filters - Collection of fields that would act as master filter over the data bound in row, column and value axes of the pivot table.

In-order to define each field in the respective axis, the following basic properties should be set.

  • name: It allows to set the field name from the bound data source. It’s casing should match exactly like in the data source and if not set properly, the pivot table will not be rendered.
  • caption: It allows to set the field caption, which is the alias name of the field that needs to be displayed in the pivot table.
  • type: It allows to set the summary type of the field. By default, Sum is applied.

In this illustration, “Year” and “Quarter” are added in column, “Country” and “Products” in row, and “Sold” and “Amount” in value section respectively.

tagHelper
fields.cs
<ejs-pivotview id="PivotView" height="300">
    <e-datasourcesettings dataSource="@ViewBag.DataSource">
        <e-rows>
            <e-field name="Country"></e-field>
            <e-field name="Products"></e-field>
        </e-rows>
        <e-columns>
            <e-field name="Year" caption="Year"></e-field>
            <e-field name="Quarter"></e-field>
        </e-columns>
        <e-values>
            <e-field name="Sold" caption="Units Sold"></e-field>
            <e-field name="Amount" caption="Sold Amount"></e-field>
        </e-values>
    </e-datasourcesettings>
</ejs-pivotview>
public ActionResult Index()
{
    var data = GetPivotData();
    ViewBag.DataSource = data;
    return View();
}

Applying formatting to a value field

Formatting defines a way in which values should be displayed. For example, format “C” denotes the values should be displayed in currency pattern. To do so, define the e-formatsettings tag with its name and format properties and add it to e-formatSettings. In this illustration, the name property is set as Amount, a field from value section and its format is set as currency. Likewise, we can set format for other value fields as well and add it to e-formatSettings.

Only fields from value section, which is in the form of numeric data values are applicable for formatting.

tagHelper
format.cs
<ejs-pivotview id="PivotView" height="300">
    <e-datasourcesettings dataSource="@ViewBag.DataSource" expandAll="false" enableSorting="true">
        <e-formatsettings>
            <e-field name="Amount" format="C0" maximumSignificantDigits="10" minimumSignificantDigits="1" useGrouping="true"></e-field>
        </e-formatsettings>
        <e-rows>
            <e-field name="Country"></e-field>
            <e-field name="Products"></e-field>
        </e-rows>
        <e-columns>
            <e-field name="Year" caption="Year"></e-field>
            <e-field name="Quarter"></e-field>
        </e-columns>
        <e-values>
            <e-field name="Sold" caption="Units Sold"></e-field>
            <e-field name="Amount" caption="Sold Amount"></e-field>
        </e-values>
    </e-datasourcesettings>
</ejs-pivotview>
public ActionResult Index()
{
    var data = GetPivotData();
    ViewBag.DataSource = data;
    return View();
}

Enable Grouping Bar

The grouping bar feature 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 alter pivot table at runtime. It also provides option to sort, filter and remove fields. It can be enabled by setting the showGroupingBar property to true.

tagHelper
groupingbar.cs
<ejs-pivotview id="PivotView" height="300" showGroupingBar="true">
    <e-datasourcesettings dataSource="@ViewBag.DataSource" expandAll="false" enableSorting="true" allowLabelFilter="true" allowValueFilter="true">
        <e-formatsettings>
            <e-field name="Amount" format="C0" maximumSignificantDigits="10" minimumSignificantDigits="1" useGrouping="true"></e-field>
        </e-formatsettings>
        <e-rows>
            <e-field name="Country"></e-field>
            <e-field name="Products"></e-field>
        </e-rows>
        <e-columns>
            <e-field name="Year" caption="Year"></e-field>
            <e-field name="Quarter"></e-field>
        </e-columns>
        <e-values>
            <e-field name="Sold" caption="Units Sold"></e-field>
            <e-field name="Amount" caption="Sold Amount"></e-field>
        </e-values>
    </e-datasourcesettings>
</ejs-pivotview>
public ActionResult Index()
{
    var data = GetPivotData();
    ViewBag.DataSource = data;
    return View();
}

Enable Pivot Field List

The field list allows to add or remove fields and also rearrange the fields between different axes, including column, row, value, and filter along with filter and sort options dynamically at runtime. It can be enabled by setting the showFieldList property to true.

tagHelper
fieldlist.cs
<ejs-pivotview id="PivotView" height="300" showFieldList="true">
    <e-datasourcesettings dataSource="@ViewBag.DataSource" expandAll="false" enableSorting="true" allowLabelFilter="true" allowValueFilter="true">
        <e-formatsettings>
            <e-field name="Amount" format="C0" maximumSignificantDigits="10" minimumSignificantDigits="1" useGrouping="true"></e-field>
        </e-formatsettings>
        <e-rows>
            <e-field name="Country"></e-field>
            <e-field name="Products"></e-field>
        </e-rows>
        <e-columns>
            <e-field name="Year" caption="Year"></e-field>
            <e-field name="Quarter"></e-field>
        </e-columns>
        <e-values>
            <e-field name="Sold" caption="Units Sold"></e-field>
            <e-field name="Amount" caption="Sold Amount"></e-field>
        </e-values>
    </e-datasourcesettings>
</ejs-pivotview>
public ActionResult Index()
{
    var data = GetPivotData();
    ViewBag.DataSource = data;
    return View();
}

Calculated field

The calculated field feature allows user to insert or add a new calculated field based on the available fields from the bound data source using basic arithmetic operators. The calculated field can be included in pivot table using the e-calculatedFieldSetting tag from code behind. Or else, calculated fields can be added at run time through the built-in dialog by just setting the allowCalculatedField property to true in pivot table. You will see a button enabled in the Field List UI automatically to invoke the calculated field dialog and perform necessary operation.

Calculated field is applicable only for value fields.

tagHelper
calculatedfield.cs
@{var amount = "\"" + "Sum(Amount)" + "\"";}
@{var sold = "\"" + "Sum(Sold)" + "\"";}
@{ var totalPrice = amount + "+" + sold;}

<ejs-button id="calculated-field-btn" content="Calculated Field" isPrimary="true"></ejs-button>
<ejs-pivotview id="PivotView" height="300" allowCalculatedField="true">
    <e-datasourcesettings dataSource="@ViewBag.DataSource" expandAll="false" enableSorting="true">
        <e-formatsettings>
            <e-field name="Amount" format="C0" maximumSignificantDigits="10" minimumSignificantDigits="1" useGrouping="true"></e-field>
        </e-formatsettings>
        <e-rows>
            <e-field name="Country"></e-field>
            <e-field name="Products"></e-field>
        </e-rows>
        <e-columns>
            <e-field name="Year" caption="Year"></e-field>
            <e-field name="Quarter"></e-field>
        </e-columns>
        <e-values>
            <e-field name="Sold" caption="Units Sold"></e-field>
            <e-field name="Amount" caption="Sold Amount"></e-field>
        </e-values>
        <e-calculatedfieldsettings>
            <e-field name="Total" formula="@totalPrice"></e-field>
        </e-calculatedfieldsettings>
    </e-datasourcesettings>
</ejs-pivotview>

<script>
    document.getElementById("calculated-field-btn").addEventListener('click', function () {
        var pivotObj = document.getElementById("PivotView").ej2_instances[0];
        pivotObj.createCalculatedFieldDialog();
    });
</script>
public ActionResult Index()
{
    var data = GetPivotData();
    ViewBag.DataSource = data;
    return View();
}

Output be like the below.

Alt text