duration fields in export to excel

[auntyem]

I've already found this in the wiki:

5.1 Excel
Date, Date-Time, and Time fields are all exported as their display values, displayed using a custom format instead of the system date format. Duration fields, however, export as the value stored in the database, which is an integer value of seconds.

The last part about duration fields in seconds is REALLY undesirable; customers are complaining that the report is useless. Is there a way to configure this to be something friendly like hours?

Thanks!

[geoffcox]

Since the duration field exports as seconds, we find that we often have to add another field to the table which is a copy of the duration field but in days (displayed as a decimal). Then a business rule has to calculate this field's value whenever the record is updated.

Duration calculation is so common that we have developed a separate script include for this purpose.
The before update business rule contains:

    var td = new CalculateDuration();
    var duration = td.calculate_duration(start,end);
    current.u_task_duration = duration.duration;
    current.u_task_duration_in_days = duration.days;

start and end should contain whatever you want the duration calculated upon, for example:

start = current.sys_created_on.getDisplayValue();
end = current.work_end.getDisplayValue();

The script include contains:

gs.include("PrototypeServer");

var CalculateDuration = Class.create();

CalculateDuration.prototype = {
    calculate_duration : function(start,end) {
        var duration = new Object();
        duration.duration = gs.dateDiff(start,end,false);
        var seconds = gs.dateDiff(start,end,true);
        if (seconds < 0) {
            seconds = 0;
        }
        duration.days = parseInt(seconds * 100 / 86400,10)/100;
        return duration;
    }
};

[auntyem]

Thanks Geoff. I suspect this is the best answer that I'm going to get, although I hope the ServiceNow folks are noting this is a much-desired enhancement to be able to set the format of exported duration fields.

[scotty81]

Geoff/Mary,

Not sure if anyone from SN is watching this thread, but I just wanted to echo Mary's comments.   SN does do a good job in reporting duration fields using a dynamic format (i.e. reporting duration in minutes, when it is only minutes, and then switching to hours or days, when it is longer).   But, we still export some SN reports for further analysis and data cutting, which are external to the SN environment.   And, when one exports these SN reports to Excel, the dynamic formatting is lost, and the duration field is displayed in seconds, which I believe what SN is using to calculate duration internally.

One can create another column in Excel to convert the duration field (in seconds) to something more palatable like what SN does in its native reports.   But, this does require extra processing.   What our users would really like is the means to export the report data without resorting to any extra processing.   Sort of like WYSIWYG.   That would really add more value vs. trying to interpret that 172,800 seconds is 2 days.

I do appreciate the workaround that Geoff proposed, but I would rather much spend my coding, and testing resources on other items and not have to add extra engineering into something that I think SN should do out of the box.

Regards,

Scotty81

[DhananjayM]

Completely agree with Cory its high time SN realizes value of its reporting. We have similar feature and now users are just confused with the numerical representation of duration fields.

Is there a way to upvote this thread and bring to SN notice?

Regards,

Dhananjay