INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Formula Help

Optimizing SQL Pass Through using the Record Selection Formula by synapsevampire
Posted: 2 Jul 03 (Edited 4 May 05)

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 your own function.

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
cdatetime(year(minimum({?MyDateParm})), month(minimum({?MyDateParm})), day(minimum({?MyDateParm})),0,0,0)

@enddate
cdatetime(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 {?MyParmChoices}} <> ôAllö then
{Table.CustomerChoices} = {?MyParmChoices}
else if
{?MyParmChoices} = ô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.

Recently Ido Millet demonstrated the following method for checking default values which appears to work well in my preliminary tests, but how you construct it is critical, and I will likely stick with the tested method until I can assure that this means holds up:

(
{Table.CustomerChoices} = {?MyParmChoices}
or
{?MyParmChoices} = ôAllö
)

However if you reverse the order of the above the SQL won't pass.

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 helps you, and if you have something meaningful to contribute, please do so.

To display parameters, take a look at my whitepaper on that:

FAQ767-5684

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

-K

Back to Business Objects: Crystal Reports 1 Formulas FAQ Index
Back to Business Objects: Crystal Reports 1 Formulas Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close