jquery.pivot plugin examples

Description

The jquery.pivot plugin can be used for presenting table data in pivot form. It is made and maintained me, Janus Schmidt, currently employed by Metalogic.dk.

The plugin was originally made for the time tracking site styrpaatiden.dk

The project lives on github.

Examples:

To run an example, click run. The raw table data will be shown. Then click the table and it will be transformed into a pivot table

Example 1 (source is html table)

run

Source:


<script>
    $('#res').pivot({
        source: $('#example1'),
        formatFunc: function (n) { return jQuery.fn.pivot.formatUK(n, 2); },
        onResultCellClicked: function (data) { alert(dumpObj(data, "data")); },
        sortPivotColumnHeaders:false //we want months non sorted to get them in the right order.
    });
</script>
.....
<table id="example1" data-pivot-dataid="An optional sourcetable identifier">
    <tr>
        <th data-pivot-pivot="true">Month </th>
        <th data-pivot-groupbyrank="2">Subject </th>
        <th data-pivot-groupbyrank="1" data-pivot-dataid="An optional id.">Student </th>
        <th data-pivot-result="true">Score </th>
    </tr>
    .....
    

Example 2 (source is html table)

Example with danish number formatting and no fold unfold buttons and every row open

run

Source:


<script>
    $('#res').pivot({
        source: $('#example2'),
        formatFunc: function (n) { return jQuery.fn.pivot.formatDK(n, 2); },
        parseNumFunc: function (n) { return +((typeof n === "string") ? +n.replace('.', '').replace(',', '.') : n); },
        onResultCellClicked: function (data) { alert(dumpObj(data, "data")); },
        bCollapsible : false
    });
</script>
.....
<table id="example2" data-pivot-dataid="An identifier for the table">
    <tr>
        <th data-pivot-groupbyrank="2" data-pivot-datatype="number">companyid </th>
        <th data-pivot-groupbyrank="3" data-pivot-datatype="number">userid </th>
        <th data-pivot-pivot="true" data-pivot-dataid="idforpivot" data-pivot-sortbycol="2">date </th>
        <th>regMinutes </th>
        <th>flexMinutes </th>
        <th>correction </th>
        <th data-pivot-result="true">diffMinutes </th>
    </tr>
    .....
    

Example 3 (source is html table)

Example with 3 groupby columns.

run

Source:


<script>
    $('#res').pivot({
        source: $('#example3'),
        formatFunc: function (n) { return jQuery.fn.pivot.formatDK(n, 2); },
        parseNumFunc: function (n) { return +((typeof n === "string") ? +n.replace('.', '').replace(',', '.') : n); },
        onResultCellClicked: function (data) { alert(dumpObj(data, "data")); }
    });
</script>
....
<table id="example3">
    <tbody>
        <tr>
            <th data-pivot-groupbyrank="1">Ansat </th>
            <th data-pivot-groupbyrank="2">Kunde </th>
            <th data-pivot-groupbyrank="3">Projekt </th>
            <th data-pivot-result="true">sum </th>
            <th>Fakturerbar </th>
            <th>sort </th>
            <th data-pivot-pivot="true" data-pivot-dataid="idforpivot">dato </th>
        </tr>
        .....
    

Example 4 (source is json)

This is the same as example 1 except the source is JSON data.

run

Source:


<script>
    $('#res').pivot({
        source: example4JSONdata,
        formatFunc: function (n) { return jQuery.fn.pivot.formatUK(n, 2); },
        onResultCellClicked: function (data) { alert(dumpObj(data, "data")); },
        sortPivotColumnHeaders:false //we want months non sorted to get them in the right order.
    });
