Reporting date range to span working days only

[domaners]

Hi all. I've been asked to create a report that shows active Incidents that haven't been updated for two days. This will be sent as a scheduled report daily to a manager, however Monday and Tuesday's reports will be massive as most Incidents won't be updated over the weekend!

Is there a way to create a query along the lines of "Updated > relative > at or before > 2 WORKING days"?

If that's not possible, I wouldn't mind using a Script Include to check the day of the week and change the date window accordingly. The only issue with that is you can't embed Javascript into a date field condition in the same way that you can with a reference field. Somebody correct me if I'm wrong on that?

If none of the above is possible, I guess I'll have to create separate scheduled report for Monday and Tuesday, but as a 21st century analyst I don't want to go down this archaic route...!

[tltoulson · Accepted solution]

Hi Adam,

You have a few options on this one:

Dynamic Filters

If you are using Dublin or better then you can capitalize on Dynamic Filters.   You would need to write a script (most likely a script include) that calculates the date 2 working days prior.   You could either brute force that calculation (if monday / if tuesday / else) or use a Duration Calculator with a Calendar.   I haven't done a dynamic filter for dates just yet so I oddest have an example handy but let me know if you need more help and I'll check it out.

Administrative Hack

Another option is to change the condition on the report record using a background script.   Date fields on the condition builder don't let you use a "javascript:" style condition but if you update the record with a script, it will actually work.   Condition fields store the filter string (ie. active=true^sys_updated_onONLast 7 days@javascript:gs.daysAgoStart(7)@javascript:gs.daysAgoEnd(0)), so you just have to insert a filter script into the filter field in a background script like so:

var gr = new GlideRecord('sys_report');

gr.get('sys id of your report here');

gr.filter = 'active=true^sys_updated_onONLast 2 business days@javascript:someScriptInclude.businessDaysAgoStart(2)@javascript:javascript:someScriptInclude.businessDaysAgoEnd(0)';

gr.update();

Disadvantage: Tougher to update and the Condition Builder looks odd

Separate Scheduled Reports

Yeah... sometimes we have to resort to hamsters on a wheel.

Hope this helps!

Kind regards,

Travis

[domaners]

Hi Travis, thanks for the detailed list of options. I tried the dynamic filters first but I don't seem to get the is (dynamic) option when selecting the Updated field on the report condition builder.

I will try the hack instead. For me, an easier way of doing this would be to export my report as an XML file and add the javascript line using Notepad++ or some other text editor, then re-importing.

Will let you know if I come unstuck!

[domaners]

I've managed to get this working using the hack idea. I've now set up a basic script include called workingDaysAgo that takes a number of days as an argument, calculates the result, and adds on two days if the resulting day is Saturday / Sunday.

[Krishna73]

Hi All,

I have a similar requirement(Cases last worked on) to show the report on Manager Dashboard for last 5 business days.

Help me to configure this using script includes.

Note: Not able to achieve using the Hack method.

Thanks,

Krishna