Data missing on Scheduled Reports


Description

The scheduled report is configured to be sent via email on daily basis. However, most of the time the report does not contain the data in the attachment. 

Resolution

The cause of the failure is because of report is exported in Excel 2000 (.xls) format, which has a limitation (max. 256) on column number, and the report has more columns than that. But the real problem is that we support exporting reports to Excel 2003 (xlsx) format, whose column number limit is 65536 so no report will exceed it:

We have a choice list for Excel (.xlsx) in the OOB instance but this option is missing on the problematic instance :

https://instance.service-now.com/sys_choice_list.do?sysparm_query=sys_id%3D0abd019d7fb01200f936dd620efa91d1&sysparm_first_row=1&sysparm_view=

Below exception can be seen in app node logs during the scheduled job execution : 

2021-01-29 18:00:10 (662) worker.7 worker.7 txid=a7494a01db06 *** Start Background transaction - system, user: system
2021-01-29 18:00:10 (668) worker.7 worker.7 txid=a7494a01db06 Starting: Scheduled export of incident data.0260fadedbbd20105569121d1396196b, Trigger Type: Daily, Priority: 100, Upgrade Safe: false, Repeat:
2021-01-29 18:00:10 (668) worker.7 worker.7 txid=a7494a01db06 Name: Scheduled export of incident data
Job Context:
#Thu Jan 28 18:00:09 PST 2021
fcDocumentClass=sysauto_report
fcDocumentKey=9a9f86371b2d28501bb30f2d6e4bcb8a

Script:

2021-01-29 18:00:10 (897) worker.7 worker.7 txid=a7494a01db06 SEVERE *** ERROR *** Invalid column index (256). Allowable column range for BIFF8 is (0..255) or ('A'..'IV')
java.lang.IllegalArgumentException: Invalid column index (256). Allowable column range for BIFF8 is (0..255) or ('A'..'IV')
at org.apache.poi.hssf.usermodel.HSSFCell.checkBounds(HSSFCell.java:982)
at org.apache.poi.hssf.usermodel.HSSFCell.<init>(HSSFCell.java:153)
at org.apache.poi.hssf.usermodel.HSSFRow.createCell(HSSFRow.java:159)
at org.apache.poi.hssf.usermodel.HSSFRow.createCell(HSSFRow.java:114)
at org.apache.poi.hssf.usermodel.HSSFRow.createCell(HSSFRow.java:38)
at com.glide.processors.ExcelSpreadsheet.writeHeader(ExcelSpreadsheet.java:553)
at com.glide.processors.ExcelSpreadsheet.createSheet(ExcelSpreadsheet.java:268)
at com.glide.processors.ExcelSpreadsheet.writeDocument(ExcelSpreadsheet.java:221)
at com.glide.processors.ExcelSpreadsheet.createDocument(ExcelSpreadsheet.java:200)
at com.glide.processors.ExcelSpreadsheet.createDocument(ExcelSpreadsheet.java:179)
at com.glide.generators.ExcelExporter.createDocument(ExcelExporter.java:88)
at com.glide.generators.ExcelExporter.generate(ExcelExporter.java:74)
at com.glide.generators.AbstractGridExporter.generate(AbstractGridExporter.java:67)
at com.glide.report.Report.getExcelReport(Report.java:1061)
at com.glide.report.Report.genReport(Report.java:884)
at com.glide.report.Report.getInputStream(Report.java:1654)

In order to overcome this situation, either import the attached missing choice list in an affected instance or as a workaround try to reduce the column number in a report to be no greater than 256.