Skip to main content

DHTMLX Pivot. External filter example

Demo of filtering DHTMLX Pivot rows using an external search input. Type into the search field to dynamically filter the pivot table across all visible columns using the table API's exec("filter-rows") command.

Live example​

const widget = new pivot.Pivot("#pivot", {
    fields,
    data: dataset,
    config: {
        rows: [
            "state",
            "product"
        ],
        columns: [
            "product_line",
            "product_type"
        ],
        values: [
            {
                field: "profit",
                method: "var"
            },
            {
                field: "sales",
                method: "sum"
            },
            {
                field: "date",
                method: "min"
            }
        ]
    }
});

const input = document.getElementById("search");
const table = widget.api.getTable();

// clear "Search" input value on pivot config update
widget.api.on("update-config", () => {
    table.exec("filter-rows", {filter: null});
    input.value = "";
});

input.addEventListener("input", event => {
    const value = event.target.value.toString().toLowerCase();

    // get filter handler by all columns
    const filter = item => 
        Object.values(item).some(itemValue =>
            itemValue.toString().toLowerCase().includes(value)
        );
    
    // filter table
    if(table)    
        table.exec("filter-rows", {filter});
});
<!-- auxiliary controls for interacting with the sample -->
<link rel="stylesheet" href="https://snippet.dhtmlx.com/codebase/assets/css/auxiliary_controls.css">
<section class="dhx_sample-controls">
    <label for="search" class="dhx_sample-label">Search:</label>
	<input id="search" class="dhx_sample-input" style="width: 200px">
</section>

<!-- component container -->
<div id="pivot" style="width: 100%; height: calc(100% - 60px);"></div>

<!-- dataset -->

<script>
const dataset = [
      {
        "cogs": 51,
        "date": "10/1/2018",
        "inventory_margin": 503,
        "margin": 71,
        "market_size": "Major Market",
        "market": "Central",
        "marketing": 46,
        "product_line": "Leaves",
        "product_type": "Herbal Tea",
        "product": "Lemon",
        "profit": -5,
        "sales": 122,
        "state": "Colorado",
        "expenses": 76,
        "type": "Decaf"
      },
      {
        "cogs": 52,
        "date": "10/1/2018",
        "inventory_margin": 405,
        "margin": 71,
        "market_size": "Major Market",
        "market": "Central",
        "marketing": 17,
        "product_line": "Leaves",
        "product_type": "Herbal Tea",
        "product": "Mint",
        "profit": 26,
        "sales": 123,
        "state": "Colorado",
        "expenses": 45,
        "type": "Decaf"
      },
      // ... 1060 more items (see Live Editor for full data)
    ];
    const fields = [
      {
        "id": "cogs",
        "label": "Cogs",
        "type": "number"
      },
      {
        "id": "date",
        "label": "Date",
        "type": "date"
      },
      {
        "id": "inventory_margin",
        "label": "Inventory Margin",
        "type": "number"
      },
      {
        "id": "margin",
        "label": "Margin",
        "type": "number"
      },
      {
        "id": "market_size",
        "label": "Market Size",
        "type": "text"
      },
      {
        "id": "market",
        "label": "Market",
        "type": "text"
      },
      {
        "id": "marketing",
        "label": "Marketing",
        "type": "number"
      },
      {
        "id": "product_line",
        "label": "Product Line",
        "type": "text"
      },
      {
        "id": "product_type",
        "label": "Product Type",
        "type": "text"
      },
      {
        "id": "product",
        "label": "Product",
        "type": "text"
      },
      {
        "id": "profit",
        "label": "Profit",
        "type": "number"
      },
      {
        "id": "sales",
        "label": "Sales",
        "type": "number"
      },
      {
        "id": "state",
        "label": "State",
        "type": "text"
      },
      {
        "id": "expenses",
        "label": "Expenses",
        "type": "number"
      },
      {
        "id": "type",
        "label": "Type",
        "type": "text"
      }
    ];
</script>

Pivot tables can contain hundreds or thousands of aggregated rows, making it difficult for users to locate specific data points. Built-in configuration panel filters work on source fields, but sometimes you need a quick text search across the rendered table output - similar to a browser's "find on page" but integrated with the pivot's data layer.

DHTMLX Pivot exposes a table API through widget.api.getTable() that provides direct control over the rendered grid. The exec("filter-rows") command accepts a filter function that receives each row item and returns a boolean, allowing you to implement custom filtering logic that works across all visible columns simultaneously.

This example adds a search input above the pivot. As the user types, a filter function checks every cell value in each row against the search text. When the pivot configuration changes (fields added or removed), the filter resets automatically via the update-config event to avoid stale results.

Solution overview​

To implement external filtering, get the table API via widget.api.getTable(), then call table.exec("filter-rows", {filter}) with a function that matches row values against the search input. Pass {filter: null} to clear the filter.

Key points​

  • Table API access: Use widget.api.getTable() to get the internal grid API, which provides the exec method for direct table manipulation including row filtering
  • Custom filter function: The filter-rows command accepts a function that receives each row object and returns true/false, enabling flexible matching logic across any combination of columns
  • Case-insensitive search: Convert both the search value and cell values to lowercase with .toLowerCase() before comparison to ensure the filter works regardless of text casing
  • Auto-reset on config change: Listen to the update-config event to clear the filter and reset the input when the user modifies pivot rows, columns, or values through the configuration panel

API reference​

Additional resources​