Creating a report with case.

[Abhijit Das7]

Hi Everyone,

I want to create a report with case table. I have to show the sum of  time duration for which cases were open i.e duration between opened date and closed date of case.

For example: There are three cases :

Case 1: It was open for 1 Day 2 Hours

Case 2: It was open for 2 Days 2 Hours

Case 3: It was open for 3 Days 2 Hours

So , on report I should be able to display sum of duration of all the three days i.e 6 Days 6 Hours.

Approach 1: 

The approach I am trying is I am writing a run script and populating a string field called normal_case_time on custom table. So,  I am able to populate this field with 1 Day 2 Hours , 2 Days 2 Hours , 3 Days 2 Hours , .. etc . But I am not able to add these values and display on report.

Run Script:

var diff1;

for(var g=0; g<newArr.length ; g++){ // newArr is array which has list of some specific cases

var trend = new GlideRecord("sn_customerservice_case");
trend.addQuery("number" , newArr[g] );
trend.addEncodedQuery('state=3');
trend.query();
if(trend.next()){

var start = trend.getValue("opened_at");
var end = trend.getValue("closed_at");

var date1 = new GlideDateTime(start);
var date2 = new GlideDateTime(end);
diff1 = GlideDateTime.subtract(date1, date2);

var sr = new GlideRecord("x_case_handling");
sr.initialize()
sr.setValue("normal_case_time", diff1.getDisplayValue()); // normal_case_time is a string field
sr.insert();
}
}

Please guide me for my requirement. If I can achieve this with my approach1 then please suggest me,  other wise if any other approach with which I can achieve please specify that.

Thanks in advance. 

[AndersBGS · Accepted solution]

Hi @Abhijit Das7 ,

Why not utilizing the duration field on the case, and afterwards create a report to sum the duration?

If my answer has helped with your question, please mark my answer as accepted solution and give a thumb up.

best regards

Anders

[AndersBGS · Accepted solution]

If my answer has helped with your question, please mark my answer as accepted solution and give a thumb up.

best regards

Anders

[Abhijit Das7]

Hi @AndersBGS 

Duration field in case table is empty;

Thanks

[AndersBGS]

Hi @Abhijit Das7 ,

Agree, but you could write a business rule that calculate the duration that you would like and populate the filed. 

If my answer has helped with your question, please mark my answer as accepted solution and give a thumb up.

best regards

Anders