We have a report that is not returning all the data we want. I’ve found the problem in Show SQL. The WHERE clause contains an IN statement which excludes some of the wanted data. But the Record Selection Formula doesn’t contain any criteria on this field.
This report only queries one table, so it is not a join issue. There are several groups, formulas and summary fields though.
Where else could that criteria be coming from? Is there an easy way to clean up possible corruption so that the SQL query WHERE clause is based only on the Record Selection Formula?
EDIT:
My solution to this was to create a new report hitting the same table with the same groups, then copy & past all of the fields over. Ive done this before with reports that simply dont behave properly, or crash when run. I dont believe anything other than selection formulas affects the WHERE clause, so this was a corrupted report.
There’s a way the SQL querry can be modified. When you do a “create new connection” in you Crystal, you have the option to choose a lot of type of database, but you also can choose the “Add Command”. By using the “Add Command”, you can create your own SQL querry so you can add a WHERE statement even if there’s nothing in your selection criteria.
To look if you can change your SQL querry in Crystal XI, open your report and then go in “Report” then “Database Expert”. You will see your databases there. Right click on the one you want, and if you have only these options :
Rename F2
Properties …
*this means that you cannot change the SQL querry
if you have these options :
Edit Command
View Command
Add to Reporsitory…
Disconnect from repository
*this means that you can edit this command!
This may be a solution why you have a WHERE statement even if you didn’t put anything like this in your selection criteria.
Many of the Options/ Report Option settings give you an opportunity to fine tune your report. The let you decide whether work should be done locally or on the server.
As a rule of thumb, if you put something into the Record Selection Formula (Select Expert) that is too complicated to put in an SQL command, it will be done locally. Also, many of the checkboxes on the Database tab like Use Indexes or Server for Speed, Perform Grouping On Server, and Perform Query Asynchronously, all help move processing to the server and modify the SQL.
I am not sure which one would be causing an IN portion of the WHERE clause. This might also be due to a translation between Crystal and the Database driver. Using a native driver may cause extra translations. I have used ODBC extensively and don’t recall a modification to the WHERE clause of an IN.
Sorry, my post was was not technically accurate. There is not an IN cause in the SQL query, but TSQL that might have been derived from an IN statement in the Record Selection formula. The WHERE statement contains criteria that WAS in the current record selection formula, then at the and of it was
AND (“TABLENAME”.“FIELDNAME” = ‘Value 1’ OR “TABLENAME”.“FIELDNAME” = ‘Value 2’ OR “TABLENAME”.“FIELDNAME” = ‘Value 3’ OR
“TABLENAME”.“FIELDNAME” = ‘Value 4’)
The values are actual values that exist in this field, but not all the values that are in records that were wanted in the report. Therefore they were excluded. Two of these values could not be found in any formula in the report so I had no way to remove them.
So I suspect this was part of the selection formula in the past, but was later removed, but not completely removed due to file corruption.
I looked into the Add Command option. I know I could have replaced the table with my own SELECT statement, but it looked that this would have led to rebuilding the entire report. My option, copying the objects to new report, took less then 30 minutes.
(BOB member since 2008-07-15)