var example4JSONdata = {
    dataid: "An optional sourcetable identifier",
    columns: [
        { colvalue: "Month ", coltext: "Month ", header: "Month ", sortbycol: "Month ", groupbyrank: null, pivot: true, result: false },
        { colvalue: "Subject ", coltext: "Subject ", header: "Subject ", sortbycol: "Subject ", groupbyrank: 2, pivot: false, result: false },
        { colvalue: "Student ", coltext: "Student ", header: "Student ", sortbycol: "Student ", dataid: "An optional id.", groupbyrank: 1, pivot: false, result: false },
        { colvalue: "Score ", coltext: "Score ", header: "Score ", sortbycol: "Score ", groupbyrank: null, pivot: false, result: true}],
    rows: [
        { "Month ": "January", "Subject ": "English", "Student ": "Elisa", "Score ": "8.7" },
        { "Month ": "January ", "Subject ": "Maths ", "Student ": "Elisa ", "Score ": "6.5 " },
        { "Month ": "January ", "Subject ": "Science ", "Student ": "Elisa ", "Score ": "5.8 " },
        .....
        { "Month ": "March ", "Subject ": "History ", "Student ": "Mary ", "Score ": "6.7 " },
        { "Month ": "March ", "Subject ": "French ", "Student ": "Mary ", "Score ": "9.0 "}]
};
</script>
    

Example 5 (source is json)

Example with 2 simultaneous tables from json sources. Same as example 1 and 2 but from json sources.

run

<script>
    $('#res').pivot({
        source: example4JSONdata, //same as example 1
        formatFunc: function (n) { return jQuery.fn.pivot.formatUK(n, 2); },
        onResultCellClicked: function (data) { alert(dumpObj(data, "data")); }
    });
    $('#res2').pivot({
        source: example5JSONdata, //same as example 2
        formatFunc: function (n) { return jQuery.fn.pivot.formatDK(n, 2); },
        parseNumFunc: function (n) { return +((typeof n === "string") ? +n.replace('.', '').replace(',', '.') : n); },
        onResultCellClicked: function (data) { alert(dumpObj(data, "data")); }
    });
var example4JSONdata = {
    dataid: "An optional sourcetable identifier",
    columns: [
        { colvalue: "Month ", coltext: "Month ", header: "Month ", sortbycol: "Month ", groupbyrank: null, pivot: true, result: false },
        { colvalue: "Subject ", coltext: "Subject ", header: "Subject ", sortbycol: "Subject ", groupbyrank: 2, pivot: false, result: false },
        { colvalue: "Student ", coltext: "Student ", header: "Student ", sortbycol: "Student ", dataid: "An optional id.", groupbyrank: 1, pivot: false, result: false },
        { colvalue: "Score ", coltext: "Score ", header: "Score ", sortbycol: "Score ", groupbyrank: null, pivot: false, result: true}],
    rows: [
        { "Month ": "January", "Subject ": "English", "Student ": "Elisa", "Score ": "8.7" },
        { "Month ": "January ", "Subject ": "Maths ", "Student ": "Elisa ", "Score ": "6.5 " },
        { "Month ": "January ", "Subject ": "Science ", "Student ": "Elisa ", "Score ": "5.8 " },
        .....
        { "Month ": "March ", "Subject ": "History ", "Student ": "Mary ", "Score ": "6.7 " },
        { "Month ": "March ", "Subject ": "French ", "Student ": "Mary ", "Score ": "9.0 "}]
};
        
var example5JSONdata = {
        "dataid": "An identifier for the table",
        "columns": [
            { "colvalue": "companyid ", "coltext": "companyid ", "header": "companyid ", "datatype": "number", "sortbycol": "companyid ", "groupbyrank": 2, "pivot": false, "result": false },
            { "colvalue": "userid ", "coltext": "userid ", "header": "userid ", "datatype": "number", "sortbycol": "userid ", "groupbyrank": 3, "pivot": false, "result": false },
            { "colvalue": "date ", "coltext": "date ", "header": "date ", "sortbycol": "date ", "dataid": "idforpivot", "groupbyrank": null, "pivot": true, "result": false },
            { "colvalue": "regMinutes ", "coltext": "regMinutes ", "header": "regMinutes ", "sortbycol": "regMinutes ", "groupbyrank": null, "pivot": false, "result": false },
            { "colvalue": "flexMinutes ", "coltext": "flexMinutes ", "header": "flexMinutes ", "sortbycol": "flexMinutes ", "groupbyrank": null, "pivot": false, "result": false },
            { "colvalue": "correction ", "coltext": "correction ", "header": "correction ", "sortbycol": "correction ", "groupbyrank": null, "pivot": false, "result": false },
            { "colvalue": "diffMinutes ", "coltext": "diffMinutes ", "header": "diffMinutes ", "sortbycol": "diffMinutes ", "groupbyrank": null, "pivot": false, "result": true}],
        "rows": [
            { "companyid ": 2, "userid ": 1, "date ": "02-08-2009 ", "regMinutes ": "0 ", "flexMinutes ": "0 ", "correction ": "0 ", "diffMinutes ": "0 " },
            { "companyid ": 2, "userid ": 1, "date ": "03-08-2009 ", "regMinutes ": "0 ", "flexMinutes ": "330 ", "correction ": "0 ", "diffMinutes ": "-330 " },
            { "companyid ": 2, "userid ": 1, "date ": "01-08-2009 ", "regMinutes ": "0 ", "flexMinutes ": "0 ", "correction ": "0 ", "diffMinutes ": "0 " },
            .....
            { "companyid ": 4, "userid ": 95, "date ": "12-08-2009 ", "regMinutes ": "0 ", "flexMinutes ": "450 ", "correction ": "0 ", "diffMinutes ": "-450 " },
            { "companyid ": 4, "userid ": 121, "date ": "12-08-2009 ", "regMinutes ": "0 ", "flexMinutes ": "360 ", "correction ": "0 ", "diffMinutes ": "-360 "}]
};
</script>
    

