Well, it seems that pivot tables are just a little bit popular in community and while sometimes native pivot tables will suffice, there are times when custom is the only route to take. Sadly, these pivot table questions have all too often gone unanswered or answered with the dreaded "You can't do that out of box". Well no more. The technique covered in Code Creative Episode 3 can be used to build nearly any pivot table imaginable! Here is just a sample of the questions I could find that this might benefit:

"I was wondering how you active the 'grid' section to show the count and percentage in text below the bars?"

- David Hreben (Code Creative - Episode 1 - How to Build Custom Charts and Reports)

"We can create "Pivot Table" using HighCharts API? I went through the HighCharts plotOptions. Didn't find anything there."

- Probir Das

"A requirement came up to create a report very similar to a pivot table, however, with multiple columns for the rows field."

- Raul Ron (Pivot table with multiple "row" columns)

"Has anyone create report of Incident Category by Month in a Pivot Table format such as followed?"

- Alex Ng (Pivot table - Incident Category by Month report)

"Has anyone created report for number of created incidents per hour in a Pivot Table using custom charts format such as followed?:"

- vaigai.kothandaraman Last (Custom Chart Scripts)

"This works perfect for presenting the results, just what I need, except the pivot report totals the rows and columns, which I don't particularly care for, since the metrics might be amount of users and another mailbox space used, these two values don't really mean anything added up."

- William Sun (Can you remove the totals from the pivot table reports?)

"Does anyone know how to a pivot table report with more than one column within ServiceNow? (see example attached).

All of the records and information needed comes from the same source table."

- April Edwards (Advanced Reporting (Pivot Table) Question)

And here's the code:

UI Page

<?xml version="1.0" encoding="utf-8" ?>

      g2:evaluate

              var openQuery = 'stateIN1,2,3,4,5',

                      resolvedQuery = 'state=6resolved_atONToday@javascript:gs.daysAgoStart(0)@javascript:gs.daysAgoEnd(0)',

                      closedQuery = 'state=7closed_atONToday@javascript:gs.daysAgoStart(0)@javascript:gs.daysAgoEnd(0)',

                      allQuery = 'stateIN1,2,3,4,5NQstate=6resolved_atONToday@javascript:gs.daysAgoStart(0)@javascript:gs.daysAgoEnd(0)NQstate=7closed_atONToday@javascript:gs.daysAgoStart(0)@javascript:gs.daysAgoEnd(0)',

                      ga,

                      categories = [],

                      series = [];

              // Setup Categories

              var ga = new GlideAggregate('incident');

              ga.addEncodedQuery(allQuery);

              ga.addAggregate('COUNT');

              ga.groupBy('location');

              ga.orderBy('location');

              ga.query();

              while (ga.next()) {

                      categories.push(ga.location.getDisplayValue() + '' || '(empty)');

              }

              // Reusable function for building the 3 Series

              function getSeries(name, index, query, categories) {

                      var ga = new GlideAggregate('incident'),

                              data = [],

                              i,

                              cat;

                      // Fill data with 0's

                      for (i = 0; i != categories.length; i++) {

                              data.push(0);

                      }

                      ga.addEncodedQuery(query);

                      ga.addAggregate('COUNT');

                      ga.groupBy('location');

                      ga.orderBy('location');

                      ga.query();

                      while (ga.next()) {

                              // Find category index

                              for (i = 0; i != categories.length; i++) {

                                      cat = ga.location.getDisplayValue() + '' || '(empty)';

                                      if (categories[i] == cat) {

                                              break;

                                      }

                              }

                              data[i] = ga.getAggregate('COUNT') * 1;

                      }

                      return { 'name': name, 'legendIndex': index, 'data': data };

              }

              // Add the 3 series to an array for output

              series.push(getSeries('Opened Currently', 0, openQuery, categories));   // Add Open Series

              series.push(getSeries('Resolved Today', 1, resolvedQuery, categories)); // Add Resolved Series

              series.push(getSeries('Closed Today', 1, closedQuery, categories)); // Add Closed Series

      /g2:evaluate

  </p> <p>              .my-table {</p> <p>                      color: #485563;</p> <p>              }</p> <p>  caption.my-tables {</p> <p>                      font-weight: bold;</p> <p>                      font-size: 2em;</p> <p>                      margin-bottom: .75em;</p> <p>              }</p> <p>              th.my-table-h {</p> <p>                      text-align: center;</p> <p>                      width: 10em;</p> <p>                      padding-bottom: 1em;</p> <p>              }</p> <p>              td.my-table-td {</p> <p>                      text-align: center;</p> <p>                      font-size: 1.25em;</p> <p>                      padding: .5em .5em;</p> <p>              }</p> <p>              td.my-table-td:hover {</p> <p>                      font-size: 1.75em;</p> <p>              }</p> <p>              td.my-row-h {</p> <p>                      font-weight: bold;</p> <p>                      text-align:right;</p> <p>              }</p> <p>              tr.my-row {</p> <p>                      border-radius: .5em;</p> <p>              }</p> <p>              tr.my-row:hover {</p> <p>                      background-color: #f3f3f3;</p> <p>              }</p> <p> 

     

             

 

 

 

 

  /j2:forEach

 

 

 

 

  var curSeriesName = jelly.jvar_series.name;

  var curSeriesData = jelly.jvar_series.data;

  /g2:evaluate

 

 

 

  /j2:forEach

 

  /j2:forEach

 

Incident Status Update

	$[jvar_cat]
$[curSeriesName]	$[jvar_val]

/j:jelly