Custom report fails with "ORA-02019..." after refresh

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

After refresh, a custom report based on the Project Storyboard report fails with error: 

2020-08-19 12:38:13,718 ERROR AsyncJasperPrintAccessor,pool-6-thread-228:321 [tenant|admin] - Error during report execution

net.sf.jasperreports.engine.JRException: Error executing SQL statement for: CSK_PRJ_ProjectStoryboard.

            at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:347)

Caused by: java.sql.SQLSyntaxErrorException: ORA-02019: connection description for remote database not found

Environment

Release : Any

Component : CLARITY JASPERSOFT

Cause

This happens if the database link to PROD DWH has been hardcoded in the main custom report (example):

i.test as TEST from [email protected]

We do not recommend hardcoded database links to be included in custom reports.

Resolution

Check the report queries by exporting the report or looking in Studio to see if the above is the cause. If yes, work with your report developers to use a variable or a parameter in the query. Otherwise, every time you refresh the environment the reports will break. 

This is an example of how the report can be coded:

<parameter name="LoggedInUserAttribute_dwhDBLink" class="java.lang.String" isForPrompting="false"> <parameterDescription><![CDATA[Data Warehouse DB Link Profile Attribute]]></parameterDescription> </parameter> <parameter name="dwhDBLink" class="java.lang.String" isForPrompting="false"> <parameterDescription><![CDATA[Data Warehouse DB Link to CA PPM DB]]></parameterDescription> <defaultValueExpression><![CDATA[$P{LoggedInUserAttribute_dwhDBLink}]]></defaultValueExpression> </parameter>

Additional Information

See also: Jaspersoft / reports known issues with Clarity