Multi-select filters and input controls not populating for Out of the Box JasperSoft reports

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

Inconsistent JasperSoft Report behavior when populating input control with Multi-select filters:

Example:

- Where Resources not being populated for ALL 1000+ selected 'Resource Managers'

- Or Report not being generated for ALL / 1000+ selected 'Resource Managers'

<Please see attached file for image>

Environment

Impacts both On Premise & On Demand implementations with Oracle Database.

Cause

Error reported under Jasperserver.log:

ORA-01795 maximum number of expressions in a list is 1000.

 

Resolution

Recommendation is User does not need to select ALL Input controls or Multi-select filters that are not required.

a) JasperSoft understands that if you are not selecting any entry from a multi-select list you want them all; it is like running the report wide open. In this case the syntax is translated as null (AND 0 = 0)

b) When you select entries; JasperSoft builds an IN clause including all selected entries. In this case the syntax is (AND i.investment_status_key IN (?,?,?))

<Please see attached file for image>

src="/servlet/servlet.FileDownload?file=0150c000004AK7TAAW" alt="Multi_Select_Resource_Manager_1000.png" width="304" height="307">

c) Selecting ALL entries in non required filters (input controls) just makes it worse in terms of usability and also in terms of performance. Which can lead to other issues since there is a known limitation with the IN clause in Oracle.

Additional Information

NOTE: Microsoft SQL Database Server doesn't have this limitation.