Breakdown elements to show only specific values

ana beatriz
Tera Guru

I have a requirement to populate a breakdown element from a Dashboard with values that I was able to only retrieve creating a database view.

The breakdown element is on Assigned To field from Interaction table.

The database view is a join of interaction, sys_user_has_skill, cmn_skill and sys_user tables.

Because I want to show on the breakdown element only the users populated on Assigned To field on Interaction table that have specific skills.
But now, I'm not being able to pull those values on the breakdown element.

I tested the following script on the background script and it is returning the exactly correct users I want to see on the breakdown element:

 

assignedTo = function(){
    var gr = new GlideAggregate('u_interactions_assigned_to_hr_chat_group_members');
    gr.addEncodedQuery('int_u_hr_related=true^skill_level_type=51d1d8511b764690167b759d1e4bcb47');
    gr.addAggregate('COUNT');
    gr.groupBy('user_name');
    gr.query();
    while (gr.next()) {
     return gr.user_name; 
// used 
 gs.info(gr.user_name); to test on the background script
    }
};
  assignedTo();
 
But now I'm not able to make those values return on the breakdown source created for the breakdown element. 
I tried creating a breakdown mapping where the facts table is the sys_user and the script contains the above script.
Also tried creating a bucket group but it didn't work.

Anyone can help me with this?

Thank you very much
1 ACCEPTED SOLUTION

ana beatriz
Tera Guru

In case anyone else faces this issue too, I made it work by creating a script include and calling it into the Breakdown source filter. 

The Script Include must be Client callable 

Here's the code:

var HRPerformanceAnalyticsUtils = Class.create();
HRPerformanceAnalyticsUtils.prototype = Object.extendsObject(AbstractAjaxProcessor, {
    getHRChatReps: function() {
        var users = [];
        var grUserHasSkills = new GlideAggregate('sys_user_has_skill');
        grUserHasSkills.addEncodedQuery('skill.level_type=51d1d8511b764690167b759d1e4bcb47^active=true');
        grUserHasSkills.addAggregate('COUNT');
        grUserHasSkills.groupBy('user');
        grUserHasSkills.query();
        while (grUserHasSkills.next()) {

            var gr = new GlideAggregate('interaction');
            gr.addEncodedQuery('u_hr_related=true^assigned_to.active=true');
            gr.addQuery('assigned_to', grUserHasSkills.user);
            gr.addAggregate('COUNT');
            gr.groupBy('assigned_to');
            gr.query();
            while (gr.next()) {
                users.push(gr.assigned_to.toString());
            }

        }
       return users;

    },

    type: 'HRPerformanceAnalyticsUtils'
});

View solution in original post

1 REPLY 1

ana beatriz
Tera Guru

In case anyone else faces this issue too, I made it work by creating a script include and calling it into the Breakdown source filter. 

The Script Include must be Client callable 

Here's the code:

var HRPerformanceAnalyticsUtils = Class.create();
HRPerformanceAnalyticsUtils.prototype = Object.extendsObject(AbstractAjaxProcessor, {
    getHRChatReps: function() {
        var users = [];
        var grUserHasSkills = new GlideAggregate('sys_user_has_skill');
        grUserHasSkills.addEncodedQuery('skill.level_type=51d1d8511b764690167b759d1e4bcb47^active=true');
        grUserHasSkills.addAggregate('COUNT');
        grUserHasSkills.groupBy('user');
        grUserHasSkills.query();
        while (grUserHasSkills.next()) {

            var gr = new GlideAggregate('interaction');
            gr.addEncodedQuery('u_hr_related=true^assigned_to.active=true');
            gr.addQuery('assigned_to', grUserHasSkills.user);
            gr.addAggregate('COUNT');
            gr.groupBy('assigned_to');
            gr.query();
            while (gr.next()) {
                users.push(gr.assigned_to.toString());
            }

        }
       return users;

    },

    type: 'HRPerformanceAnalyticsUtils'
});