Clarity: Filtering of Jaspersoft Adhoc View breaks if you create a filter using Multivalue lookup attribute using DWH as datasource

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

 

STEPS TO REPRODUCE

1. Login to clarity and go to administration 

2. Under any object and I took resource object create an attribute  Multi Valued Lookup – Number and use look up All Resources Browse and include in DWH

3. Now go to resource list and populate values against this attribute and ensure to select at least two values 

4. Run the Load DWH job and ensure it completes

5. Build an adhoc view using Resource Management domain 

6. Pull the newly created attribute in the list view of adhoc view 

7. Now add the newly created attribute to filter

8. Apply the filter and you will get error 

9. Looking at jaspersoft log and it generated  the below query 

 

select * from ( select "DWH_RES_RESOURCE"."RESOURCE_NAME" as "DWH_RES_RESOURCE_RESOURCE_NAME", 

"DWH_RES_RESOURCE"."Z_RESMAN" as "DWH_RES_RESOURCE_Z_RESMAN"

from "DWH_1561_GA"."DWH_RES_RESOURCE" "DWH_RES_RESOURCE"

inner join "DWH_1561_GA"."DWH_RES_SECURITY_V" "DWH_RES_SECURITY_V" on (("DWH_RES_RESOURCE"."RESOURCE_KEY" = "DWH_RES_SECURITY_V"."RESOURCE_KEY"))

where (("DWH_RES_SECURITY_V"."USER_UID" = 'admin') and ("DWH_RES_RESOURCE"."Z_RESMAN" = 'Bauer, Joyce; Beera, Szzz'))

) where ROWNUM <= 1000

Expected Results: The user should be able to filter based on Multi value lookup 

Actual Results: User gets an error 

 

 

Environment

Release : 15.6.1

Component : CA PPM SAAS JASPERSOFT

Cause

The multi value attribute in dataware house is stored with a semi colon between and Jaspersoft treats Semi colon as SQL injection and stops the request. 

Resolution

This was raised as potential defect DE50559, however based on further analysis its not advisable to exclude semicolon from the SQL injection check and this is a limitation in Adhoc View. The workaround would be to create a Jaspersoft Studio report.