Number formatting in ASP.NET Core Pivot Table

28 Aug 202624 minutes to read

The Pivot Table component lets you display numeric values in standard number, currency, percentage, or custom formats to match the reporting needs of your application.

Supported format types

The Pivot Table component supports the following display formats for numeric values:

  • Number - Standard numeric formatting with optional grouping separators and configurable decimal places.
  • Currency - Formats currency values with appropriate symbols, optional grouping separators, and customizable decimal places.
  • Percentage - Values displayed as percentages with the % symbol.
  • Custom - User-defined formatting patterns for specific display requirements.

Defining number format settings

To configure number formats for numeric values, use the e-formatsettings property inside dataSourceSettings.

The following properties are available inside each formatSettings entry:

Essential formatting properties

Property Type Description
Name string The field name to which the formatting should be applied.
Format string The format pattern for the field.

Format type codes

Use these standard format codes as the value of the format property. You can also append a digit to set the number of decimal places (e.g., N2 for two decimal places).

  1. N - Numeric formatting (e.g., N produces 1,234.56; N2 produces 1,234.56).
  2. C - Currency formatting (e.g., C0 produces $1,234; the symbol is taken from the currency property).
  3. P - Percentage formatting (e.g., P1 produces 12.3% for the value 0.1234).

Note: When no format is specified, the component applies numeric formatting by default.

Additional formatting options

  • UseGrouping (boolean, default true): Controls the display of grouping separators. When true (default), values display with separators (for example, $100,000,000); when false, they display without separators (for example, $100000000).
  • Currency (string): The currency code to be considered for currency formatting (for example, USD, EUR, GBP).
<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="C2" currency="EUR" maximumSignificantDigits="10" minimumSignificantDigits="1" useGrouping="false"></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();
}

Number formatting example

You can also format the values at runtime using the formatting dialog. This option can be enabled by setting the AllowNumberFormatting property to true. The same has been discussed in some of the upcoming topics.

Important: To use the runtime formatting dialog (and the toolbar option), include the NumberFormatting module in the Pivot Table:

Custom format

Custom format lets you display numbers in your preferred pattern by setting the Format property within the e-formatsettings. You can use one or more format specifiers (shown in the table below) to control how values appear in the Pivot Table. The Input column shows the format value applied to the value 123 (except where noted).

Specifier Description Input Format Output
0 Replaces the zero with the corresponding digit if one is present. Otherwise, zero appears in the result string. { format: '0000' } '0123'
# Replaces the # symbol with the corresponding digit if one is present. Otherwise, no digit appears in the result string. { format: '####' } '1234'
. Denotes the number of digits permitted after the decimal point. { format: '###0.##0#' } '546321.000' (value 546321)
% Percent specifier; multiplies the value by 100 and appends the % symbol. { format: '0000 %' } '0100 %' (value 1)
$ Denotes currency formatting based on the global currency code specified in currency. { format: '$ ###.00' } '$ 13.00' (value 13)
; Denotes separate formats for positive, negative, and zero values. { format: '###.##;(###.00);-0' } '(120.00)' (value -120)
, Grouping separator (thousands). { format: '###,###' } '1,234,567' (value 1234567)
'String' Characters enclosed in single quotes are included literally in the result string. { format: "####.00 '@'" } '123.00 @' (value 123)

NOTE: When you define a custom format, certain properties such as UseGrouping and Currency in the format settings will be ignored.

@{var amount = "\"" + "Sum(Amount)" + "\"";}
@{var sold = "\"" + "Sum(Sold)" + "\"";}
@{ var totalPrice = amount + "+" + sold;}
<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="Sold" format="####.00 'Nos'" maximumSignificantDigits="10" minimumSignificantDigits="1" useGrouping="false"></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-field name="Total"></e-field>
        </e-values>
        <e-calculatedfieldsettings>
            <e-field name="Total" formula="@totalPrice"></e-field>
        </e-calculatedfieldsettings>
    </e-datasourcesettings>
</ejs-pivotview>
public ActionResult Index()
{
    var data = GetPivotData();
    ViewBag.DataSource = data;
    return View();
}

Custom number formatting

Toolbar

Number formatting can be applied at runtime through the built-in dialog, accessible from the toolbar. To enable this, set both the AllowNumberFormatting and ShowToolbar properties to true, include the NumberFormatting module, and add the NumberFormatting option to the toolbar property. The toolbar then displays the Number Formatting icon. Clicking this icon opens the dialog, where you can specify number formats for value fields directly within the Pivot Table.

<ejs-pivotview id="pivotview" allowExcelExport="true" allowNumberFormatting="true" allowConditionalFormatting="true" allowPdfExport="true" showToolbar="true" allowCalculatedField="true" showFieldList="true" width="100%" height="300" toolbar="@(new List<string>() {"New", "Save", "SaveAs", "Rename", "Remove", "Load",
"Grid", "Chart", "Export", "SubTotal", "GrandTotal", "ConditionalFormatting", "NumberFormatting", "FieldList" })" saveReport="saveReport" loadReport="loadReport" fetchReport="fetchReport" renameReport="renameReport" removeReport="removeReport" newReport="newReport">
    <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"></e-field>
            <e-field name="Order_Source" caption="Order Source"></e-field>
        </e-columns>
        <e-values>
            <e-field name="In_Stock" caption="In Stock"></e-field>
            <e-field name="Sold" caption="Units Sold"></e-field>
            <e-field name="Amount" caption="Sold Amount"></e-field>
        </e-values>
        <e-filters>
            <e-field name="Product_Categories" caption="Product Categories"></e-field>
        </e-filters>
    </e-datasourcesettings>
    <e-gridSettings columnWidth="140"></e-gridSettings>
    <e-displayOption view="Both"></e-displayOption>
