MitrahSoft is an Adobe offcial partner MitrahSoft is Lucee offcial partner & core contributing developer

Creating Pivot Table Report Using ColdFusion And jQuery

In Excel there is a option to make a cell to calculate formulas and display the result like sum, average, etc., into the another cell. We're just thinking that is there any possible to bring the same operation dynamically in table format to display in browser. On that discussion we found the PivotTable.js, which is a JavaScript Pivot Table library with drag'nDrop functionality built on top of jQuery and jQueryUI.

Overview

Using pivot table, we can able to summarize data from the original table. We can also able to calculate count, average, sum or min/max values for numbers which were in a group. Consider original table has salary of each person based on various countries, We can use a pivot table to calculate the average salary in each country (the country is your group) ASAP. BTW Pivot tables are excellent to get your data from the long format into a wide format.

Pivot Tables are one of the most powerful and useful tool. They will save you a lot of time by allowing you to quickly summarize large amounts of data into a meaningful report. Understanding exactly how a pivot table works will really help you unleash the full potential of this amazing tool. This is a good way to quickly see all the values that appear in a field and also find typos, and other inconsistencies

Here we're going to see the basic functionality of how data renders in pivot table. In pivot there are two main functions defined in pivot() and pivotUI(). Please note that pivot() and pivotUI() do not accept the same parameters, and although certain sets of parameters will be parsed similarly by both, this is not generally true. At first clone the pivot js from Nicolas Kruchten Git Repo and link the required JS and CSS files( jQuery, jQueryUI & pivot ).

Demo

In this demo, we're going to see how to render student details from database to pivot table.

index.cfm
student.cfc

In this example, we call remote function to get student details from DB and receive response in JSON format. BTW using that JSON response, the pivot table will get summarized.

Result
There is a two possible ways to display the result via Pivot table.
  • Using pivot()
  • Using pivotUI()
Using Pivot()

If we use "$('#pivotUI').pivot()", the result should be a table format without having any dedicated UI options for drag'n drop and filters.

Pivot Result
Using PivotUI()

If we use "$('#pivotUI').pivotUI()", the result should be looks with dedicated UI options for drag'n drop and filter options

PivotUI Result
Table format and structures

There are some options and filters to display our report in various formats

Pivot Table

Hope now clear about some of the basic things in pivot table. For further refer : PivotTable.js