How to Resolve HCRException During Canned Report Preview When Special Characters Are Present in Data

Technical queries
, , , ,
#1

Hello Team,

When special characters are present in the data, the SQL query executes successfully in the data source, but the Canned Report preview fails with the following error:
HCRException: com.helicalinsight.efw.exceptions.HCRException: / by zero, / by zero

How can we resolve this issue?

Thank You,
Snow.

#2

Hello Snow,

This issue can be resolved by removing non-printable characters from the columns containing special characters using the REGEXP_REPLACE() function in the SQL query.

Use the following expression for the relevant column:

REGEXP_REPLACE(

IFNULL(oi.DESCRIPTION, ‘’),

‘[^[:print:]]’,

‘’

) AS DESCRIPTION

Explanation:

  • IFNULL() replaces NULL values with an empty string.

  • REGEXP_REPLACE() removes non-printable characters.

  • [^[:print:]] identifies non-printable characters.

  • AS DESCRIPTION retains the column alias.

Apply this expression to the relevant columns containing special or non-printable characters in your query.

Replace oi.DESCRIPTION with the actual table alias and column name from your query.

Note: The regular expression syntax may vary depending on the database being used.

Thank You,
Helical Insight.