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

Document ID : KB000004117
Last Modified Date : 14/02/2018
Show Technical Document Details
Issue:

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'

Multi_Select_Resource_Manager_1000_Plus.png

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 (?,?,?))

Multi_Select_Resource_Manager_1000.png

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.