Reports and Custom Charts

[bhenson]

Good morning all,

Still learning ServiceNow and making my first foray into Custom Charts.   I have 2 reports that separately display the data I want to combine into 1 custom chart.   Is there any way to use those 2 reports as my Data Generators or find the code that renders those reports to copy into my Data Generators when I create them?

Struggling with getting the code right to generate the data and hoping for a way to leverage what I have already created in SN to create something new and wonderful.   🙂

Thanks in advance for your assistance.

[bhenson]

sangun,

Sorry for the late reply - have not been out in the community much.

Here are my scripts for a 3-line comparison chart.

Render Script:

//
// Script will generate a chart of incidents opened in the last 30 days
// vs. number of incidents opened 1 year ago.
//

// Get the sys_id values for the sys_report_summary table entries build by our generators
  var openID = summary_sets.get("Num Incidents per day - one year ago");
  var closedID = summary_sets.get("Num Incidents per day - last 30 days");
  var midID = summary_sets.get("Num Incidents per day - two years ago");
//   var backLogID = summary_sets.get("Incident Backlog");

  // Gets a utility class for dataset manipulation
  var cu = new ChartUtil();
  cu.setTable('incident');
  cu.setColors("#FFDEAD,#7FFF00");
  ////cu.setColumn('Sequence') ;
 
  // Get the dataset for the one year ago incidents
  var open = new ChartGenerator("line");
  open.setTable('incident');
  open.setSummaryID(openID);
  var ds = open.getDataset();

  var chart = open.getChart(ds);

// Get the dataset for the current 30 days incidents and combine with the one year ago incidents into
  // a multi series dataset
  var closed = new ChartGenerator("line");
  closed.setTable('incident');
  closed.setSummaryID(closedID);
  closedDS = cu.addEmptyValues(closed.getDataset(), ds);
  ds = cu.mergeDatasets(ds, "One Year Ago", closed.getDataset(), "Past 30 Days");
  ds = cu.changeDatasetLabel(ds, "Number of Incidents");

  // Get the chart for the multi series bar chart of open and closed incidents
  var chart = closed.getChart(ds);

var plot = chart.getPlot();

gs.log("plot = "+ plot);

axisD = plot.getDomainAxis();
axisD.setLabel("Date");
axisV = plot.getRangeAxis();
axisV.setLabel("Number of Incidents");

gs.log("axisD = " + axisD);
gs.log("axisV = " + axisV);

// Change the spacing
  closed.setNoMargins();

// Add a bar renderer to the chart for past 30 days incidents
////   var closed = new ChartGenerator("line");
////   closed.setSummaryID(closedID);
////   var closedDS = cu.addEmptyValues(closed.getDataset(), ds);
////   closedDS = cu.changeDatasetLabel(closedDS, "Past 30 days");
////   var renderer = closed.getChartGenerator().getLineRenderer(Packages.java.awt.Color.GREEN, 3.0, false, true, true);
////   open.addRenderer(renderer, closedDS, true, "Past 30 days");

// Adds a line renderer to the chart to show the incidents 2 years ago
var line = new ChartGenerator("line");
line.setSummaryID(midID);

// plot2 = line.getPlot();

var lineDS = cu.addEmptyValues(line.getDataset(), ds);
lineDS = cu.changeDatasetLabel(lineDS, "Two Years Ago");
var renderer = line.getChartGenerator().getLineRenderer(Packages.java.awt.Color.lightGray, 3.0, false, true, true);
closed.addRenderer(renderer, lineDS, true, "Two Years Ago");

axisV2 = plot.getRangeAxis(1);

gs.log("axisV2 = " + axisV2);
axisV2 = plot.setRangeAxis(1,axisV);

// return our chart
  answer = chart;

Data Generator #1

//To make my job easier, I put all the key components at the top as variables.   So I can copy this quickly and just change what I need.  

var table = "incident";  

var field = "sys_created_on"; // Date Field  

var trend = "date";  

var query = "sys_created_onRELATIVEGE@dayofweek@ago@760^sys_created_onRELATIVELT@dayofweek@ago@730^EQ^TRENDBYsys_...";  

var g_sequence = 0;   // This will give me the value for the Sequence field in the Summary Set Line.  

var id = createSummary(); // This will create the Summary Set record.  

current.summary = id;  

queryForSetLines(); // This will fill in the Summary Set record with individual lines  

current.setWorkflow(false);  

