How to create breakdowns on Catalog variables in Performance Analytics in ServiceNow?

Anand78
Giga Contributor

I am new to PA and new to ServiceNOW.

I am trying to create few dashboards for Catalog Request Items to present meaningful data.

My data segregation is in variables on catalog items.

I do not see any way to create breakdowns on Variables.

Can anybody shed some light on it or provide pointers for me to move in right direction?

13 REPLIES 13

Dennis R
Tera Guru

Quick note: I seem to have exceeded my screenshot upload limit on the community board for today. Hopefully the descriptions below will give you enough info to carry out what you're trying to do. I'll try to upload the rest of the screenshots tomorrow. Also, apologies in advance if the formatting of this post is borked up. I'm still not used to the new community forum formatting and editor...

 

Okay, here goes. I'm going to step-by-step describe how I'd set this up, assuming that your variable field is a reference.

Variable Reference Field

Creating a Sample Table

I'm going to create a sample table below that will be the table to which my request item will refer. Note that if this table already exists (for example, if you are creating a variable that contains a reference to the Group [sys_user_group] table), you do not have to do this step. But I don't want to assume anything, so I'm going to start from square one.

 

 

For my example, I've created a table called My Custom Table [u_custom_table] with one field, Description [u_description]. I'm going to have a variable in my catalog item that points to this table. I'll create a few records...

 

Again, if you're going to have your reference field point to an existing table (such as users, groups, or whatever), you don't have to do this part. This is only relevant if you are going to be referring to your own custom table.

Creating a Catalog Item

Okay, now I'm going to create a catalog item with a reference variable that points to this table. Here are my catalog item details. Obviously, if you already have a catalog item, you don't have to create one specifically for this, but just for the sake of knowing what I'm defining and where, I'll include a brand new one:

 

 

And here is my variable definition. I'm going to call it custom_item, and it's going to point at the new table I created above.

 

 

With that done, I'm going to create a few new requested items from the catalog item.

 

Creating a Breakdown Source

