How can I create a breakdown that shows the Name instead of Number -- for example, if I want breakdown by project, but not the project number, instead the project name

[cathy_godwin]

I want to create a breakdown that comes from table an auto number, like Project.     However, I want the breakdown to be on the project title, not the Project number (for example).   Can it be done?

[Vincent Loffel1]

Hi Cathy,

I think you need to create a manual breakdown and populate it with a scheduled script that runs on a daily basis.

First go to PA > Breakdowns and and click new. 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.

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 populate manual breakdown project names.

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

var table = 'pm_project';

var col = 'title';

var breakdown = 'sys_id of manual breakdown';

gs.log('Collection of Project titles started');

(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.setValue('breakdown', breakdown);

                                gr.setValue('value', value);

                                gr.insert();

                                gr.close();

                      }

            }

            ga.close();

            gs.log('Collection of Project titles 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 if 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] and set label for unmatched to "No Project Title".

Now you have to create an PA script to relate each record to you breakdown element. Go to PA > Scripts and crate a new script. Give it a name and create a script similar to this:

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

var value = current.[field that holds the title of project].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 all you need to do is create an automated breakdown that uses the script for the mapping. Use the breaksource you created earlier and save the breakdown. Now create the breakdown mapping. Set the facts table to the table you want to report on and check the scripted checkbox. Select the mappingscript you created and submit.

Now you should have a breakdown on project title.

[Uncle Rob]

Thread Zombie!

vincentloffeld, what is the purpose of the line sysID || ''; at the end of your script?  

[Vincent Loffel1]

Hi Robert,

If the variable sysID is NULL it doesn't return NULL, but and empty string ('')

[Uncle Rob]

Ah, the purpose makes sense!   Script syntax checker doesn't like it, even though it will accept a save.   Just an FYI to those using this code.