Reporting on tickets for each member in a group

RKumar3 · ‎05-13-2015

Hi All,

I am looking for a report given below. Please let me know your suggestions.

I would like it to display the names of the people in a group and against them list

Number of tickets in their queue
Number of tickets closed that day
Number of ticket closed that week
Number of tickets closed that month

Report should look like something given below

or

Many thanks in advance.

Regards,

Rajnish Kumar

RKumar3 · ‎05-15-2015

Alright. I have managed to get my custom chart. It is shown below. I am able to drill-down "Currently Assigned" and "Closed This Month" bars when I click them but not able to drill-down other two line charts. anyone can help on this please?

Below is the code in render script.

var closedMonth = summary_sets.get("Resolved this month(EMEA)");

var closedWeek = summary_sets.get("Resolved this week(EMEA)");

var closedToday = summary_sets.get("Resolved today(EMEA)");

var assigned = summary_sets.get("Tickets in queue(EMEA)");

var cu = new ChartUtil();

cu.setTable('u_task_assignment_history');

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

var ClosedThisMonthGenerator = new ChartGenerator("bar");

ClosedThisMonthGenerator.setTable('u_task_assignment_history');

ClosedThisMonthGenerator.setSummaryID(closedMonth);

//close.setStacked();

var dsClosedThisMonth = ClosedThisMonthGenerator.getDataset();

var assignedGenerator = new ChartGenerator("bar");

assignedGenerator.setTable('u_task_assignment_history');

assignedGenerator.setSummaryID(assigned);

var dsAssigned = assignedGenerator.getDataset();

dsClosedThisMonth = cu.mergeDatasets(dsAssigned, "Currently Assigned", dsClosedThisMonth, "Closed This Month");

var chart = assignedGenerator.getChart(dsClosedThisMonth);

assignedGenerator.setNoMargins();

var lineClosedThisMOnth = new ChartGenerator("line");

lineClosedThisMOnth.setTable('u_task_assignment_history');

lineClosedThisMOnth.setSummaryID(closedWeek);

var lineDS1 = cu.addEmptyValues(lineClosedThisMOnth.getDataset(), dsClosedThisMonth);

lineDS1 = cu.changeDatasetLabel(lineDS1, "Closed This Week");

var renderer1 = lineClosedThisMOnth.getChartGenerator().getLineRenderer(Packages.java.awt.Color.RED, 3.0, false, true, true);

assignedGenerator.addRenderer(renderer1, lineDS1, true, "Server Support(EMEA) Report");

var line = new ChartGenerator("line");

line.setTable('u_task_assignment_history');

line.setSummaryID(closedToday);

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

lineDS = cu.changeDatasetLabel(lineDS, "Closed Today");

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

assignedGenerator.addRenderer(renderer, lineDS, true, "Server Support(EMEA) Report");

answer = chart;

Know Your Expert | Pradeep Sharma

Bhavesh Jain

Abhiram Bharadwaj Diddigi

Harikrishnan Vijayan

Kalaiarasan P

ProbirDas · ‎05-15-2015

Here is the Script. This should give you the first chart. Modify the query conditions as per your requirement.

function findIncidentsClosedThisMonth(usr,grp)


{


   var gr = new GlideAggregate('incident');


   gr.addEncodedQuery('closed_atONThis month@javascript:gs.beginningOfThisMonth()@javascript:gs.endOfThisMonth()^assigned_to='+usr+'^assignment_group='+grp);


   gr.addAggregate('COUNT');


   gr.query();


   var incidents = 0;


   if (gr.next()) 


       incidents = gr.getAggregate('COUNT');


   return incidents;


}


function findIncidentsClosedThisWeek(usr,grp)


{


   var gr = new GlideAggregate('incident');


   gr.addEncodedQuery('closed_atONThis week@javascript:gs.beginningOfThisWeek()@javascript:gs.endOfThisWeek()^assigned_to='+usr+'^assignment_group='+grp);


   gr.addAggregate('COUNT');


   gr.query();


   var incidents = 0;


   if (gr.next()) 


       incidents = gr.getAggregate('COUNT');


   return incidents;


}


function findIncidentsResolvedToday(usr,grp)


{


   var gr = new GlideAggregate('incident');


   gr.addEncodedQuery('resolved_atONToday@javascript:gs.daysAgoStart(0)@javascript:gs.daysAgoEnd(0)^assigned_to='+usr+'^assignment_group='+grp);


   gr.addAggregate('COUNT');


   gr.query();


   var incidents = 0;


   if (gr.next()) 


       incidents = gr.getAggregate('COUNT');


   return incidents;


}