</ejs-pivotview>
<style>
    #pivotview {
        width: 100%;
        height: 100%;
    }

    .e-tool-expand::before {
        content: '\e702';
    }
</style>
<script>

    function saveReport(args) {
        var reports = [];
        var isSaved = false;
        if (localStorage.pivotviewReports && localStorage.pivotviewReports !== "") {
            reports = JSON.parse(localStorage.pivotviewReports);
        }
        if (args.report && args.reportName && args.reportName !== '') {
            reports.map(function (item) {
                if (args.reportName === item.reportName) {
                    item.report = args.report;
                    isSaved = true;
                }
            });
            if (!isSaved) {
                reports.push(args);
            }
            localStorage.pivotviewReports = JSON.stringify(reports);
        }
    }
    function fetchReport(args) {
        var reportCollection = [];
        var reeportList = [];
        if (localStorage.pivotviewReports && localStorage.pivotviewReports !== "") {
            reportCollection = JSON.parse(localStorage.pivotviewReports);
        }
        reportCollection.map(function (item) {
            reeportList.push(item.reportName);
        });
        args.reportName = reeportList;
    }
    function loadReport(args) {
        var pivotObj = document.getElementById('pivotview').ej2_instances[0];
        var reportCollection = [];
        if (localStorage.pivotviewReports && localStorage.pivotviewReports !== "") {
            reportCollection = JSON.parse(localStorage.pivotviewReports);
        }
        reportCollection.map(function (item) {
            if (args.reportName === item.reportName) {
                args.report = item.report;
            }
        });
        if (args.report) {
            pivotObj.dataSourceSettings = JSON.parse(args.report).dataSourceSettings;
        }
    }
    function removeReport(args) {
        var reportCollection = [];
        if (localStorage.pivotviewReports && localStorage.pivotviewReports !== "") {
            reportCollection = JSON.parse(localStorage.pivotviewReports);
        }
        for (var i = 0; i < reportCollection.length; i++) {
            if (reportCollection[i].reportName === args.reportName) {
                reportCollection.splice(i, 1);
            }
        }
        if (localStorage.pivotviewReports && localStorage.pivotviewReports !== "") {
            localStorage.pivotviewReports = JSON.stringify(reportCollection);
        }
    }
    function renameReport(args) {
        var reportCollection = [];
        if (localStorage.pivotviewReports && localStorage.pivotviewReports !== "") {
            reportCollection = JSON.parse(localStorage.pivotviewReports);
        }
        reportCollection.map(function (item) {
            if (args.reportName === item.reportName) {
                item.reportName = args.rename;
            }
        });
        if (localStorage.pivotviewReports && localStorage.pivotviewReports !== "") {
            localStorage.pivotviewReports = JSON.stringify(reportCollection);
        }
    }
    function newReport() {
        var pivotObj = document.getElementById('pivotview').ej2_instances[0];
        pivotObj.setProperties({
            dataSourceSettings: {
                columns: [],
                rows: [],
                values: [],
                filters: []
            }
        }, false);
    }
</script>
public ActionResult Index()
{
    var data = GetPivotData();
    ViewBag.DataSource = data;
    return View();
}

Number formatting toolbar

Invoking formatting dialog through external button

The number formatting dialog can be opened programmatically by clicking an external button, using the ShowNumberFormattingDialog method of the PivotTable component.

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

<ejs-button id="calculated-field-btn" content="Formatting" 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.numberFormattingModule.showNumberFormattingDialog();
    });
</script>
public ActionResult Index()
{
    var data = GetPivotData();
    ViewBag.DataSource = data;
    return View();
}

Number formatting dialog

Events

NumberFormatting

The NumberFormatting event is triggered when the user clicks the ‘Apply’ button in the number formatting dialog to confirm their formatting settings. This event facilitates the validation or modification of the formatting settings applied by the user. It includes the following parameters:

Parameter Type Description
FormatName string Represents the name of the value field to which number formatting is applied in the dialog.
FormatSettings IFormatSettings Contains the user-defined formatting options, such as decimal places (MinimumFractionDigits, MaximumFractionDigits), currency symbols (Currency), or grouping separators (UseGrouping), applied to the field.
Cancel boolean It is a boolean property, and when set to true, the customization made in the number formatting dialog will not be applied.

The following sample demonstrates how to prevent number formatting changes for the ‘Amount’ field by setting the Cancel property to true in the NumberFormatting event.

<ejs-pivotview id="pivotview" allowNumberFormatting="true" width="100%" height="300" numberFormatting="numberFormatting" showToolbar="true" toolbar="@(new List<string>() {"NumberFormatting" })" >
    <e-datasourcesettings dataSource="@ViewBag.DataSource" >
        <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"></e-field>
            <e-field name="Order_Source" caption="Order Source"></e-field>
        </e-columns>
        <e-values>
            <e-field name="In_Stock" caption="In Stock"></e-field>
            <e-field name="Sold" caption="Units Sold"></e-field>
            <e-field name="Amount" caption="Sold Amount"></e-field>
        </e-values>
        <e-filters>
            <e-field name="Product_Categories" caption="Product Categories"></e-field>
        </e-filters>
    </e-datasourcesettings>
    <e-gridSettings columnWidth="140"></e-gridSettings>
    <e-displayOption view="Both"></e-displayOption>
</ejs-pivotview>
<style>
    #pivotview {
        width: 100%;
        height: 100%;
    }

    .e-tool-expand::before {
        content: '\e702';
    }
</style>
<script>
    function numberFormatting(args) {
       if (args.formatName === 'Amount' ) {
            args.cancel = true;
        }
    }
</script>
public ActionResult Index()
{
    var data = GetPivotData();
    ViewBag.DataSource = data;
    return View();
}

See also