Streamlining SQL Queries

[cmichailovlee]

When creating a Crystal Template that needs to interface through a SQL ODBC, if you go to Database->SQL Query (running Crystal Reports 10), it will pop up with the SQL script that Crystal created in order to interact with the database.

However, depending on the complexity of the query, the SQL query may not be optimized the way it should be so pulling and compiling reports takes a long time to do.

If I go over to SQL Query Analyzer, I can create the SQL code by hand and have it return results much faster. But is there ANY way to have the SQL query I've created somehow imported or used by Crystal Reports instead of the SQL Query it generates on its own?

 

[vidru]

The lack of a WHERE clause in the Show SQL Query window is usually due to the structure of the Record Selection Formula. Have a look at synapsevampire's FAQ on this: faq766-3826.

Alternately, since you're using CR10, you have the ability to use actual SQL when designing the report using a Command. In the Database Expert, once you've browsed to your data source, you'll see an Add Command option. If you choose that, you can write your own SQL (as complex as you want), which becomes sort of an on-the-fly stored procedure.

Using stored procedures and/or predesigned Views that already link your tables efficiently are another option as well.

-dave