var myGroup = "8a4dde73c6112278017a6a4baf547aa7";


var barDataset = new Packages.org.jfree.data.category.DefaultCategoryDataset();


var grpGR = new GlideRecord("sys_user_grmember");


grpGR.addQuery('group',myGroup);


grpGR.query();


while(grpGR.next())


{


   barDataset.addValue(findIncidentsClosedThisMonth(grpGR.user,myGroup)," Closed This Month", grpGR.user.name );


   barDataset.addValue(findIncidentsClosedThisWeek(grpGR.user,myGroup),"Closed This Week",grpGR.user.name );


   barDataset.addValue(findIncidentsResolvedToday(grpGR.user,myGroup),"Resolved Today",grpGR.user.name );


}   


var barr = new ChartGenerator("bar");


barr.setStacked();


var chart = barr.getChart(barDataset);


answer = chart;

RKumar3 · ‎05-15-2015

Hi Probir,

Thanks very much for your response. I used your method to create individual datasets and merge them into one. I got the results I want and I am able to drill down as well but the colors on the graph are not appropriate. Same color is being used for multiple categories(Light Blue) as shown in below image. also some new colors like light green orange and yellow appeared unexpectedly.

Below is my code.

var closedMonth = summary_sets.get("Resolved this month(EMEA)");

var closedWeek = summary_sets.get("Resolved this week(EMEA)");

var closedToday = summary_sets.get("Resolved today(EMEA)");

var assigned = summary_sets.get("Tickets in queue(EMEA)");

var cu = new ChartUtil();

cu.setTable('u_task_assignment_history');

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

var ClosedThisMonthGenerator = new ChartGenerator("bar");

ClosedThisMonthGenerator.setTable('u_task_assignment_history');

ClosedThisMonthGenerator.setSummaryID(closedMonth);

var dsClosedThisMonth = ClosedThisMonthGenerator.getDataset();

var assignedGenerator = new ChartGenerator("bar");

assignedGenerator.setTable('u_task_assignment_history');

assignedGenerator.setSummaryID(assigned);

var dsAssigned = assignedGenerator.getDataset();

var closedThisWeekGenerator = new ChartGenerator("bar");

closedThisWeekGenerator.setTable('u_task_assignment_history');

closedThisWeekGenerator.setSummaryID(closedWeek);

var dsClosedThisWeek = closedThisWeekGenerator.getDataset();

var closedTodayGenerator = new ChartGenerator("bar");

closedTodayGenerator.setTable('u_task_assignment_history');

closedTodayGenerator.setSummaryID(closedToday);

var dsClosedToday = closedTodayGenerator.getDataset();

var dataset = new Packages.org.jfree.data.category.DefaultCategoryDataset();

for (var i = 0; i < dsClosedThisMonth.getRowCount(); i++) {

for (var j = 0; j < dsClosedThisMonth.getColumnCount(); j++) {

dataset.addValue(dsClosedThisMonth.getValue(i, j), "Closed This Month", dsClosedThisMonth.getColumnKey(j));

}

for (var i = 0; i < dsAssigned.getRowCount(); i++) {

for (var j = 0; j < dsAssigned.getColumnCount(); j++) {

dataset.addValue(dsAssigned.getValue(i, j), "Assigned", dsAssigned.getColumnKey(j));

}

for (var i = 0; i < dsClosedThisWeek.getRowCount(); i++) {

for (var j = 0; j < dsClosedThisWeek.getColumnCount(); j++) {

dataset.addValue(dsClosedThisWeek.getValue(i, j), "Closed This Week", dsClosedThisWeek.getColumnKey(j));

}

for (var i = 0; i < dsClosedToday.getRowCount(); i++) {

for (var j = 0; j < dsClosedToday.getColumnCount(); j++) {

dataset.addValue(dsClosedToday.getValue(i, j), "Closed Today", dsClosedToday.getColumnKey(j));

}

var barr = new ChartGenerator("bar");

//barr.setStacked();

var chart = barr.getChart(dataset);

barr.setNoMargins();

answer = chart;

RKumar3 · ‎05-18-2015

Anyone with the solution to this issue I am facing?

Many thanks in advance.

Regards,

Rajnish