current.update();  

// Here is the script that you will need to tailor for your needs.  

// This one is pretty simple, I am just taking a query for last year's change records per month and getting the total number.  

function queryForSetLines() {  

    var table1 = new GlideAggregate(table);  

    table1.addTrend(field, trend);  

    table1.addAggregate('MIN', field);  

    table1.addAggregate('COUNT');  

    table1.addQuery(query);  

    table1.setGroup(false);  

    table1.query();  

    // Add Lines to Summary Set from iterating through the GlideAggregate query  

    while (table1.next()) {  

  months = table1.getAggregate('MIN', field);  

  days = table1.getAggregate('MIN',field);

  counts = table1.getAggregate('COUNT');  

  dateParts = days.split('-');

  month = dateParts[1];

  day = dateParts[2].substring(0,2);

  monthday = month+day;

// Since the date stamp is in yyyy-MM-dd 00:00:00 format, I have to parse it out, I only want the Month.  

  var sStamp = months.split(" ");  

  var sDate = sStamp[0].split("-");  

  months = sDate[1];  

  gs.log(months+': ' + counts); // When you are done testing, comment this out. [I forgot on my code]  

  createSummaryLine(monthday, counts); // In the end we take the X axis (months) with the Y axes (counts)  

    }  

}  

// Nothing should need edited except for the expiration date (and even that may not be necessary to edit)  

// This creates the actual record for the summary set, filling in all the required criteria.    

function createSummary() {  

      var expires = new Date();  

      expires.setDate(expires.getDate() + 7); // This will expire 7 days from now  

      var expDate = (expires.getMonth()+1)+'/'+expires.getDate()+'/'+expires.getFullYear()+' '+expires.getHours()+':'+expires.getMinutes()+':'+expires.getSeconds();  

      var s = new GlideRecord("sys_report_summary");  

      s.title = current.name;  

      s.field = field;  

      s.table = table;  

      s.expires = expDate;  

      return s.insert();  

}  

// Nothing should need edited below, this just creates the individual Summary Set lines  

function createSummaryLine(key, value) {  

      var xx = new String(value);  

      if (xx.startsWith("error"))  

            return;  

      xx = parseInt(value);  

      if (isNaN(xx))  

            return;  

      var s = new GlideRecord("sys_report_summary_line");  

      s.summary = id;  

      s.sequence = g_sequence++;  

      s.value = value;  

      s.category = key;  

      s.name = key;  

      s.insert();  

}

Data Generator #2

//To make my job easier, I put all the key components at the top as variables.   So I can copy this quickly and just change what I need.  
 
var table = "incident";  
var field = "sys_created_on"; // Date Field  
var trend = "date";  
var query = "sys_created_onONLast 30 days@javascript:gs.daysAgoStart(30)@javascript:gs.daysAgoEnd(0)^EQ^TRENDBYsys_created_on,date";  
 
var g_sequence = 0;   // This will give me the value for the Sequence field in the Summary Set Line.  
var id = createSummary(); // This will create the Summary Set record.  
current.summary = id;  
queryForSetLines(); // This will fill in the Summary Set record with individual lines  
current.setWorkflow(false);  
current.update();  
 
// Here is the script that you will need to tailor for your needs.  
// This one is pretty simple, I am just taking a query for last year's change records per month and getting the total number.  
function queryForSetLines() {  
    var table1 = new GlideAggregate(table);  
    table1.addTrend(field, trend);  
    table1.addAggregate('MIN', field);  
    table1.addAggregate('COUNT');  
    table1.addQuery(query);  
    table1.setGroup(false);  
    table1.query();  
     
    // Add Lines to Summary Set from iterating through the GlideAggregate query  
    while (table1.next()) {  
  months = table1.getAggregate('MIN', field);  
  days = table1.getAggregate('MIN',field);
  counts = table1.getAggregate('COUNT');  

  dateParts = days.split('-');
  month = dateParts[1];
  day = dateParts[2].substring(0,2);
   
  monthday = month+day;
   
// Since the date stamp is in yyyy-MM-dd 00:00:00 format, I have to parse it out, I only want the Month.  
  var sStamp = months.split(" ");  
  var sDate = sStamp[0].split("-");  
  months = sDate[1];  
   
  gs.log(months+': ' + counts); // When you are done testing, comment this out. [I forgot on my code]  
  createSummaryLine(monthday, counts); // In the end we take the X axis (months) with the Y axes (counts)  
    }  
}  
 
