Symptom
- 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.
Environment
- Crystal Reports XI R2
- BusinessObjects Enterprise XI R2
- Reporting Database: Oracle 10g with Oracle native connection
- Unix
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"
Cause
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:
NLS_COMP=LINGUISTIC
NLS_SORT=BINARY_CI
Prior to the case insensitive search being set, the LOV's and dynamic parameters were working correctly.
Resolution
Change the Oracle variables to the following:
NLS_COMP=ANSI
NLS_SORT=BINARY_CI
or
Use the Oracle Wire Protocol odbc driver on the Unix server.
Keywords
ora-1791, Select Distinct, Order by, DCP, , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , Problem