Before you can sort requests into various breakdown buckets, you have to specify what those buckets are. (And I'm using the term "buckets" in a general sense; not in the literal sense of PA Bucket Groups. For example, if you're breaking down your requested items by assignment group, I'm considering a "bucket" as one specific assignment group to which zero or more requested items may be assigned and sorted into.)

 

To do that, you'll need a breakdown source. Before you go creating one, you should check the existing list of Breakdown Sources [pa_dimensions] to see if one is already out there. If you're breaking down by records in a table that already existed (such as assignment group, user, or other such table), there's a fairly good chance that the breakdown source is already there out of the box. In this case since my buckets are defined by a completely new custom table, I'm going to have to create my own breakdown source.

 

To do so, I'll pull up my list of Breakdown Sources [pa_dimensions] and hit the New button to create a new one. I'll call it "Custom Table" (you can name it anything you want, try to make it make sense). My facts table will be My Custom Table, and—this is important—unless you really know what you're doing, use the sys_id as the field. (This "Field" field is the field that your automated breakdown will use to match up your requests to a record in the breakdown source table.)

 

Note that you can also put conditions on this source. For example, if your source table is Groups [sys_user_group], you can put a condition that the Active field must be true. If you do that, however, you might end up with some unmatched records if, for example, you mark a group inactive between the time a requested item is assigned to a group and the time the collector runs next.

 

For my example, I won't put any conditions on the table, so that all of the elements are available.

 

 

Note also that I entered a label "Unknown" for any records that, for whatever reason, don't match up with a record in My Custom Table. This might happen if, for example, a record is deleted from the table so that the sys_id stored in the requested item variable doesn't match up with any record in the My Custom Table table.

 

[Side note: After typing this up I noticed that I misspelled "Unknown" in the screen shot above. I corrected it after pasting that screenshot in, but I'm too lazy to create another screenshot. Please just imagine that I corrected the spelling before saving it. :)]

Creating the Mapping Script

All right, now that we have a breakdown source, there's one more thing we have to do before actually creating the automated breakdown. If we had the record directly available to us in the requested item record (such as Assignment group, which would map directly to a Group breakdown), we wouldn't have to do this part. But since we're going to be looking at requested item variables, we're going to have to create a script that will take a requested item, pull the variable value, and match it up to a record in the breakdown source table.

 

Create a script by clicking on Performance Analytics > Automation > Scripts [pa_scripts]. Click on the New button and name your script something that makes it obvious what it is. I'll call mine Map RITM to Custom Table Record. For your facts table, select Requested Item [sc_req_item].

 

Now this is where breakdowns get a bit difficult. There will be a variable, current, that is passed into this script. However, unlike in business rules and other records, you don't by default have access to every field in the current record. This is kind of weird, but you have to specifically tell your script which fields from that facts table you want to pipe into the script.

 

To make matters a little bit worse, if you search for a Variables field, you'll see it in there. Unfortunately, though, it doesn't actually pass the variables in. Long story short, as far as I know, you need to pass in the sys_id field and do a GlideRecord lookup so that you can get access to all the fields—especially the variables—with the requested item that is passed in.

 

So make sure that you have Sys ID selected in the Fields list so that you can access current.sys_id in your script.

 

 

The script itself doesn't have to be fancy schmancy; all it has to do is, given a requested item, evaluate to a sys_id that corresponds to a record in your breakdown source table. I've put a few checks in there (I'll explain later why) to validate that we actually have a value for that variable. Here's the text version of the script I'm using:

 

(function getCustomRecord(current) {
    var grReq = new GlideRecord('sc_req_item');
    if (grReq.get(current.sys_id)) {
        if (grReq.variables.custom_item)
            return grReq.variables.custom_item.getValue();
    }
    return '';
})(current);

 

If you're using an out of the box breakdown source that hits against an existing table, just make sure that in this script you're returning a sys_id of a record from that table.

Creating the Breakdown

Now that we have a method of mapping a requested item to a record in our referenced table, we can create the breakdown. To do so, navigate to Performance Analytics > Breakdowns > Automated Breakdowns and click the New button. Give it a name that makes sense and a description if you want, and select your Breakdown source that you either created above or that already exists for the table you're referencing.

 

 

After saving your new automated breakdown, there will be a Breakdown Mapping related list at the bottom of the record. This is where the magic happens; where ServiceNow takes your requested item records and sorts them into the buckets of the table you specify. Create a new Breakdown Mapping, specifying Requested Item [sc_req_item] as your facts table, since that's the type of record you're going to be passing into your script. Select the Scripted checkbox, and enter your script that you created above in the Script field.

 

 

With that, you should be ready to go with your indicator!

Creating the Indicator

The last step is to create your indicator. I'm going to create one that runs against all open Requested Items [sc_req_item]. Since the breakdown I'm looking for is only applicable to "I Need This" requests (as defined way up top), I'm going to put an Additional condition limiting the records to only those requests.

 

 

Now I'll add the breakdown in the related list at the bottom of the indicator form, and add it to a job so that the collector will run on it.

 

 

When I run the job, I get back data, and the breakdown is by record in my custom table.

 

 

If I click on one of the breakdowns and pull up the records, I can validate that they all have the same record from My Custom Table selected. Note that I have one record filed under "Unknown". Why is that? If I pull up the record I can see why:

 

 

It's because for that requested item, I didn't populate the variable with a value. This is also why, as I mentioned above and promised to explain later, I put in checks to make sure that even if the variable isn't there, the mapping script returns something. In case something gets screwed up along the way and requested items that aren't applicable get passed into the mapping script, they'll be classified as "Unknown" so that the process can be troubleshot to check if something is going awry.

Variable Choice List

Because the steps involved are almost identical for a variable choice list, I'm going to shortcut most of the long-form description. For demonstration purposes, I'm going to add a choice variable to my I Need This catalog item to allow the user to select a planet:

 

 

I added a bunch of choices to choose from:

 

 

The most important difference between using a variable choice list and a reference table is:

Creating a Breakdown Source

When using a variable choice list, you need to create a breakdown source for your variable choices. As mentioned in a post above, the facts table for this breakdown source is going to be the Question Choice table. Create a new breakdown source using Question Choice [question_choice] as your facts table and Sys ID as your field.

 

 

However, you don't really want ALL choices for ALL questions to be the domain of possible answers. You really only want the answers to the "Select a planet" question. In this case, you will want to put a condition on the records in the Question Choice [question_choice] table. That condition will be where the Question is "Select a planet". When I hit the preview button, I can see that I have 8 records that match this condition, which is exactly what I wanted since there are 8 possible choices to my question. Enter that condition and save your breakdown source.

Creating a Breakdown Script

I'll need to create another breakdown script that maps the requested item passed in to the sys_id of one of the choices. This script is going to be almost exactly the same as the script for the reference field above, with one glaring exception: The value that is stored in the variable is not the sys_id of the question choice, and our script must return the sys_id of the question choice, NOT the value of the variable!

 

So what are we to do? Well, we'll have to do another lookup to get the sys_id of the question choice. Here's the script:

 

 

(function getPlanetChoice(current) {
    var grReq = new GlideRecord('sc_req_item');
    if (grReq.get(current.sys_id)) {
        if (grReq.variables.planet) {
            var grChoice = new GlideRecord('question_choice');
            if (grChoice.get('value', grReq.variables.planet.getValue()))
                return grChoice.getUniqueValue(); // sys_id of choice
        }
    }
    return '';
})(current);

 

See how the core purpose of the script remains the same as it was above, even though we had to jump through an extra hoop? The sole purpose of the script is to take a requested item and convert that into a sys_id from the facts table specified in the breakdown source.

Creating the Breakdown

Now that we have a mapping script, we're ready to create the breakdown. This part is pretty much the same as above, just be sure to specify your new breakdown script created just above.

Creating the Indicator

Same as above, but enable the new breakdown you created just above.

Helpful Hint: Creating an Indicator Source

One thing you might notice when running these collectors is that if you run them against an indicator using a source of a crapton of requested items, even if you whittle down those items by putting a condition on the indicator, your breakdown mapping is going to be evaluated for every record that meets the criteria of the indicator source, whether or not it will ultimately be weeded out by the indicator condition.

 

Because of this, you may want to consider creating an indicator source specifically for the catalog item you want to sort according to variable and filter the records out at the indicator source instead of the indicator. This should make your collection much more efficient.

 

Hope all of this helps, let me know if you have any questions or issues.

 

 

Anand78
Giga Contributor

Wow, This is very detailed explanation. Thank you for all the time you spend to answer this. It is going to take couple of days for me to digest this all and try what I have asked for here.

Thanks you again for all guidance.

I will post the results here after I try it out.

Best Regards,

Anand

 

Fantastic job Dennis! This was well written and I can follow this even without the screenshots. I know ill have a little learning curve with the reference field mapping script but Ill reply if I have any questions.  Everything else makes logical sense to me!  I cant thank you enough!

 

Hello,

 

I attempted to replicate your script using the question_choice table & sc_task as the variables are passed down from the RITM to the catalog task with no success.

 

I was able to get this to work when the item variable is a reference to another table even on the sc_task record. 

 

Can you please advise?

Hello,

i am not able to do breakdown mapping for variables, although there is no sys_id inside variable. Can you guide me how Breakdown mapping to be configured for creating breakdown on variables