Skip to main content

DHTMLX Pivot. Defining fields formats example

Demo of field-level formatting in DHTMLX Pivot. Configure currency prefixes, decimal precision for numeric fields, and custom date patterns directly on field definitions for professional data presentation.

Live example​

const url = "https://snippet.dhtmlx.com/codebase/data/pivot/04/dataset.json";

fetch(url).then(res => res.json()).then(data => {
    const { dataset, fields } = prepareData(data);
   
    const currencyFields = ["marketing", "profit", "sales"];
    
    fields.forEach(field => {
        // set currency formatting by "$" prefix 
        if (currencyFields.indexOf(field.id) > -1) {
            field.format = {
                prefix: "$",
                minimumFractionDigits: 2,
                maximumFractionDigits: 2
            };
        } else if(field.type === "date"){
            // date formatting
            field.format = "%M %d, %Y";
        }
             
    });

    new pivot.Pivot("#pivot", {
        data: dataset,
        config: {
            rows: ["state", "product_type"],
            columns: [],
            values: [
                {
                    field: "profit",
                    method: "sum"
                },
                {
                    field: "sales",
                    method: "sum"
                },
                {
                    field: "marketing",
                    method: "sum"
                },
                {
                    field: "date",
                    method: "min"
                },
                {
                    field: "cogs",
                    method: "sum"
                },
            ],
        },
        fields
    });
})
<!-- component container -->
<div style="width: 100%; height: 100%" id="pivot"></div>

<script>
// convert string to Date for "date" fields
    function prepareData(data) {
        const {data: dataset, fields} = data;
        const dateFields = fields.filter(f => f.type == "date");
        dataset.forEach(item => {
            dateFields.forEach(f => {
                const v = item[f.id];
                if (typeof v == "string") item[f.id] = new Date(v);
            })
        });
        return {dataset, fields}
    }
</script>

Pivot tables often display financial data alongside dates and general numbers. Without formatting, profit values appear as raw numbers like "353" instead of "$353.00", and dates show in a default system format rather than a business-friendly pattern. Mixing unformatted fields makes the table harder to read and less professional.

DHTMLX Pivot supports field-level formatting through the format property on each field definition. For numeric fields, you can set a prefix (like "$"), minimumFractionDigits, and maximumFractionDigits to control decimal precision. For date fields, the format property accepts a date pattern string using directives like %M (full month name), %d (day), and %Y (4-digit year). This approach applies formatting consistently across all cells that use the field.

This example demonstrates formatting on a coffee sales dataset loaded from a remote JSON endpoint. The marketing, profit, and sales fields receive currency formatting with a "$" prefix and two decimal places. The date field uses the pattern "%M %d, %Y" to display dates as "October 01, 2018". Rows group by state and product type, with no column dimensions, showing Profit Sum, Sales Sum, Marketing Sum, Date Min, and Cogs Sum as values.

Solution overview​

To define field formats, iterate over the fields array after loading data and set the format property on each field. Use an object with prefix, minimumFractionDigits, and maximumFractionDigits for currency fields, or a date pattern string for date fields.

Key points​

  • Currency prefix and precision: Set field.format = { prefix: "$", minimumFractionDigits: 2, maximumFractionDigits: 2 } to display values like "$353.00" with consistent decimal places
  • Date pattern strings: Use field.format = "%M %d, %Y" on date-type fields to control how dates appear in cells, using the same directives as the locale dateFormat
  • Applied per field: Formatting is set on the field definition itself, so it applies everywhere that field appears - in values, filters, and the configuration panel
  • Remote data compatible: Field formats can be applied after fetching data from a remote endpoint, giving you full control over presentation regardless of the data source

API reference​

  • Pivot fields: Field definitions including the format property for number and date formatting.
  • Pivot configuration: Properties for defining rows, columns, and value aggregations.
  • Pivot data: Configuration for providing the dataset to the Pivot component.

Additional resources​