Reports and Custom Charts

bhenson · ‎04-24-2014

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 · ‎04-24-2014

I have attached the report definitions if any of you custom chart gurus can help me with the code I would need to put in my data generator to do the same thing as these reports do.

Chris M3 · ‎04-24-2014

You basically need to create the Report Summary Lines & Report Summary manually. You'll right a script to query the records, create a Report Summary record, and create the report summary lines. Below is an example script I wrote that created some data for a custom chart. This is a Generator script, then you would create a custom chart that displays the data in the report summary. Find the report summary/lines from the reports you created to get an idea of what your end data should look like.

// Setup the Start/End Date ranges


var numWeeks = 13;


var today = new Date();


var daysBack = today.getDay() + numWeeks * 7 - 1;


var sumID = createSummary();


var gSequence = 0;






for (k=0; k<numWeeks; k++) {


     var ITILCount = 0;


     var CasualCount = 0;


     var FacilityCount = 0;


     var dStart = daysBack - k * 7 + 49;


     var dEnd = dStart - 6 - 49;


     var agg = new GlideAggregate('u_system_usage');


     agg.addAggregate('count', 'u_employee_number');


     agg.addEncodedQuery("u_last_login_timeBETWEENjavascript:gs.daysAgoStart(" + dStart +   ")@javascript:gs.daysAgoEnd(" + dEnd + ")");


     agg.addQuery('u_user_type', 'itil');


     agg.query();


     while (agg.next()) {


           if (agg.getAggregate('count', 'u_employee_number') >= 24) {


                 ITILCount++;


           } else if (agg.getAggregate('count', 'u_employee_number') >= 😎 {


                 CasualCount++;


           }


     }


     var facagg = new GlideAggregate('u_system_usage');


     facagg.addAggregate('count', 'u_employee_number');


     facagg.addEncodedQuery("u_last_login_timeBETWEENjavascript:gs.daysAgoStart(" + dStart +   ")@javascript:gs.daysAgoEnd(" + dEnd + ")");


     facagg.addQuery('u_user_type', 'ops_manager');


     facagg.query();


     while (facagg.next()) {


     FacilityCount++;


     }


     createSummaryLine(sumID, gSequence, gs.daysAgoStart(dStart - 49).split(' ')[0], ITILCount + FacilityCount + Math.round(CasualCount/3), "#FFA100", 0);


     gSequence++;


}


current.summary = sumID;


current.setWorkflow(false);


current.update();






function createSummary(title) {


     var s = new GlideRecord("sys_report_summary");


     s.title = "Total License Count";


     s.field = "Week Starting";


     s.expires = gs.endOfToday();


     return s.insert();


}






function createSummaryLine(id, seq, name, num, color, level) {


     var s = new GlideRecord("sys_report_summary_line");


     s.summary = id;


     s.sequence = seq;


     s.value = num;


     s.name = name;


     s.color = color;


     s.level = level;


     s.insert();


}

bhenson · ‎04-24-2014

Chris,

Thanks for your reply.

So I have my data generators generating the correct data now (compared actual numbers from the summary set created by the report to the numbers from the summary set created by my data generator).

But when I try to render the custom chart all I get is a little icon like it can't display the image. Here is my rendering script (copied from an example and modified (apparently not correctly)).

Any help will be SO very much appreciated!!!

//

// 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("Number of Incidents per day - one year ago");

var closedID = summary_sets.get("Number of 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");

// Get the dataset for the one year ago incidents

var open = new ChartGenerator("line");

open.setTable('incident');

open.setSummaryID(openID);

var ds = open.getDataset();

// 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);

ds = cu.mergeDatasets(ds, "Open", closed.getDataset(), "Closed");

// Get the chart for the multi series bar chart of open and closed incidents

var chart = closed.getChart(ds);

// Change the spacing

//// closed.setNoMargins();

// Add a line renderer to the chart to show the backlog of incidents

// var line = new ChartGenerator("line");

// line.setSummaryID(backLogID);

// var lineDS = cu.addEmptyValues(line.getDataset(), ds);

// lineDS = cu.changeDatasetLabel(lineDS, "BackLog");

// var renderer = line.getChartGenerator().getLineRenderer(Packages.java.awt.Color.RED, 3.0, false, true, true);

// closed.addRenderer(renderer, lineDS, true, "Back log");

// return our chart

answer = chart;

bhenson · ‎04-24-2014

So I've gotten further - realized that I had my "Type" set as Line rather than Script Generated so the script was not being executed.

Now it displays a box that says 'Incident comparison last 30 days vs 1 year ago' did not return a chart. (See attachment "Render Results".

I have NO idea what I'm doing wrong as the summary sets are being created correctly by my data generators.

The cleaned up script (took out the commented out code) is:

//