Best practices for coding multiple parameters

[MSBrady]

CR 10
SQL Server 2000

Oy!

I have seen several posts regrading how to code multiple parameters on this forum. Is there a best practice that most people agree on?

(
{?param1} = {table.name1} or
{?param1} = "All"
) and
(
{?param2} = {table.name2} or
{?param2} = "All"
)

versus

(
if {?param1} = "All"
then TRUE
else if {?param1} <> "All" 
then {?param1} = {table.name1}
) and
(
if {?param2} = "All"
then TRUE
else if {?param2} <> "All" 
then {?param2} = {table.name2}
)

Also does the best practice change with field data types?

thanks,

.bat man

 

[synapsevampire]

Depends on your version of Crystal.

With CR 10, the former should pass the SQL in almost every instance, I've only had one real world example where it would not and had to opt for something akin to your latter methodology.

The latter methodology is an incorrect implementation of a system I worked out years ago for CR 8.5, note that it may not pass the SQL correctly to the database using it in older versions of Crystal, and perhaps even with CR 10 if the record selection gets complex.

Check out my FAQ:

faq767-3825

-k

 

[HardJeans]

There is an minuscule performance hit taken on the second one you display since it has to process more conditions. The condition itself returns True or False, so there's no point in telling it to return True or False. In the end, the Select formula is *usually* only processed once, and the performance difference is absolutely non-existent. Even with thousands upon thousands of it repeating probably won't make a difference.

So I say, do what makes it easier for you to read. Personally I find the first one easier, and cleaner to read, and would prefer that format if I'm working on somebody else's report.

 

[synapsevampire]

Hardjeans: at issue is whether it passes to the database, that is where the performance is gained. Not sure what the point of your post is, are you aware that the criteria needs to pass to the database, and that is the #1 way to increase Crystal performance?

As for processing time within reading the record selection, as you state, it's irrelevent and not worthy of consideration, but stating that it doesn't matter misses the whole point of a record selection formula, it has to pass the criteria to the database.

-k

 

[MSBrady]

Is there something about "All" that Crystal knows that means '*'? What happens when a field in a row of data equals "ALL"? Like if you are using a code field and the values are:
AGY
AAGJ
ALL
APS

 

[synapsevampire]

All doesn't get passed, it is the side that tells Crystal to do nothing, which is the same as saying all.

To simplify, you could set the default parameter to "Get nuffin" and check for that text.

-k

 

[MSBrady]

Got it. Thank you both for your input. Anyone else care to weigh in?