Can you create a breakdown off of a free text field?

John VanBruggen · ‎09-03-2015

Here is the scenario. My client has a field in the locations column named 'District' which is populated with a district number.
This is just a standard text field, not a reference field (I wish it was).

Is there a way to create a breakdown source for this free text field?

Check out my Consultant's Survival Guide
https://youtube.com/watch?v=zYi8KhP9SUk

pvet · ‎09-07-2015

Hi John,

This is certainly possible using a manual breakdown and some scripting.

First off you'll need to create a Manual Breakdown. You can populate the breakdown by running a scheduled job with "automatically run a script of your choosing" in which you'll query the cmn_location table (district col) and add each unique value (COUNT) to the Manual Breakdown. (select to run it each day just before the scores collection)

Next up you'll need to create a breakdown source that references the manual breakdown. The facts table should be [pa_manual_breakdowns] with the condition set to [Breakdown] [is] [<name of your manual breakdown>]

Then you'll to create a PA Script that will relate each record of the record set of the indicator, to one of the breakdown elements.

For example, using the districts via caller from the incident table:

Source on [Incident], field: [Caller.Location.<district>]

var breakdown = '<sys id of manual breakdown>'

var value = current.caller_id.location.<district col name>.getDisplayValue();

var sysID = '';

if (value && value != '') {

var gr = new GlideRecord('pa_manual_breakdowns');

gr.addQuery('breakdown', breakdown);

gr.addQuery('value', value);

gr.query();

if (gr.next()) {

sysID = gr.getValue('sys_id');

}

sysID || '';

Now you'll need to create the breakdown. It's breakdown's source is the breakdown source you created earlier. After you save the breakdown you create the breakdown mapping with facts table on 'incident' and check 'scripted' > script referencing to the script above.

Last step is adding (relating) the indicators of your choice to the breakdown and running the (historic) scores collection.

Cheers,

Pim Vet

View solution in original post

Vincent Loffel1 · ‎09-07-2015

Hi John,

Pim was right. See my answer to another similar question here:

https://community.servicenow.com/thread/192291

Cheers,

Vincent