Export of Jaspersoft report in XLSX format has space in place of Null

Document ID : KB000097698
Last Modified Date : 23/05/2018
Show Technical Document Details
Issue:
When jaspersoft reports are run from Reports And Jobs menu in PPM and is exported to XLSX format, any null value in that exported XLSX is having special characters. 
The same does not occur, if the report output is exported in XLS format, from Reports And Jobs page in PPM. The behavior is the same, in Advanced Reporting

Steps to reproduce: 
  1. Log onto to PPM and navigate to Home >> Personal >> Reports and Jobs link 
  2. Select the Data Warehouse Schema report 
  3. Default the report output to XLSX format, in the Data Warehouse Table format, use any table, for example -CMN_INDX_MONITORING_V 
  4. Execute the report and download the report in XLSX format 
  5. Open the outputted report, if the report is having null values, for example under the Comments section, input the following Excel formula -> ISBLANK(column). For example if in the output the Comments column is J and the values are starting from Row 4, the formula will be -> =ISBLANK(J4) 
  6. Repeat the step 5, for a report output , which was downloaded in XLS format 
Expected Results: In the Excel the result of the formula should be True in the XLSX format 
Actual Results: In XLSX format, the result of the formula is FALSE, which shows that there are special / white space characters. In the XLS format the result is correctly TRUE. The behavior is the same under Advanced Reporting 
Environment:
CA PPM 15.3, 15.4
Cause:
This is a known issue was raised as DE40357. 
Resolution:
This is not an issue with CA PPM and JasperSoft reporting tool. When you use the ISBLANK() function in a Microsoft Excel spreadsheet, the result is "FALSE". 

This behavior occurs even though the cell appears to be empty. Additionally, this behavior occurs even though the formula bar may show that nothing is in the cell.Cause: This behavior may occur when the cell contains a zero-length string. 

See this link: The result is "FALSE" when you use the ISBLANK() function in an Excel spreadsheet

Workaround: LEN function can be used instead of ISBLANK().