// Nothing should need edited except for the expiration date (and even that may not be necessary to edit)  
// This creates the actual record for the summary set, filling in all the required criteria.    
function createSummary() {  
      var expires = new Date();  
      expires.setDate(expires.getDate() + 7); // This will expire 7 days from now  
      var expDate = (expires.getMonth()+1)+'/'+expires.getDate()+'/'+expires.getFullYear()+' '+expires.getHours()+':'+expires.getMinutes()+':'+expires.getSeconds();  
         
      var s = new GlideRecord("sys_report_summary");  
      s.title = current.name;  
      s.field = field;  
      s.table = table;  
      s.expires = expDate;  
      return s.insert();  
}  
 
// Nothing should need edited below, this just creates the individual Summary Set lines  
function createSummaryLine(key, value) {  
      var xx = new String(value);  
      if (xx.startsWith("error"))  
            return;  
       
      xx = parseInt(value);  
      if (isNaN(xx))  
            return;  
 
      var s = new GlideRecord("sys_report_summary_line");  
      s.summary = id;  
      s.sequence = g_sequence++;  
      s.value = value;  
      s.category = key;  
      s.name = key;  
      s.insert();  
}  

Data Generator 3

//To make my job easier, I put all the key components at the top as variables.   So I can copy this quickly and just change what I need.  

var table = "incident";  

var field = "sys_created_on"; // Date Field  

var trend = "date";  

var query = "sys_created_onRELATIVEGE@dayofweek@ago@395^sys_created_onRELATIVELT@dayofweek@ago@365^EQ^TRENDBYsys_...";  

var g_sequence = 0;   // This will give me the value for the Sequence field in the Summary Set Line.  

var id = createSummary(); // This will create the Summary Set record.  

current.summary = id;  

queryForSetLines(); // This will fill in the Summary Set record with individual lines  

current.setWorkflow(false);  

current.update();  

// Here is the script that you will need to tailor for your needs.  

// This one is pretty simple, I am just taking a query for last year's change records per month and getting the total number.  

function queryForSetLines() {  

    var table1 = new GlideAggregate(table);  

    table1.addTrend(field, trend);  

    table1.addAggregate('MIN', field);  

    table1.addAggregate('COUNT');  

    table1.addQuery(query);  

    table1.setGroup(false);  

    table1.query();  

    // Add Lines to Summary Set from iterating through the GlideAggregate query  

    while (table1.next()) {  

  months = table1.getAggregate('MIN', field);  

  days = table1.getAggregate('MIN',field);

  counts = table1.getAggregate('COUNT');  

  dateParts = days.split('-');

  month = dateParts[1];

  day = dateParts[2].substring(0,2);

  monthday = month+day;

// Since the date stamp is in yyyy-MM-dd 00:00:00 format, I have to parse it out, I only want the Month.  

  var sStamp = months.split(" ");  

  var sDate = sStamp[0].split("-");  

  months = sDate[1];  

  gs.log(months+': ' + counts); // When you are done testing, comment this out. [I forgot on my code]  

  createSummaryLine(monthday, counts); // In the end we take the X axis (months) with the Y axes (counts)  

    }  

}  

// Nothing should need edited except for the expiration date (and even that may not be necessary to edit)  

// This creates the actual record for the summary set, filling in all the required criteria.    

function createSummary() {  

      var expires = new Date();  

      expires.setDate(expires.getDate() + 7); // This will expire 7 days from now  

      var expDate = (expires.getMonth()+1)+'/'+expires.getDate()+'/'+expires.getFullYear()+' '+expires.getHours()+':'+expires.getMinutes()+':'+expires.getSeconds();  

      var s = new GlideRecord("sys_report_summary");  

      s.title = current.name;  

      s.field = field;  

      s.table = table;  

      s.expires = expDate;  

      return s.insert();  

}  

// Nothing should need edited below, this just creates the individual Summary Set lines  

function createSummaryLine(key, value) {  

      var xx = new String(value);  

      if (xx.startsWith("error"))  

            return;  

      xx = parseInt(value);  

      if (isNaN(xx))  

            return;  

      var s = new GlideRecord("sys_report_summary_line");  

      s.summary = id;  

      s.sequence = g_sequence++;  

      s.value = value;  

      s.category = key;  

      s.name = key;  

      s.insert();  

}

Hope that helps if you are still needing this functionality.