Crystal Reports parameterized queries

Question

The company I work for is using MacolaES for an ERP system. The SQL Server database is structured such that when things are no longer considered active, they are moved from a set of "active" tables to a set of "history" tables. This helps to keep the "active" tables small enough that queries return quickly. On the flip side, the history tables are enormous. The appropriate columns are indexed in these tables, and if you query for something specific it returns quickly.

The problem is when you make a Crystal Report, which is prompting the user for a parameter. For reasons not known to me, Crystal parameters are not translated into SQL parameters, so you end up with queries selecting everything from the order header history table inner joined to everything in the order lines history table, which results in over 8 million rows.

Is there a way to get Crystal Reports to use the parameters in the SQL query instead of loading all the records and filtering after the fact? I read somewhere that a stored procedure should work, but I'm curious if an ordinary parameterized query is possible in the interest of saving my time.

Here is the selection formula:

(
    trim({Orderheader.ord_no}) = {?Order No}
)
and
(
    {Orderheader.ord_type} = 'O'
)
and 
(
    {orderlines.ord_type} = 'O'
)

Answer 1

In Crystal Reports top menu go to Report / Selection Formulas / Record... There you can add a formula similar to:

{table.field1} = {?Parameter1} and {table.field2} = {?Parameter2}

That will add the condition to the where statement of the SQL query that the report will use to pull the rows.

To verify what is the condition in the where statement that the report is using to pull the data you can go to the menu database / Show SQL Statement. That way you can verify that the report is using the parameters in the filter.

Crystal Reports 8.5 User Guide mention the following tips:

To push down record selection, you must select “Use Indexes or Server for Speed” in the Report Options dialog box (available on the File menu).

In record selection formulas, avoid data type conversions on fields that are not parameter fields. For example, avoid using ToText( ) to convert a numeric database field to a string database field.

You are able to push down some record selection formulas that use constant expressions.

Your formula has a TRIM function on a field. The function against the field does not allow Crystal to push the formula to the database because is not a constant expression.

If you really need to trim the order number field you should do it using SQL Expressions.

References: Check out this article.