question Report on time between change 'Review' state and 'Planned Start' in ITSM forum

Report on time between change 'Review' state and 'Planned Start'

ebaileyybs — Wed, 05 Nov 2014 16:36:03 GMT

Hi,

We are looking to create a report for Change Management which will show the time between a change moving to 'Review' state and the 'Planned Start' date of the change record.

We would like the time to be calculated for every change, so the information can then be exported and we can work out an average. However if there is a way to obtain the time between Review and Planned Start for each record and an average then that would be great.

Please let me know if further information is required.

Thanks in anticipation,

Emma

Re: Report on time between change 'Review' state and 'Planned Start'

jaybray — Wed, 05 Nov 2014 16:41:35 GMT

Hi Emma,

I believe you can achieve this via Metrics: http://wiki.servicenow.com/index.php?title=Metric_Definition_Support

From an Analytical, time series reporting perspective this can also be achieved via performance analytic which will provide KPI/Metric dashboarding of such trends, averages and performance.

Re: Report on time between change 'Review' state and 'Planned Start'

ebaileyybs — Thu, 06 Nov 2014 09:57:15 GMT

Thanks Jason, I have looked into this, however, I can only base it on the duration of one field. I would like to calculate the difference between two fields and I cannot see a way to do this - I assume a script is required?

Re: Report on time between change 'Review' state and 'Planned Start'

Anurag Tripathi — Thu, 06 Nov 2014 10:04:55 GMT

Hi Emma,

Metrics work exactly the same way as your expectation here. It the best resort .

Re: Report on time between change 'Review' state and 'Planned Start'

Anurag Tripathi — Thu, 06 Nov 2014 10:06:45 GMT

IF you still don't want to use it, then you can have a simple script written that will serve your purpose. Let me know if you need help with the script.

Re: Report on time between change 'Review' state and 'Planned Start'

ebaileyybs — Thu, 06 Nov 2014 10:07:28 GMT

Hi Anurag,

Thank you for your reply.

Would a script be required to achieve this type of metric?

I have very little knowledge when it comes to scripting so was looking for some advise on how this could be achieved?

Kind regards,

Emma

Re: Report on time between change 'Review' state and 'Planned Start'

ebaileyybs — Thu, 06 Nov 2014 10:08:01 GMT

That would be great if you could help with the script please?

Re: Report on time between change 'Review' state and 'Planned Start'

Anurag Tripathi — Thu, 06 Nov 2014 10:24:16 GMT

just confirming the requirement here. You want to calculate the difference between the planned start date and the actual time when the ticket moved to review.

Write a Business Rule

When : Before

Table: change Request

Condition: current.state.changesTo('Review') //here you will have to provide the numerical value of the state

Script

var getUserDateFormat = gs.getDateFormat();

var dateFormatHelper = new DateFormatHelper();

var currentDate = dateFormatHelper.getDateInUserFormat(gs.now() , getUserDateFormat); //this is the date when ticket moved to review

var planned_start_date = dateFormatHelper.getDateInUserFormat(current.start_date , getUserDateFormat); //planned start date in the specific format

var daysDuration = gs.dateDiff(planned_start_date ,currentDate);

current.<your field where you want to save duration > =daysDuration ;

/* Duration gets calculated in format - days hours mins seconds(25 10:55:52),if you have to extract the day part from it.

dateDifference = daysDuration.split(' ');

This should work

Re: Report on time between change 'Review' state and 'Planned Start'

ebaileyybs — Thu, 06 Nov 2014 11:25:49 GMT

Thank you, I have created the business rule and added a new field to the change_request table named u_lead_time. The field is a string field but I have also tried with Integer and this doesn't work either.

Unfortunately, the 'lead time' field is not populating with the output of the script. Please could you advise if you see any errors or what could be causing this problem?

Please see below condition and script:

Condition: current.state.changesTo('-5')

Script:

var getUserDateFormat = gs.getDateFormat();

var dateFormatHelper = new DateFormatHelper();

var currentDate = dateFormatHelper.getDateInUserFormat(gs.now() , getUserDateFormat); //this is the date when ticket moved to review

var planned_start_date = dateFormatHelper.getDateInUserFormat(current.start_date, getUserDateFormat); //planned start date in the specific format

var daysDuration = gs.dateDiff(planned_start_date,currentDate);

current.u_lead_time =daysDuration ;

/* Duration gets calculated in format - days hours mins seconds(25 10:55:52),if you have to extract the day part from it.

dateDifference = daysDuration.split(' ');

Re: Report on time between change 'Review' state and 'Planned Start'

Anurag Tripathi — Thu, 06 Nov 2014 11:58:24 GMT

Make the Field type duration (glide_duration)

If that dosent work properly then

modify this line of the code

current.u_lead_time =daysDuration.toString() ;

Re: Report on time between change 'Review' state and 'Planned Start'

ebaileyybs — Thu, 06 Nov 2014 14:42:19 GMT

Thank you for your reply and help so far.

I have amended the field to a duration type and I have amended the script as per the above update, however, unfortunately this still does not work.

Is there anything else I need to change to make this work?

Kind regards,

Emma

Re: Report on time between change 'Review' state and 'Planned Start'

Anurag Tripathi — Thu, 06 Nov 2014 14:46:51 GMT

Emma, do only one of the above, not both

lets keep the u_lead_time as string only.

and add the below line to code...

current.u_lead_time =daysDuration.toString() ;

Re: Report on time between change 'Review' state and 'Planned Start'

ebaileyybs — Thu, 06 Nov 2014 15:00:04 GMT

Ok, so I have changed 'u_lead_time' back to string and used current.u_lead_time =daysDuration.toString() ; - this does not work

I have also tried a duration field on 'u_lead_time' with current.u_lead_time =daysDuration ; - this does not work

Apologies, is there anything else I can try to resolve this?

Kind regards,

Emma

Re: Report on time between change 'Review' state and 'Planned Start'

Anurag Tripathi — Thu, 06 Nov 2014 15:36:44 GMT

Hi Emma,

Lets keep things simple and get this done

Business rule script

var planned_start_date = gs.now()

var daysDuration = gs.dateDiff(current.start_date,gs.now());

current.u_lead_time =daysDuration;

/*I have tried this on demo and its works just fine(provided you planned start date has a value, you can add checks).*/

And Lead Time field is 'duration'

You can see the same if you log on to

https://demochannel.service-now.com

Create any change request

Re: Report on time between change 'Review' state and 'Planned Start'

ebaileyybs — Thu, 06 Nov 2014 15:47:24 GMT

Thank you very much! The script has worked

Re: Report on time between change 'Review' state and 'Planned Start'

Anurag Tripathi — Thu, 06 Nov 2014 15:52:00 GMT

Yeah...finally.