Create report by vendor, by priority, by month

[mbal1]

Hi,

I am having difficulty creating a report to show the total number of incidents by each vendor by   month by priority.   Something like this example:

My client would prefer that something custom not be created, but I cannot seem to find any other way around it.   Whether I create a trend chart, then I can only break it down by month, not for each vendor, or a pivot table where I can break it down by vendor and priority but not month!

Any help would be much appreciated.

Thanks,

Mandeep

[tltoulson]

Hi Mandeep,

Sadly, as far as I am aware, you are correct.   The out of box Reports application limited in the Group By functionality.   You have a couple options ranging in Customization/Complexity.

Option 1: New Field and Business Rule (Simplest)

Create a new field on the Incident table that will hold the joined combination of Vendor and Priority.   Write a business rule such that when either Priority or Vendor changes, the new field is updated with the Vendor and Priority concatenated.   This way, you can use a trend that Groups By the new field and trends by the date field.

Example:

Business Rule

Update: true

Insert: true

Condition: current.priority.changes() || current.vendor.changes()

Script:

current.u_new_field_name = current.vendor + ' - ' + current.priority;

This will concatenate similar to how Excel does it on charts (with a space separated dash).   Of course, modify the script with the correct field names.

Option 2: Custom Charts Module (Challenging)

Create a Custom Chart using the OOB Custom Charts module.   This uses the JFreeChart java library albeit slimmed down.   If you want to pursue this but need further direction, let me know and I will help out.   Its a little more complicated.

Option 3:   Custom UI Page (Custom)

One last option is a custom UI Page.   Using the GlideAggregate API, getting the group by and the count is trivial.   And honestly, the table you show above would not be difficult either as it would be plain HTML.   It would even be possible to provide drilldown links with minimal effort.   I can demonstrate this as well if you would like.   However, this does go into the custom realm rather than using OOB.

Let me know if you need further assistance or if you would like me to delve deeper into Option 2 or 3 further.

Kind regards,

Travis Toulson

[mbal1]

Hi Travis,

Thanks so much for the detailed response.   We will try option 1 since it is the simplest!

Much appreciated.

Mandeep

[Mariano Lemus]

Travis, I am currently in need of a customized report, similar to the one from this post

I need to group by 2 fields: WORK TASK field and TLA field, then display the hours for each day of the week.

I have managed to do this:

But still I need to group by TLA field, do you think this might be possible through UI page or Custom Charts Module?

[tltoulson]

Hi Mariano,

I am assuming that these fields are all on the same table.   If so, then you have the same options mentioned above with concatenating Work Task and TLA into a single field for reporting being the easiest.   If you preferred to accomplish this via UI Page or Custom Chart, the key is to use GlideAggregate - ServiceNow Enterprise Wiki.   Your query would look something like the following:

var ga = new GlideAggregate('TableNameHere');

ga.groupBy('work_task');

ga.groupBy('u_tla');

ga.addTrend('date_worked', 'Date'); // Acts as a Group By on date fields

ga.addAggregate('sum', 'DurationFieldHere'); // Adjust to your needs

ga.query();

while (ga.next()) {

         // Build your chart dataset or html as needed

         var workTask = ga.work_task;

         var tla = ga.u_tla;

         var date = ga.getValue('timeref');   // This gets the Trend value

         var duration = ga.getAggregate('sum', 'DurationFieldHere');

}

This method will let you iterate through the GlideAggregate, providing rows with columns that represent the Work Task, TLA, Date Worked, and Duration.   Then, all you have to do is produce a chart or table from it.