Creat custom report that combines the graphs of three reports

[e279s896]

I am having trouble getting a custom report that combines the graphs of three different reports. Basically, in the reports if I could "group by" two things instead of just one, it would be exactly what I need. Here is a screen shot of one of the reports that need to be included in the custom report.

Basically what I need to do is this exact report, except add another "group by" category that will group by primary affiliation as seen in the conditions.

[Harish Murikina]

Hi Emily,

                  You have to create custom chart.

                  Here is the more info http://wiki.servicenow.com/index.php?title=Custom_Chart_Data_Generation

Regards,

Harish.

[e279s896]

Yeah, that is my question. The first sentence says I need to create a custom chart...those directions are doing nothing for me. I have stared and googled the past few days to no avail.

[dan_cooper]

Custom charts can get pretty involved, and you'll need to be pretty handy with JavaScript to accomplish one.

I'm by no means an expert, but this is how I understand them.

A couple things to get you started:

There are three parts to a custom chart:

a generator - a script that executes a query and populates lines on a temporary table;

a summary set - the above mentioned temporary table of data that's assembled;

and a renderer - a script that takes that assembled data and presents it in a chart using JFreeChart

You will want to start with your generator (wiki page here).   Your generator script should always include the following lines:

      var g_sequence = 0;

      var id = yourQueryFunction();

      current.summary = id;

      current.setWorkFlow(false);

      current.update();

Next you will need to define yourQueryFunction(); to do the following:

1) Execute a query against your database (this is where I really don't like the wiki article because its example uses external data which I think makes it needlessly complicated).   Your query can be fairly simple depending on what data you need (more information about glide queries here).

2) Populate a row in the summary set table (called a "summary set line") with the desired information from the query.

For example, here is part of a generator that I wrote to get information from the time_card table:

function getTimeCards() {

      var getMonth = gs.beginningOfThisMonth().split('-');

      var month = getMonth[0] + "-" + getMonth[1];

      var timeCards = new GlideRecord('time_card');

      timeCards.addQuery('sys_created_on','CONTAINS', month);

      timeCards.query();

      var id = createSummary();

      while(timeCards.next()){

                  var users = timeCards.user;

                  var totals = timeCards.total;

                  createSummaryLine(id, users, totals);

      }

      return id;

}

You'll notice that the getTimeCards() function calls functions that we haven't yet defined. those being a function that creates the Summary Set (temporary table to hold your data)...

e.g.

function createSummary() {

      var s = new GlideRecord("sys_report_summary");

      s.title = "Time Cards";

      s.field = "Totals";

      return s.insert();

}

...and a function to populate the rows (summary set lines) of that summary set. e.g.

function createSummaryLine(id, user, total){

      var s = new GlideRecord("sys_report_summary_line");

      s.summary = id;

      s.sequence = g_sequence++;

      s.value = total;

      s.name = user;

      s.insert();

}

Here is where I normally execute the generator script to make sure that it's functioning properly and populating the summary set like it should.

Note: for your purposes, you'll need to have multiple generators.   Each generator will create data for one of the "graphs" you wish to overlay on the chart.   Repeat those steps for each dataset you need to gather for your chart.

Next you'll need to write your renderer script, which will produce the chart using the data that your generator put together.   Here I will stray from my example to an example that's more relevant to you.   I'll be looking at the Incident Backlog chart that comes with the plugin, since it combines multiple datasets into one chart.

You'll start your renderer script by defining variable names for the summary sets your generator created.   You'll do this using summary_sets.get();

e.g.

      var openID = summary_sets.get("Trend of Open Incidents");

      var closedID = summary_sets.get("Trend of Closed Incidents");

      var backLogID = summary_sets.get("Incident Backlog");

Then you will get a new utility class to use to manipulate the datasets:

      var cu = new ChartUtil();

      cu.setTable('incident');

      cu.setColors("#FFDEAD,#7FFF00");

You will then define as many new ChartGenerator(); variables as you have data generators.

      var open = new ChartGenerator("bar");

      open.setTable('incident');

      open.setSummaryID(openID);

      var ds = open.getDataset();

      var closed = new ChartGenerator("bar");

      closed.setTable('incident');

      closed.setSummaryID(closedID);

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

You'll see here that you will need to merge the datasets using cu.mergeDatasets();.   Here the arguments needed are (dataset1, label1, dataset2, label2);

This chart then adds a line to the chart:

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

Here you define a new chart and set the summary ID the same way you did before.   The third line compares the data in backLogID to those in the merged dataset and adds empty values where there aren't matches (this is to make sure that the charts span the same date range).   Lines five and six define the line renderer using JFreeChart, and add that renderer to the chart of the merged dataset you defined before, respectively (full disclosure: I'm not really sure what the arguments for getLineRenderer() are, so I can't really say what is being defined here other than what appears to be the color of the line - so if anyone has any clarity here, please chip in!).

You will then return your completed chart:

      answer = chart;

You can try the chart to see that it's working, and then schedule it for automatic updating.   I hope this was helpful, I personally banged my head against custom charts for quite some time last winter.   And don't hesitate to ask for more clarification on any of this.

Thanks,

Dan Cooper

University of Kentucky

[Sandeep132]

Hi Daniel,

Thank you for your explanation in creating custom charts. Can you please let me know how can we merge a 3rd data generator of type Bar? In your explanation you have shown how to merge a 3rd data generator of type Line. I am trying to merge a Bar Data generator.

Thanks,

Sandeep