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.

[#2]

Hi,

I have an requirement for chart labels to show Up_45 or down_45. I see your chart does that. Could I ask you how you achieved that? Thanks in advance.

[bhenson]

Laurens - I'm not sure what you mean by that.   I have posted the scripts I'm using later in this post - hope that helps.

[tyleraherman]

Hi there,

I'm currently running into the same exact thing you encountered where you're comparing data from the current year to the previous. Chris made what seems to be an excellent suggesstion that worked out for you. I was hoping you could share how exactly you implemented his suggestion so that I could attempt to do the same on my end.

He mentioned you should try to change the grouping. Where did this change take place, in the render script or in the data generators? Perhaps you could provide a bit of your code to show how this was achieved.

"Setting your summary line for the grouping to the actual date, set it to the Month-Day string."

[bhenson]

Tyler,

I can give you the scripts that I used.   Hope that helps.   I found these in a post in community and tailored to suit what I needed.

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 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("bar");
  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("bar");
  closed.setTable('incident');
  closed.setSummaryID(closedID);
  closedDS = cu.addEmptyValues(closed.getDataset(), ds);
  ds = cu.mergeDatasets(ds, "One Year Ago", closed.getDataset(), "Past 30 Days");

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

  // Change the spacing
  closed.setNoMargins();

 
  // return our chart
  answer = chart;

Data Generator 1 (Num Incidents per day - one year ago):

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_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.  
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 (Num Incidents per day - last 30 days):

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 and Day.  
  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();  
}  

[Sandeep132]

Hi Bhenson,

Is there a way to merge 3 data generators? I have tried your code above but it is limited to only 2 data generators. I am trying to merge a 3rd data generator as well and I am not finding a way to merge it. Please let me know if there is a way to do it.

Thanks,

Sandeep