Example 6 (source is json)

Example with text data in result column from json sources.

run

<script>
    $('#res').pivot({
        source: example6JSONdata,
        parseNumFunc : null,
        aggregatefunc: function (aggValues) { return aggValues.join(', '); },
        bTotals: false,
        onResultCellClicked: function (data) { alert(dumpObj(data, "data")); }
    });
    var example6JSONdata =  {
       dataid: "Application Information.",
       columns: [
       { colvalue: "password", coltext: "password", header: "password", sortbycol: "password", result: true },
       { colvalue: "username", coltext: "username", header: "username", sortbycol: "username", pivot:true},
       { colvalue: "hostname", coltext: "hostname", header: "hostname", sortbycol: "hostname", groupbyrank: 2},
       { colvalue: "sysid", coltext: "sysid", header: "sysid", sortbycol: "sysid", dataid: "sysid", groupbyrank: 1}],
       rows : [
       { "sysid": "SID1", "hostname": "host01", "username": "user1", "password": "a"},
       { "sysid": "SID1", "hostname": "host01", "username": "user2", "password": "b"},
       { "sysid": "SID1", "hostname": "host51", "username": "user1", "password": "c"},
       { "sysid": "SID1", "hostname": "host52", "username": "user1", "password": "d"},
       { "sysid": "SID1", "hostname": "host52", "username": "user2", "password": "e"},
       { "sysid": "SID1", "hostname": "host52", "username": "user2", "password": "e1" },
       { "sysid": "SID1", "hostname": "host52", "username": "user2", "password": "e2" },
       { "sysid": "SID1", "hostname": "host52", "username": "user2", "password": "e3" },
       { "sysid": "SID1", "hostname": "host54", "username": "user1", "password": "f"},
       { "sysid": "SID2", "hostname": "host55", "username": "user1", "password": "g"},
       { "sysid": "SID2", "hostname": "host55", "username": "user2", "password": "h"},
       { "sysid": "SID2", "hostname": "host02", "username": "user1", "password": "i"},
       { "sysid": "SID2", "hostname": "host02", "username": "user1", "password": "j"},
       { "sysid": "SID2", "hostname": "host53", "username": "user1", "password": "k"},
       { "sysid": "SID2", "hostname": "host53", "username": "user2", "password": "l"},
       { "sysid": "SID2", "hostname": "host53", "username": "user3", "password": "m"}]
   };
</script>
    

Example 7 (performance test)

Performance test. This example takes 100 rows from example5 and repeats them 200 times to get a total of 20.000 rows of data

run

<script>
    $('#res').pivot({
        source: example5JSONdata, //same as example 2
        formatFunc: function (n) { return jQuery.fn.pivot.formatDK(n, 2); },
        parseNumFunc: function (n) { return +((typeof n === "string") ? +n.replace('.', '').replace(',', '.') : n); },
        onResultCellClicked: function (data) { alert(dumpObj(data, "data")); }
    });
