How can I create a breakdown by task type on the Time Card table?

[cathy_godwin]

We are building a series of indicators on the Time Cards table and we want to have a breakdown by the task type.   I thought that I would create the breakdown source from the sys_class_name on the task table, but this does not seem to bring any values.

[Vincent Loffel1]

Hi Cathy,

To use the task type as a breakdown, you have to create a manual breakdown and populate it with a scheduled script that runs on a daily basis. The answer to this question is similar to a question you posted a few weeks ago: Re: How can I create a breakdown that shows the Name instead of Number -- for example, if I want bre...

But let me help you with this specific breakdown

- First go to PA > Breakdowns and and click new.

- Give it a name like: Task.Type.

- Set the type to manual and leave display unchecked.

- From the context menu click Save.

- Then use the context menu and select copy sys_id and copy the sys_id (Crtl + C).

Your manual breakdown is ready.

- Now go to System Definition > Scheduled Jobs and click new.

- Choose the 3rd option Automatically run a script of your choosing.

- Set name to something like: Task Types.

Then create your script, which might be something like this:

//provide context:

var table = 'task';

var col = 'sys_class_name';

var breakdown = '[sys_id of manual breakdown]';

gs.log('Collection of Task types started');

//do not modify:

(function(table, col, breakdown) {

  var ga = new GlideAggregate(table);

  ga.addAggregate('COUNT', col);

  ga.query();

  while(ga.next()) {

  var value = ga.getDisplayValue(col);

  var gr = new GlideRecord('pa_manual_breakdowns');

  gr.addQuery('breakdown', breakdown);

  gr.addQuery('value', value);

  gr.query();

  if(!gr.next()) {

  gr = new GlideRecord('pa_manual_breakdowns');

  gr.initialize();

  gr.setValue('breakdown', breakdown);

  gr.setValue('value', value);

  gr.insert();

  gr.close();

  }

  }

  ga.close();

  gs.log('Collection of Task types finished');

}) (table, col, breakdown);

Set the script active and run on a daily basis but choose a time before the scores collection of performance analytics. Then hit Execute now to fill the breakdown the first time.

Check your manual breakdown if all elements have been added.

Then create a breakdown source that uses the manual breakdown as a source.

- Go to PA > Breakdown Sources and click new.

- As source use the facts table pa_manual_breakdown and set the field to sys_id.

- Create a condition: breakdown is [the name of your manual breakdown: Task Types]..

Now you have to create an PA script to relate each record to you breakdown element.

- Go to PA > Scripts and create a new script.

- Give it a name (Time_Card.Task.Type)

- Set the Facts table to Time_card and select the task type field.

- Create a script similar to this:

//provide context:

var breakdown = '[sys_id of manual breakdown]';

var value = current.time_card.sys_class_name.getDisplayValue();

//do not modify:

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 all you need to do is create an automated breakdown that uses the script for the mapping. Use the breakdown source from above and save the breakdown. Now create the breakdown mapping. Set the facts table to the table you want to report on (time_card) and check the scripted checkbox. Select the mappingscript you created (Time_Card.Task.Type) and submit.

Now you have a breakdown on Time Card Task type.

Cheers,

Vincent

[Subhrajit2]

Hi Vincent,

I was also looking for similar solution for Task table. I implemented your solution and it worked perfectly. However, one observation is that the task types of the records returned by the indicator source should be present as a value in the manual breakdown table. Otherwise the PA script will throw exception error and the job will not pull any data.

Thanks again for the solution.

Regards,

Subhrajit

[Vincent Loffel1]

Hi Subhrajit,

I'm glad this solution works for you! To prevent the score collection from throwing errors, schedule the first script that fills the manual breakdown to run just a couple of minutes before the PA collection job. This ensures you that all task types are in the breakdown.

Cheers,

Vincent

[jpatton]

I am trying to use your scripting and steps for a manual breakdown to create Task types on the Incident table instead of the time card table.

Below is the error I am receiving...   Help!!!   Thank you!

Error during JavaScript evaluation: Not all references of "current" are passed in by "arguments" script: //provide context:

var breakdown = 'afb6735a133f020018e7d0322244b031';

var value = current.incident.sys_class_name.getDisplayValue();  

//do not modify:

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 || '';