How to schedule a semi-monthly report with date-based records and email attachment?

Gurucharanc
Tera Contributor

3 weeks ago

Hi Community,

I have a requirement in ServiceNow where I need to send a report by email on the 1st and 15th of every month.

The requirement is:

  • On the 1st of the month, I need to send all records that were created from the 15th of the previous month through the 1st of the current month.

  • On the 15th of the month, I need to send all records that were created from the 2nd through the 14th of the current month.

  • The report should contain the required records in an Excel attachment.

  • I already have a Notification/Email Template that needs to be used for sending the email.

  • The Excel file should be attached to the email and sent to specific recipients.

For example:

October 1st:
Records created from September 15th → October 1st

October 15th:
Records created from October 2nd → October 14th

I am considering using a Scheduled Script Execution to run the process and dynamically calculate the date range, retrieve the records, generate the Excel attachment, and trigger the notification.

Could someone please suggest the recommended ServiceNow approach for implementing this requirement?

Specifically, I would like to understand:

  1. How should I configure the Scheduled Job to run on the 1st and 15th?

  2. How can I dynamically calculate the required date range?

  3. What is the recommended way to generate the Excel report?

  4. How can I attach the generated Excel file to the email?

  5. How can I trigger the existing Notification/Email Template after the attachment is created?

  6. Is there a better/recommended approach than Scheduled Script Execution for this requirement?

Any guidance or example implementation would be greatly appreciated.

Thank you.

0 Helpfuls
  • 375 Views
1 REPLY 1

vijaydodla
Giga Guru

3 weeks ago

While using a Scheduled Script Execution is a valid way to meet your exact requirements (especially since you want to use an existing Notification/Email Template), it requires complex scripting to generate a binary Excel file.

ServiceNow best practice strongly recommends using Scheduled Reports combined with a Dynamic Filter Option for this type of requirement.

 

Below, I have detailed both approaches: the Recommended OOTB Approach (No-Code/Low-Code) and the Scheduled Script Execution Approach (Custom Scripting).

 

The Recommended OOTB Way is using Scheduled Reports + Dynamic Filter

Instead of scripting the Excel generation, you can use ServiceNow's native reporting engine. You will create a script that dynamically calculates the date range, use it as a filter in a standard report, and schedule it.

Note: The only tradeoff here is that you will need to copy your existing Email Template's text into the Scheduled Report's "Introductory message" field, rather than using the Notification record.

Step 1: Create a Script Include for the Date Logic

  1. Navigate to System Definition > Script Includes.
  2. Create a new Script Include named CustomReportDates.
  3. Check Client callable.
  4. Use the following script to calculate the dynamic ranges

var CustomReportDates = Class.create();
CustomReportDates.prototype = {
initialize: function() {},

getBiMonthlyRange: function() {
var gdt = new GlideDateTime();
var day = gdt.getDayOfMonthLocalTime();
var start = new GlideDateTime();
var end = new GlideDateTime();

if (day <= 14) {
// Run on the 1st: 15th of previous month to 1st of current month
start.addMonthsLocalTime(-1);
start.setDayOfMonthLocalTime(15);
end.setDayOfMonthLocalTime(1);
} else {
// Run on the 15th: 2nd to 14th of current month
start.setDayOfMonthLocalTime(2);
end.setDayOfMonthLocalTime(14);
}

// Return the encoded query string for the BETWEEN operator
return 'sys_created_onBETWEENjavascript&colon;gs.dateGenerate("' + start.getDate() + '","00:00:00")@javascript&colon;gs.dateGenerate("' + end.getDate() + '","23:59:59")';
},
type: 'CustomReportDates'
};

 

Step 2: Create a Dynamic Filter Option

  1. Navigate to System Definition > Dynamic Filter Options.
  2. Create a new record:
    • Label: Bi-Monthly Report Range
    • Script: new CustomReportDates().getBiMonthlyRange();
    • Field Type: Date/Time
    • Available for filter: Checked

Step 3: Create the Report and Schedule It

  1. Create your report. In the filter conditions, set: [Created] [is (dynamic)] [Bi-Monthly Report Range].
  2. Navigate to Reports > Scheduled Reports.
  3. Create two schedules for this report:
    • Schedule 1: Run Monthly, Day 1.
    • Schedule 2: Run Monthly, Day 15.
  4. Set the Type to Excel.
  5. Add your recipients and paste your email template content into the Introductory message.

 

Please mark solution accepted if it aligns with your requirements.

0 Helpfuls