Reporting date range to span working days only

domaners · ‎01-09-2015

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 · ‎01-09-2015

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

View solution in original post

lana5 · ‎09-21-2021

Did you manage to get it working? I have a similar request