How to report on how many change requests have been changes its state from Work in Progress to Completed in a day?

[Arpan6]

I have a requirement to report on when the state of a change request changes to get the count of records changed from 'new'   to 'work in progress' state or 'work in progress to completed' or 'completed to closed' etc. I have created a metric definition on 'change_request' table on the field 'state' with type 'field value duration' and reported on the 'Change Metric' database view. But I am only getting the count and time when it changes from a particular state (eg,   work in progress) but cannot determine the count that how many of them changes from 'work in progress to completed' or 'work in progress to cancelled'.

Here I am only getting the count that 33 change requests have change their state from 'Draft' state bu I want that out of that 33   how many change requests have changed their state from 'Draft' to 'Review'.

Does anyone have any idea how to do that?

[Julian Hoch]

I've not done this before, but I'd try to create a definition for each start state, and then in the script get the previous state of the record and see if that matches the current state. It might be a bit difficult to get the previous state, you might have to query the metrics instance table.

In the end, it might be easier to roll your own reporting table with a custom business rule to capture exactly the information you need. But perhaps there is an easier way to capture a different but similar metrics, that might also fulfil your reporting needs?

I'd go back and try to find out what the business actually wants to achieve with this report. Maybe there's an easier answer if you dig a little.

[Arpan6 · Accepted solution]

Thanks Julian,

I have created a before   business rule on change_request table to capture it , and it works,

condition: current.state.changes()

(function executeRule(current, previous /*null when async*/) {

insertMetrics();

function insertMetrics() {

      var mi= new GlideRecord('metric_instance');

      //sys id of the metric definition

      var metricSysID = 'sys id of metric definition';

      mi.initialize();

      mi.definition = metricSysID;

      mi.start = previous.sys_updated_on;

      mi.end = gs.nowDateTime();

      mi.duration = gs.dateDiff(mi.start, mi.end);

      mi.id = current.sys_id;

      mi.value =     previous.state.getDisplayValue() + ' to ' + current.state.getDisplayValue();

      mi.calculation_complete = true;

      mi.insert();

}

})(current, previous);