Optimizing SQL pass through using the Record Selection Formula

This FAQ is maintained in the Crystal Formulas forum FAQs, pleas check there for the latest information.

Creating a Record Selection Formula to pass SQL to the database, thus optimizing performance.

Ultimately, I would use a Stored Procedure in lieu of a Record Selection Formula, assuring that the processing is performed on the database, and with most large scale databases, gain the advantages of a precompiled execution plan. However using Views (never use tables directly!) lends itself to reusability and maintenance, and you have more control over parameters if they are Crystal generated.

First, hereÆs a link to the Crystal Decisions whitepaper on the topic:

<http://support.crystaldecisions.com/communityCS/TechnicalPapers/scr_recsel.pdf>;

Having been very dissatisfied with their explanation, and having significantly more complex Record Selection Formulas, I spent a lot of time experimenting, the results of which I share here:

Overview:

The steps to create most of the objects/formulas referenced here are described at the bottom of this FAQ under Reference.

The Record Selection Formula can be passed to the database providing you are using a database which supports it, this means SQL databases, and others if they have a connection which supports it. One example of a non-SQL database which supports it is using MS Access under ODBC.

The Record Selection Formula is constructed using Crystal syntax, or Crystal Basic syntax.

All of my examples use Crystal syntax.

One means to facilitate a pass through is to use SQL Expressions (recent versions of Crystal Reports) to use the underlying databases SQL to create a field that lends itself to your requirements.

An example might be to convert a numeric type field containing a date to a datetime field:

cast(cast(numericdate as varchar(8)) as datetime)

The above assumes a format of YYYYMMDD in the numeric field.

Tricks for ensuring pass through SQL:

-Wrap all discreet pass through clauses in parentheses.
-Test after each part of the Record Selection Formula is created to determine that everything is being passed.
-Never reference formulas which use variables.
-If when using a Crystal function it fails to pass the SQL, write you own functionality.

Creating a basic Record Selection Formula on a Record basis:

A common scenario is to pass a date range to a View:

Create a Parameter of type date or datetime as is appropriate for your user and data type. You do NOT have to match the parameter data type to the database data type to assure that SQL will be passed to the database, however you may have to use formulas to convert a DATE to a DATETIME to pass it.

Date to Datetime Record Selection Formula:

This is common because it allows for a friendly user imput, but if the database is a datetime, it may not pass the SQL, again, make sure that you check the Show SQL to assure this.

(
{table.datetimefield} = {?dateparm}
)

Given just one parameter being passed, Crystal will likely do a good job of passing this as SQL, however if you have numerous parameters, you may find that Crystal doesnÆt pass it, annoying, but consistent with how inconsistent Crystal is with pass through SQL.

If you discover that Crystal has stopped passing the SQL for something akin to the above, create 2 formulas, as in:

@startdate
cdate(year(minimum({?MyDateParm})), month(minimum({?MyDateParm})), day(minimum({?MyDateParm})),0,0,0)

@enddate
cdate(year(maximum({?MyDateParm})), month(maximum({?MyDateParm})), day(maxnimum({?MyDateParm})),0,0,0)

Then reference the formulas in the Record Selection Formula (record):
(
{table.datetimefield} >= @startdate
and
{table.datetimefield} <= @enddate
)

There are other means, I just find this the most consistent.

Now elaborating on this Record Selection Formula, IÆll add in some hardcoded value:

(
{table.CustomerType} = ôBig doughö
)
and
(
{table.datetimefield} >= @startdate
and
{table.datetimefield} <= @enddate
)

Note that I continue to wrap it in parentheses, even though this formula would probably pass SQL without them. After you add in enough of them, and of different types, Crystal does a better job of passing SQL if they are all separated by parentheses.

To summarize, weÆve created a Record Selection Formula based on hard coded values, and based on a parameter.

Now letÆs add in a hard coded list of choices:

(
{table.CustomerLocation} in [ôCAö, ôORö, ôWAö]
)
and
(
{table.CustomerType} = ôBig doughö
)
and
(
{table.datetimefield} >= @startdate
and
{table.datetimefield} <= @enddate
)

This will generally pass the SQL, if you find that using an IN fails, then individually pass them using something like:

(
{table.CustomerLocation} = ôCAö
or
{table.CustomerLocation} = ôORö
or
{table.CustomerLocation} = ôWAö
)

Now letÆs add to this the ability to have a predefined Parameter using ôAllö as a means to state that you want all rows returned, otherwise return what is selected:

(
If {Table.CustomerChoices} <> ôAllö then
{Table.CustomerChoices} = {?MyParmChoices}
else if
{Table.CustomerChoices} = ôAllö then
True
)
and
(
{table.CustomerLocation} in [ôCAö, ôORö, ôWAö]
)
and
(
{table.CustomerType} = ôBig doughö
)
and
(
{table.datetimefield} >= @startdate
and
{table.datetimefield} <= @enddate
)

This will result in the record selection not being altered if the {?MyParmChoices} parameter contains All, or it will return 1 or many choices if {?MyParmChoices} does not have All.

Next weÆll look at using a Group type of Record Selection Formula.

This is generally used to limit the rows in the database to an aggregated value, such as a maximum date.

This example will pull back the maximum date for a record set:

{table.datetimefield} = max({table.datetimefield})



A common request is to display the parameters chosen, to do so for a Range type date parameter, use something like:

ôData for period: ô + totext(minimum({?MyDateParm})) +ö to ôtotext(maxnimum({?MyDateParm}))

If you have an Allow Multiple Values type of parameter, use something like:

ôValues chosen: ô + join({?MyMultiParm},ö, ô)

Which would create a comma separated listing.

If you have a single value parameter, such as a number, use:

ôYou chose: ô + totext({?MyNumberParm},0,öö)

I have never found an example where I could NOT get the SQL to pass to the database, so remain diligent, sometimes a subtle change will fix it.

I hope that this FAQ serves helps you, and if you have something meaningful to contribute, please do so.

Reference:

Opening the Record Selection Formula:
Report->Edit Selection Formula->and select Record or Group as appropriate

Determining what SQL is getting passed:
Database->Show SQL Query

Creating a SQL Expression:
Insert->Field Object->Right click SQL Expression Fields and select New

Creating a Parameter:
Insert->Field Object->Right click Parameter Fields and select New

Creating a formula:
Insert->Field Object->Right click Formula Fields and select New

-Kai