Show or hide totals in ASP.NET CORE Syncfusion Pivot Table Control

27 Dec 202213 minutes to read

Show or hide grand totals

Allows to show or hide grand totals in rows and columns using the showGrandTotals property. To hide the grand totals in rows and columns, set the property showGrandTotals in e-datasourcesettings class to false.

End user can also hide grand totals for row or columns separately by setting the property showRowGrandTotals or showColumnGrandTotals in e-datasourcesettings class to false respectively.

By default, showGrandTotals, showRowGrandTotals and showColumnGrandTotals properties in e-datasourcesettings class are set as true.

CSHTML

<ejs-pivotview id="PivotView" height="300">
    <e-datasourcesettings dataSource="@ViewBag.DataSource" expandAll="false" enableSorting="true" showGrandTotals="false">
        <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>

GrandTotal.cs

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

Show grand totals at top or bottom

Allows to show grand totals either at top or bottom in rows and columns using the grandTotalsPosition property. To show the grand totals at top in rows and columns, set the grandTotalsPosition property in e-datasourcesettings to Top.

CSHTML

<ejs-pivotview id="PivotView" height="300">
    <e-datasourcesettings dataSource="@ViewBag.DataSource" grandTotalsPosition="Top">
        <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>

GrandTotal.cs

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

Show or hide sub-totals

Allows to show or hide sub-totals in rows and columns using the showSubTotals property. To hide all the sub-totals in rows and columns, set the property showSubTotals in e-datasourcesettings class to false. End user can also hide sub-totals for rows or columns separately by setting the property showRowSubTotals or showColumnSubTotals in e-datasourcesettings class to false respectively.

By default, showSubTotals, showRowSubTotals and showColumnSubTotals in e-datasourcesettings class are set as true.

CSHTML

<ejs-pivotview id="PivotView" height="300">
    <e-datasourcesettings dataSource="@ViewBag.DataSource" expandAll="false" enableSorting="true" showSubTotals="false">
        <e-formatsettings>
            <e-field name="Amount" format="C0" maximumSignificantDigits="10" minimumSignificantDigits="1" useGrouping="true"></e-field>
        </e-formatsettings>
        <e-drilledmembers>
            <e-field name="Country" items="@ViewBag.drilledMembers"></e-field>
        </e-drilledmembers>
        <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>

SubTotal.cs

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

Show or hide sub-totals for specific fields

Allows to show or hide sub-totals for specific fields in rows and columns using the showSubTotals) property. To hide sub-totals for a specific field in row or column axis, set the property showSubTotals property in Row or Column class to false respectively.

By default, showSubTotals property in Row or Column class is set as true.

CSHTML

<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" showSubTotals="false"></e-field>
            <e-field name="Products"></e-field>
        </e-rows>
        <e-columns>
            <e-field name="Year" caption="Year" showSubTotals="false"></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>

SubTotalSpecific.cs

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

Show sub-totals at top or bottom

Allows to show sub-totals either at top or bottom of the header group in rows and columns by using the subTotalsPosition property. By default, subTotalsPosition property is set to Auto, which means that column sub-totals are displayed at the bottom and row sub-totals are displayed at the top of the header group in the pivot table.

To show sub-totals at top of the header group in rows and columns, set the subTotalsPosition property in e-datasourcesettings to Top.

CSHTML

<ejs-pivotview id="PivotView" height="300">
    <e-datasourcesettings dataSource="@ViewBag.DataSource" expandAll="false" enableSorting="true" subTotalsPosition="Top">
        <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>

GrandTotal.cs

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

To show sub-totals at bottom of the header group in rows and columns, set the subTotalsPosition property in e-datasourcesettings to Bottom.

CSHTML

<ejs-pivotview id="PivotView" height="300">
    <e-datasourcesettings dataSource="@ViewBag.DataSource" expandAll="false" enableSorting="true" subTotalsPosition="Bottom">
        <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>

GrandTotal.cs

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

Show or hide totals using toolbar

It can also be achieved using built-in toolbar options by setting the showToolbar property in ejs-pivotview class to true. Also, include the items GrandTotal and SubTotal within the toolbar property in ejs-pivotview class. End user can now see “Show/Hide Grand totals” and “Show/Hide Sub totals” icons in toolbar UI automatically.

The grand totals and sub-totals can be dynamically displayed at the top or bottom of the pivot table’s row and column axes by using the built-in options “Grand totals position” and “Subtotals position” available in the grand totals and sub-totals drop down menus, respectively.

CSHTML

<ejs-pivotview id="pivotview"  showToolbar="true" width="100%" height="300" toolbar="@(new List<string>() {"SubTotal", "GrandTotal" })" >
    <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"></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>

Toolbar.cs

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