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
-> 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
-> 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.