The latest Capterra’s Tech Trends Survey names data management as one of the main challenges finance businesses face when adopting new software solutions. Ready-made tools may not be flexible enough to meet specific requirements. Therefore, it is reasonable for organizations to consider investing in custom solutions like pivot grids to cover their data management and analysis needs.
In this article, we’ll consider one of our new integration demos, comprising DHTMLX Grid and Pivot. Here we will highlight some technical tricks used by our dev team for creating a JavaScript pivot grid with these widgets.
Example of a JavaScript Pivot Grid Made with DHTMLX Grid and Pivot
DHTMLX Grid is a popular JavaScript datagrid widget for handling tabular data, and its latest version (9.1) brings in great new PRO features such as row expander and multi-sorting. But the Grid widget can become even more powerful by combining it with DHTMLX Pivot. Using the live sample below, you can conveniently switch between Grid and Pivot modes, apply different aggregations, and analyze data dynamically.
Fully overhauled last year, our JavaScript Pivot table enhances data analysis capabilities by enabling dynamic data aggregations for comparing and analyzing complex data. This integration allows end-users to seamlessly switch between raw tabular data in the Grid view and the Pivot view, where the information can be easily organized into meaningful summaries required for effective decision-making.
Key Points to Consider When Integrating DHTMLX Grid with Pivot
Now, let us highlight the main aspects of integrating these DHTMLX widgets within a single pivot grid solution. Leaving aside the details on common things like including DHTMLX components in your project and initializing them, we’ll focus on more challenging tasks.
Switching between Grid and Pivot Modes
Switching between these modes involves dynamically initializing and destroying instances of corresponding widgets while preserving configurations. The convertAndInit(state, save) function is responsible for this transition.
When switching to Pivot mode (state === 1), the Grid is destroyed, and the Pivot table is initialized with stored or default settings.
pivotWidget = new pivot.Pivot("#grid-pivot", {
fields: storedPivotFields || [],
config: storedPivotConfig || {},
data: dataset,
});
When switching back to Grid mode (state === 0), the Pivot is destroyed, and Grid reappears using previously saved settings:
grid = new dhx.Grid("grid-pivot", storedGridConfig);
This approach prevents memory leaks and avoids having multiple instances of the same component running at the same time.
Additionally, the displayButtons function ensures that the appropriate UI buttons are shown for each mode:
document.getElementById("edit-button").style.display = state === 1 ? "none" : "block";
document.getElementById("aux-buttons").style.display = state === 1 ? "flex" : "none";
}
Handling Data Types Dynamically
DHTMLX Pivot requires properly defined data for fields. Without this, numeric columns might be treated as text, affecting sorting and aggregations. The use of the dataTypes object helps automatically detect column types based on the first row of Grid data:
const firstItem = grid.data.getItem(grid.data.getId(0));
for (const c in firstItem) {
dataTypes[c] = typeof(firstItem[c]) === "string" ? "text" : typeof(firstItem[c]) === "number" ? "number" : "date";
}
Then, the detected data types are used to generate Pivot fields dynamically:
id: col.id,
label: col.header[0].text,
type: dataTypes[col.id]
}));
This ensures that the Pivot component correctly processes numeric and text-based data without manual configuration.
Avoiding an Empty Pivot Table Issue
A Pivot table with no defined rows, columns, or values appears blank. To avoid an unwanted empty UI and enable end-users to interact with Grid data in Pivot mode, you insert a temporary Grid inside the Pivot container using the replaceInnerGrid(pivotWidget) function:
const grid = pivot.container.getElementsByClassName("wx-grid");
if (grid.length) {
grid[0].innerText = "";
innerGrid = new dhx.Grid(grid[0], defaultGridConfig);
}
}
The solution improves user experience by displaying meaningful data rather than a blank screen.
Preserving Settings and Maintaining Grid Consistency When Switching from Pivot
The rich functional capabilities of the Pivot component enable end-users to rearrange data and perform complex calculations to gain valuable insights. Therefore, it is important to preserve Pivot settings before switching to Grid mode:
const pivotConfig = pivotWidget.api.getState().config;
The code does this by extracting Pivot’s final state and applying it to the Grid. If no configuration changes were made in Pivot mode, you restore the original Grid configuration:
storedGridConfig = defaultGridConfig;
storedPivotConfig = null;
Since Pivot and Grid handle column widths differently, switching back to Grid may cause width inconsistencies. You have to ensure that each column has a predefined width:
c.id = c.id.toString();
c.width = c.width ? c.width : 150;
return c;
}),
Without this step, the Grid layout might break or appear misaligned after switching from Pivot mode.
All in all, these measures help ensure a consistent and user-friendly experience when toggling between Grid and Pivot.
Taking into consideration the points highlighted in this blog post, you can create a basic JavaScript pivot grid with DHTMLX Grid and Pivot components like in our sample. Using the well-documented APIs of these products, you can significantly extend their functionalities to meet various project requirements.
Wrapping Up
DHTMLX Grid and Pivot components can be a great addition to any data-intensive app with high demands on processing and analyzing large datasets. Just like all DHTMLX products, these components are highly compatible and can be easily integrated into one pivot grid solution in your web projects based on popular front-end frameworks. Download free 30-day trial versions of Grid and Pivot components and test the capabilities of these products in your scenarios.