- Dynamic parameter in Crystal Report fails with "Failed to open rowset"
- Scheduled Crystal Reports List of Values (LOV's) gets error "Failed to open rowset"
- Previously, the dynamic parameters and scheduled LOV's were working with no errors.
- Crystal Reports XI R2
- BusinessObjects Enterprise XI R2
- Reporting Database: Oracle 10g with Oracle native connection
Reproducing the Issue
- In Crystal Reports, refresh a report with a Dynamic Parameter and get error "Failed to open rowset"
- In Business View Manager, schedule a List of Values. The schedule fails with error "Failed to open rowset"
When a Crystal Reports List of Values is generated, it creates SQL that includes Select Distinct and Order by. When run from a Windows client, the sql returns with no errors. On the unix server, however, the SQL generated gets Oracle error 1791: not a SELECTed expression
Cause: There is an incorrect ORDER BY item. The query is a SELECT DISTINCT query with an ORDER BY clause. In this context, all ORDER BY items must be constants, SELECT list expressions, or expressions whose operands are constants or SELECT list expressions.
On the Oracle 10g server, case insensitive search was enabled by setting the following variables:
Prior to the case insensitive search being set, the LOV's and dynamic parameters were working correctly.
Change the Oracle variables to the following:
Use the Oracle Wire Protocol odbc driver on the Unix server.
ora-1791, Select Distinct, Order by, DCP, , KBA , BI-RA-CR , Crystal Reports designer or BusinessViews Manager , Problem