How to filter Requested Item table based upon the volume of tickets raised for a catalog item?

[Gopi22]

Hi,

I want to display the top 3 catalog items based on the volume of tickets raised.

Please help!!

Thanks,

Gopi

[Michael Fry1]

Report on requested item table, group by item, where State is closed. That should give you a count for each catalog item so you can determine which ones as the highest volume.

[engstewart]

If you need a quick and dirty answer, try this:

Add the Item field to a list view and filter the list as desired.

Right-click the Item header and choose Bar Chart.

The largest bar on the left is the highest count.

[Gopi22]

Hi,

Yes I have tried all these. But what i need is to display the first 3 items with highest number of tickets raised. 

How can i achieve this through scripting ?

Thanks,

Gopi

[Michael Fry1 · Accepted solution]

Background script? You can do something like this:

var limit = 3;
var items = [];

var count = new GlideAggregate('sc_req_item');
count.addAggregate('COUNT','cat_item');
count.groupBy('cat_item');
count.addQuery('cat_item.sys_class_name', 'NOT IN', 'sc_cat_item_guide,sc_cat_item_wizard,sc_cat_item_content');
count.orderByAggregate('COUNT', 'cat_item');
count.query();
while (count.next() && items.length < limit) {
 
  var item = {};
  item.count = count.getAggregate('COUNT', 'cat_item');
  item.name = count.cat_item.name.getDisplayValue();
  item.short_description = count.cat_item.short_description.getDisplayValue();
  item.picture = count.cat_item.picture.getDisplayValue();
  item.price = count.cat_item.price.getDisplayValue();
  item.hasPrice = count.cat_item.price != 0;
  item.sys_id = count.cat_item.sys_id.getDisplayValue();
  items.push(item);
}

for (var i = 0; i < items.length; i++) {
var line = items[i];

gs.print(line.name);
}