Format Strings in ASP.NET WebForms PivotGrid

12 Jul 20223 minutes to read

IMPORTANT

This feature is applicable only for Relational data source at Client Mode.

Formatted numeric values like n (number with decimal points), c (currency or accounting) and p (percentage) can be displayed with desired decimal places using FormatString option.

Note: This is applicable only when the Format option is set.

JS

<ej:PivotGrid ID="PivotGrid1" Url="" runat="server" ClientIDMode="Static">
    <DataSource>
        <Rows>
            <ej:Field FieldName="Country" FieldCaption="Country"></ej:Field>
            <ej:Field FieldName="State" FieldCaption="State"></ej:Field>
        </Rows>
        <Columns>
            <ej:Field FieldName="Product" FieldCaption="Product"></ej:Field>
        </Columns>
        <Values>
            <ej:Field FieldName="Amount" FieldCaption="Amount" Format="currency" FormatString="{0:c0}"></ej:Field>
        </Values>
    </DataSource>
    <ClientSideEvents Load="onLoad" />

</ej:PivotGrid>

<script type="text/javascript">
        function onLoad(args) {
            args.model.dataSource.data = pivot_dataset; // Datasource
        }
</script>

The following table describes the result of applying some commonly used format strings on numeric values.

Label Value	Label Format property value	Result	Description
1000	n1	1000.0	The Number is rounded to 1 decimal place
1000	n2	1000.00	The Number is rounded to 2 decimal place
1000	n3	1000.000	The Number is rounded to 3 decimal place
0.01	p1	1.0%	The Number is converted to percentage with 1 decimal place
0.01	p2	1.00%	The Number is converted to percentage with 2 decimal place
0.01	p3	1.000%	The Number is converted to percentage with 3 decimal place
1000	c1	$1,000.0	The Currency symbol is appended to number and number is rounded to 1 decimal place
1000	c2	$1,000.00	The Currency symbol is appended to number and number is rounded to 2 decimal place
1.0	d2	01	The number is rounded with 2 whole number place
1.0	d3	001	The number is rounded with 3 whole number place

The following table describes some common results on applying standard format sets to date format using FormatString option.

Format Pattern	Description	Result
d	The day of the month between 1 and 31.	"1"  to "31"
dd	The day of the month with leading zero if required.	"01" to "31"
ddd	Abbreviated day name.	"Mon" to "Sun"
dddd	The full day name	"Monday" to "Sunday"
M	The month of the year between 1 - 12	"1" to "12"
MM	The month of the year with leading zero if required	"01" to "12"
MMM	Abbreviated month name	"Jan" to "Dec"
MMMM	The full month name	"January" to "December"
yy	The year as a two-digit number	"99" or "08"
yyyy	The full four digit year	"1999" or "2008"
MM/dd/yyyy	The Date is displayed in "month/date/year" format	"04/09/1900"
d M, y	The Date is displayed in "date month, year" format	"7 9, 0"
d MM, y	The Date is displayed in "date month, year" format	"7 09, 0"
dddd, d MMMM, yy	The Date is displayed in "day, date month, year" format	"Tuesday, 7 September, 00"
yyyy-MM-dd	The Date is displayed in "year-month-date"(UTC) format	"1900-09-06"