CR 8.5: "ALL" Parameter on a Number Field

[cgordonn]

Hello All,
Quick question. I have a parameter based on a number field in the database {tablename.filenum}. The user would like to be able to enter a particular file nnumber, or "ALL" to return all file numbers. How do I get an "ALL" type selection to work with a number field based parameter?

Thanks!
CGordonn

 

[synapsevampire]

you have options, such as setting a default to 0 (or some number that could never exist) and coding for it in the record selection formula:

(
if {?MyParm} <> 0 then
{tablename.filenum} = {?MyParm}
else if {?MyParm} = 0 then
true
)

Note that the parentheticals and qualifying else if are important to assure data pass through to the database.

Another approach would be to use a tsring prompt with a default of &quot;All&quot;:

(
if {?MyParm} <> &quot;All&quot; then
{tablename.filenum} = val({?MyParm})
else if {?MyParm} = &quot;All&quot; then
true
)

The latter can be problematic for passing SQL and with a multiple value parm.

Here's my FAQ which touches on this topic:

faq767-3825

-k

 

[cgordonn]

Thanks vampire. The first option worked perfectly.

Regards,
CGordonn

 

[Mike Lewis]

Synapsevampire,

Just out of curiosity, do you in fact need the second IF .. THEN in your formula? In other words, isn't this:

(
if {?MyParm} <> 0 then
{tablename.filenum} = {?MyParm}
else if {?MyParm} = 0 then
true
)


the same as this:

(
if {?MyParm} <> 0 then
{tablename.filenum} = {?MyParm}
else true
)

Or have I misunderstood?

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[synapsevampire]

Mike, take a look at my FAQ:

faq767-3825

The point isn't to merely do the record selection, that can be accomplished through the expert by new users, the concern is to assure SQL Pass through.

If you were simple trying to minimize the codebase, you should use:

(
if {?MyParm} <> 0 then
{tablename.filenum} = {?MyParm}
)

Why the Else at all?

Therein lies the ungraciousness of Crystal whehn trying to optimize SQL

CR 9 is a bit more generous with passing SQL, but this format consistently works (inclusive of honoring the parentheticals).

-k

 

[Mike Lewis]

synapsevampire,

That's very enlightening. Thank you for the detailed response. I'm going to study the FAQ carefully.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com