Over Under Allocation by Resource Jaspersoft Report throws an ORA-01795 Error

Document ID : KB000120946
Last Modified Date : 13/11/2018
Show Technical Document Details
Issue:
When all the Resource Managers are chosen in Resource Manager input control in the Over/Under Allocation by Resource CA PPM Jaspersoft report, there is an error raised: Caused by: java.sql.SQLSyntaxErrorException: ORA-01795: maximum number of expressions in a list is 1000.
Environment:
CA PPM Jaspersoft
Cause:
This is related to CLRT-80696 - Server error "Error filling report" displayed when running OOTB Jaspersoft report in 6.2.1 (ORA-01795: maximum number of expressions in a list is 1000). This happens in CA PPM Jaspersoft reports  when the user selects all Resource Managers in a large dataset. The error is due to an Oracle limitation: https://community.oracle.com/thread/235143?start=0
Resolution:
Adjust your input control selections so that the number of items meeting the criteria for any one of them is not over this threshold, and try again. If you still have the issue after that, please try to schedule the report to make sure you are also not exceeding a 500 pages limit that may also result in problems. The scheduled report can exceed this limit, but the one viewed immediately in the UI cannot.