var JSONdata = {
        "dataid": "An identifier for the table",
        "columns": [
            { "colvalue": "companyid ", "coltext": "companyid ", "header": "companyid ", "datatype": "number", "sortbycol": "companyid ", "groupbyrank": 2, "pivot": false, "result": false },
            { "colvalue": "userid ", "coltext": "userid ", "header": "userid ", "datatype": "number", "sortbycol": "userid ", "groupbyrank": 3, "pivot": false, "result": false },
            { "colvalue": "date ", "coltext": "date ", "header": "date ", "sortbycol": "date ", "dataid": "idforpivot", "groupbyrank": null, "pivot": true, "result": false },
            { "colvalue": "regMinutes ", "coltext": "regMinutes ", "header": "regMinutes ", "sortbycol": "regMinutes ", "groupbyrank": null, "pivot": false, "result": false },
            { "colvalue": "flexMinutes ", "coltext": "flexMinutes ", "header": "flexMinutes ", "sortbycol": "flexMinutes ", "groupbyrank": null, "pivot": false, "result": false },
            { "colvalue": "correction ", "coltext": "correction ", "header": "correction ", "sortbycol": "correction ", "groupbyrank": null, "pivot": false, "result": false },
            { "colvalue": "diffMinutes ", "coltext": "diffMinutes ", "header": "diffMinutes ", "sortbycol": "diffMinutes ", "groupbyrank": null, "pivot": false, "result": true}],
        "rows": [
            { "companyid ": 2, "userid ": 1, "date ": "02-08-2009 ", "regMinutes ": "0 ", "flexMinutes ": "0 ", "correction ": "0 ", "diffMinutes ": "0 " },
            { "companyid ": 2, "userid ": 1, "date ": "03-08-2009 ", "regMinutes ": "0 ", "flexMinutes ": "330 ", "correction ": "0 ", "diffMinutes ": "-330 " },
            { "companyid ": 2, "userid ": 1, "date ": "01-08-2009 ", "regMinutes ": "0 ", "flexMinutes ": "0 ", "correction ": "0 ", "diffMinutes ": "0 " },
            .....
            { "companyid ": 4, "userid ": 95, "date ": "12-08-2009 ", "regMinutes ": "0 ", "flexMinutes ": "450 ", "correction ": "0 ", "diffMinutes ": "-450 " },
            { "companyid ": 4, "userid ": 121, "date ": "12-08-2009 ", "regMinutes ": "0 ", "flexMinutes ": "360 ", "correction ": "0 ", "diffMinutes ": "-360 "}]
};
</script>
    

Example 8 (source is json)

Same as example 1 exept with 2 result columns.

run

<script>
JSONdata = $.extend(true, {}, example4JSONdata);
JSONdata.columns.push({ colvalue: 'Score2', coltext: 'Score2', header: 'Other score', sortbycol: 'Score2 ', groupbyrank: null, pivot: false, result: true });
$('#res').pivot({
    source: JSONdata, //same as example 1 exept score2 column is also result column
    formatFunc: function (n) { return jQuery.fn.pivot.formatUK(n, 2); },
    onResultCellClicked: function (data) { alert(dumpObj(data, 'data')); }
});
                        
var example4JSONdata = {
    dataid: "An optional sourcetable identifier",
    columns: [
        { colvalue: "Month ", coltext: "Month ", header: "Month ", sortbycol: "Month ", groupbyrank: null, pivot: true, result: false },
        { colvalue: "Subject ", coltext: "Subject ", header: "Subject ", sortbycol: "Subject ", groupbyrank: 2, pivot: false, result: false },
        { colvalue: "Student ", coltext: "Student ", header: "Student ", sortbycol: "Student ", dataid: "An optional id.", groupbyrank: 1, pivot: false, result: false },
        { colvalue: "Score ", coltext: "Score ", header: "Score ", sortbycol: "Score ", groupbyrank: null, pivot: false, result: true}],
    rows: [
        { "Month ": "January", "Subject ": "English", "Student ": "Elisa", "Score ": "8.7" },
        { "Month ": "January ", "Subject ": "Maths ", "Student ": "Elisa ", "Score ": "6.5 " },
        { "Month ": "January ", "Subject ": "Science ", "Student ": "Elisa ", "Score ": "5.8 " },
        .....
        { "Month ": "March ", "Subject ": "History ", "Student ": "Mary ", "Score ": "6.7 " },
        { "Month ": "March ", "Subject ": "French ", "Student ": "Mary ", "Score ": "9.0 "}]
};
</script>