Scheduled report with subreports

Brian Lancaster
Kilo Patron

3 hours ago

I have created 4 scheduled reports only one of which has a schedule. The others are setup as included with on the primary report. We have done this in the past with no issue. For some reason with this one all the excel files that are attached are the same which is the parent scheduled report. The only thing different is that we are using a script include found in this article to produce the reports.

https://www.servicenow.com/community/developer-blog/and-the-winner-is-randomly-selecting-gliderecord...

 

Any idea on why this would be happening?

0 Helpfuls
  • 157 Views
1 REPLY 1

Itallo Brandão
Tera Guru

32m ago

Hi Brian,

Bingo! The article and code you shared confirm exactly Hypothesis 1 (Session Caching).

The Root Cause: The author of that script intentionally added a "caching mechanism" to handle a specific UI quirk where Scripted Filters run twice in the browser (once for the list, once for the breadcrumb).

Look at these lines in the code provided in the article:

  1. var randomSampleRecords = []; (Declared outside the function, making it global/persistent in the thread).

  2. if (randomSampleRecords.length) return randomSampleRecords; (If data exists, stop and return it).

What is happening in your Scheduled Report:

  1. Report 1 (Parent) runs: The array is empty. It generates random IDs. It saves them to randomSampleRecords.

  2. Report 2 (Sub-report) runs: It checks randomSampleRecords. It sees it is full (from Report 1). It returns the exact same IDs instantly.

The Fix

To make this work for Scheduled Reports (where you need a fresh calculation every time), you must disable this caching logic.

Open your Script Include and comment out the lines related to randomSampleRecords as shown below:

 
// var randomSampleRecords = [];   // Comment this line out!
function randomSample(tableName, encodedQuery, sampleSize, fieldName) {
   // if (randomSampleRecords.length) return randomSampleRecords;   // Comment this line out to force NEW random data every time!
   
   try {
       // ... rest of the code remains the same ...
       var isScriptedFilter = !this.GlideRecordSecure; 
       var gr = new GlideRecord(tableName);
       // ... (middle of the script) ...
       
          if (gr.next()) {
             var value = gr.getElement(fieldName).toString();
             // use a local array "records" instead of global check here if needed, or keep logic as is
             if (indexOf(records, value) < 0) records.push(value);
          }
       }
       // if (isScriptedFilter) randomSampleRecords = records;   // Comment this line out!
       return records;
   }
   catch(e) {
       return 'ERROR: ' + e; 
   }
   
   function indexOf(arr, val) { for (var i = 0; i < arr.length; i++) if (arr[i] == val) return i; return -1; }
}

Trade-off: By removing this cache, if you use this filter in a live interactive List View (UI), you might see a mismatch between the records in the list and the IDs shown in the breadcrumb text. However, for Scheduled Reports (Excel/PDF exports), this is irrelevant, and this change will guarantee unique random data for every sub-report.

If this code adjustment solves the duplicate data issue, please mark it as Accepted Solution.

Best regards, Brandão.

0 Helpfuls