PA Indicator breakdown on database view not working

[Jason184]

Background (the ask):

Using the SAFe module, I need to provide trending data on the average amount of time tasks (User Stories, Features, Epics) spend in each State, broken down by State and Team.  To accomplish this, I have set up 2 Metrics Definitions.  The first one simply gathers time in state metrics for the task (triggers when the State field changes).  The second is a scripted metric that (after a task is no longer active) collects the duration/state data from the first metric and sums them together (e.g. if the task record was in WIP more than once, all WIP durations are summed).  This gives me 1 metric record duration for each state per task record. Then I create a Database View joining the 2nd metric instance records with the appropriate task table (e.g. sn_safe_story) so I can use the SAFe Team field as a breakdown.  Everything works fine up to this point.  

Now I create PA indicator, who's source is the Database View, to gather the average time in state broken down by State and SAFe Team (Gather Breakdown Matrix is checked).  After running the job, when I look at the Score Sheet for the indicator, I can see breakdown scores for Team, but although each State is listed, there are no scores for the State breakdown.  Now I know that the 'Value' field in a 'Metric Instance' record is not a reference field, it is a string, so I created a scripted breakdown to get the proper 'Choice' sys_id: 

function getStateSysId(state){
	var id;
	var states = new GlideRecord('sys_choice');
	states.addQuery('inactive', false);
	states.addQuery('element', 'state');
	states.addQuery('name', 'rm_story');
	states.addQuery('label', state);
	states.query();
	while(states.next()){
		id = states.getValue('sys_id');
	}
	return id;
}
getStateSysId(current.mi_value);

This PA Automation script uses the database view as the Facts Table and the Metric Instance Value field.

Since this did not seem to work, I tried modifying the database view to include the Choice table on mi_value = ch_label.  Now I can successfully see the choice fields in the database view, including the Choice sys_id.  So I modified the breakdown mapping to the ch_sys_id field and re-ran the collection job for the Indicator.  Still no breakdown data for States.  

The Question:

1) Why do neither of these breakdown methods work?  Have I missed something?  

2) Is this the most efficient way to gather these trends?  Is there another angle from which I could provide this info that I have not thought about?

Please let me know if anything needs further clarification.

Thank you,

Jason

[Adam Stout]

Use Field value instead of Field:

[Jason184]

I was using the 'Value' field not the 'Field' field, however, I did modify the breakdown script to use the 'field_value' field to lookup the choice list sys_id as in the below script:

function getStateSysId(state){
	var id;
	var states = new GlideRecord('sys_choice');
	states.addQuery('inactive', false);
	states.addQuery('element', 'state');
	states.addQuery('name', 'rm_story');
	states.addQuery('value', state);
	states.query();
	while(states.next()){
		id = states.getValue('sys_id');
	}
	return id;
}
getStateSysId(current.mi_field_value);

This, however, is still not returning any state scores on the indicator.  Please let me know if I mis-interpreted your suggestion.

Thanks,

Jason

[Adam Stout · Accepted solution]

Sys_choice is a special field.  You may be double converting since the system auto converts the value to a sys_id in the data colelctor.

Try it without the script (just using the mi_field_value) and see if it work.  

[Jason184]

That did it!  I am now pulling scores for the States.  Thank you very much, Adam.