Select Statement to Select All or one, passes to DB

[sdebo2]

We are using crystal version 8.0 and are going to have the reports run from a web page.

The report will have several parameters (Like ={?CourseCode}) and the web page will present a drop down list of Courses, including one called “ALL”. If a Course is selected from the list, then the report should only select that one Course, if ALL is selected the report should not consider CourseCode in the select statement. I have placed an if statement in the select statement to check the value of the parameter and add the appropriate statements.

Sample select statement:
if {?CourseCode}<> 'ALL'
then {TRANSCRIPTS.CD_CRS}={?CourseCode}
else true
and
if {?EventCode}<> 'ALL'
then {EVENTS.NO_EVNT}={?EventCode}
else true

The above code produces the correct results, but my concern is it appears the crystal does not pass the statement to the database, so it will read all the records in the database, and then filters through them on the front end. I found several samples from Business Object knowledge base, which show nested If statements, but they do not get passed to the database either.

That database I am working on has the potential to be vary large so the less records I select from the database the better. Does anyone have any ideas, how I can select one Course or all Courses and pass the statement to the database if only one Course is selected?

 

[vidru]

There's a great faq on this here: faq767-3825

Using the techniques described there, your best bet to get the SQL passed to the server would be with a record selection formula akin to this:

(
if {?CourseCode} <> 'ALL' then
{TRANSCRIPTS.CD_CRS} = {?CourseCode}
else if {?CourseCode} = 'ALL' then
true
)
and
(
if {?EventCode} <> 'ALL' then
{EVENTS.NO_EVNT} = {?EventCode}
else if {?EventCode} = 'ALL' then
true
)

-dave

 

[synapsevampire]

Dave's example should work, if not, rather than posting "that database", post the type of database and the connectivity used.

-k