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
<body>
<cfoutput>
<h2 class="text-center">Pivot Table</h2>
<div id="pivotUI"></div>
<!--- <img src="http://localhost:8501/cf067/EXERCISE/common/status/pivot/animation.gif"> --->
</cfoutput>
</body>
<script>
$(document).ready(function() {
$.ajax({
url : 'student.cfc?method=getStudentRecords',
success:function(data) {
var studentRecord = JSON.parse(data);
$('#pivotUI').pivotUI(
studentRecord, //Here we set the parsed student record into table
{
rows: ["Age"], //Table's row assigned to age by default
cols: ["Department"] //Table's column assigned to department by default
}
);
}
});
});
</script>student.cfc
<cfcomponent >
<cffunction name="getStudentRecords" returntype="Array" access="remote" returnformat="JSON">
<cfset returnData = ArrayNew(1)>
<cfset ticketStruct = {}>
<cfquery datasource="pivot_student" name="getStudentRecord">
SELECT * FROM students
</cfquery>
<cfloop query="getStudentRecord">
<cfset ticketStruct = { "RegID":getStudentRecord.regID, "Name":getStudentRecord.name,
"Age":getStudentRecord.age, "Location":getStudentRecord.location,
"Department":getStudentRecord.department, "BloodGroup":getStudentRecord.bloodGroup }>
<cfset arrayAppend( returnData,ticketStruct )>
</cfloop>
<cfreturn returnData>
</cffunction>
</cfcomponent>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.
Using PivotUI()
If we use "$('#pivotUI').pivotUI()", the result should be looks with dedicated UI options for drag'n drop and filter options.
Table format and structures
There are some options and filters to display our report in various formats
Hope now clear about some of the basic things in pivot table. For further refer : PivotTable.js