ServiceNow – Generate Pivot Table from any table in UI Page

Here we are going to convert the Pivot table from the list of Incident list generated based on query present in Script Include.

Follow the Steps to develop Pivot Table in UI Page:

1. Create Script Include to get the records

-> Navigate to System Definitions > Script Includes.
-> Click New to create New Script Include
-> Name: PivotReportValues
-> Client callable: true
-> Script:

var PivotReportValues = Class.create();
PivotReportValues.prototype = Object.extendsObject(AbstractAjaxProcessor, {
//techimonster.com
RecordsIds: function() {
        
var arr =[];
//techimonster.com       
var inc = new GlideRecord('incident'); //Table Name
inc.query();
while(inc.next()){
    var category = inc.category.getDisplayValue();// category
    var state = inc.state.getDisplayValue();// state
    var priority = inc.priority.getDisplayValue();// priority
    var new_arr =[];
    new_arr.push(category);
    new_arr.push(priority);
    new_arr.push(state);
    arr.push(new_arr);
}
        gs.log('TechiMonster Pivot Array '+ arr);
        return JSON.stringify(arr);	
        
    },
    
    
    type: 'PivotReportValues'
});

2. Create Script Include to get the records

-> Navigate to System UI > UI Pages.
-> Name: Pivot Table
-> HTML:

<?xml version="1.0" encoding="utf-8" ?>
<j:jelly trim="false" xmlns:j="jelly:core" xmlns:g="glide" xmlns:j2="null" xmlns:g2="null">
<script src="https://code.jquery.com/jquery-1.11.3.min.js"></script>
      <style>

.content-table{
  border-collapse: collapse;
  margin: 25px 0;
  font-size: 0.9em;
  min-width: 400px;
  border-radius: 5px 5px 0 0;
  overflow: hidden;
  box-shadow: 0 0 20px rgba(0, 0, 0, 0.15)
}
.content-table tr:first-child, .left-col{
  background-color: #009879;
  color: white;
  text-align: left;
  font-weight: bold;
    border-bottom: 1px solid #009879;
}
.content-table thead tr, .left-col{
  background-color: #009879;
  color: white;
  text-align: left;
  font-weight: bold;
    border-bottom: 1px solid #009879;
}
.content-table th, .content-table td{
  padding: 12px 15px;
}
.content-table tbody tr{
  border-bottom: 1px solid #dddddd;
}
.content-table tbody tr:nth-of-type(even){
  background-color: #f3f3f3;
}
.content-table tbody tr:last-of-type{
  border-bottom: 2px solid #009879;
}
  </style>
    
    <h2>Pivot Array</h2>
   <div id="pivotTable"></div>

    <h2>Original Array</h2>
   <div id="orgTable"></div>
 
   

      
</j:jelly>

Client Script:

var ajax = new GlideAjax('PivotReportValues');// Script Include Name
ajax.addParam('sysparm_name', 'RecordsIds'); //script include function
ajax.getXMLWait();
var arr = JSON.parse(ajax.getAnswer());

//SAMPLE OUTPUT FROM SCRIPT INCLUDE

// var arr = [
// ["Product", "Year", "Sales"],
// ["Product 1", "2009", "1212"],
// ["Product 2", "2009", "522"],
// ["Product 1", "2010", "1337"],
// ["Product 2", "2011", "711"],
// ["Product 2", "2012", "2245"],
// ["Product 3", "2012", "1000"]
// ];
//SAMPLE OUTPUT FROM SCRIPT INCLUDE

//techimonster.com
function getPivotArray(dataArray, rowIndex, colIndex, dataIndex) {
    var result = {},
        ret = [];
    var newCols = [];
    for (var i = 0; i < dataArray.length; i++) {

        if (!result[dataArray[i][rowIndex]]) {
            result[dataArray[i][rowIndex]] = {};
        }
        result[dataArray[i][rowIndex]][dataArray[i][colIndex]] = dataArray[i][dataIndex];

        //To get column names
        if (newCols.indexOf(dataArray[i][colIndex]) == -1) {
            newCols.push(dataArray[i][colIndex]);
        }
    }

    newCols.sort();
    var item = [];

    //Add Header Row
    item.push('Item');
    item.push.apply(item, newCols);
    ret.push(item);

    //Add content 
    for (var key in result) {
        item = [];
        item.push(key);
        for (var i = 0; i < newCols.length; i++) {
            item.push(result[key][newCols[i]] || "-");
        }
        ret.push(item);
    }
    return ret;

}


var output = getPivotArray(arr, 0, 1, 2);

function arrayToHTMLTable(myArray) {
    var result = "<table class='content-table'>";
    for (var i = 0; i < myArray.length; i++) {
        result += "<tr>";
        for (var j = 0; j < myArray[i].length; j++) {
            result += "<td>" + myArray[i][j] + "</td>";
        }
        result += "</tr>";
    }
    result += "</table>";
    return result;
}

//techimonster.com
$(function() {
    var output = getPivotArray(arr, 0, 1, 2);
    $('#orgTable').html(arrayToHTMLTable(arr));
    $('#pivotTable').html(arrayToHTMLTable(output));
});

3. UI Page Output:

UI Page – Pivot table for other tables:

In Script Include modify the table name and query then push the 3 values into the array so that we can generate Pivot Table from the other tables.