using 'ALL' as a paramter option 3

[cmpgeek]

we have several reports where the writer gives you several options to choose from within one parameter. usually "ALL" is one of those options. as you would think, if you choose "ALL", the report runs as if you choose all the other values one by one and added them.

i would like to use this on the report i am writing now. we have 116 surgeons to choose from for this report, so if i want to run it for all of them, it would be MUCH easier to choose "ALL" then to hit the ADD button over 100 times...

the problem is, whenever i choose "ALL", the report comes back empty. i have copied the formula used in the other reports, but with the same result... is there a trick to using the "ALL" option that is saved somewhere outside of the record selection criteria?

i hope i havent lost yall on this one...
thanks in advance for any help you can offer...

my vital stats: Crystal 8.5 / Oracle database / Windows XP...
... always in a state of ... inspiring to some day experience ...

 

[MJRBIM]

To solve a similar problem I use this...

Record Selection Formula

IF {?Name} <> &quot;ALL&quot; THEN ({EmpInfo.Name} = {?Name})
ELSE {EmpInfo.Name} = {EmpInfo.Name}

- - - - - - - - - - - - - - - - - - -

The first record I have in the default list for my {?Name} Parameter is &quot;ALL&quot;.

NOTE eformance may not be great for this solution on really large data sources.

 

[RobbieB]

You have to define &quot;ALL&quot; in your record selection as being equal to all the records in the &quot;doctor&quot; field. something like: if {?DoctorsName}= &quot;ALL&quot; then {Doctors.DocName}<>&quot;&quot;
else {Doctors.DocName} = {?DoctorsName}

where {?DoctorsName} is your parameter and {Doctors.DocName}is th table/field name.
and you will have to add it to you default settings in the parameter, although it sounds like you already knew that. does this help?

 

[RWWAMS]

Can you post your selection formula? Here is what I use to accomplish the same.

IF {?PARAMETER_NAME} <> &quot;ALL&quot; THEN
{TABLE_NAME.FIELD_NAME} = {?PARAMETER_NAME}

Hope it helps.

 

[rhinok]

None of these formulas will pass the criteria to the database for processing. Also, you do not need to define 'All' as being equal to all the records, as RobbieB suggests.

The formula you need, which will hand off processing to the server, is:

//This formula will pass record selection criteria to the database
//The 'In' statement allows more than one surgeon to be selected
(
If
{?Surgeon} <> &quot;All&quot;
Then
{table.field} In {?Surgeon}
Else If
{?Surgeon} = &quot;All&quot;
Then
True
)

 

[cmpgeek]

I tried all three, but the only one that worked for me was RobbieB's... but thanks to all of you for responding so quickly!

thanks again...


my vital stats: Crystal 8.5 / Oracle database / Windows XP...
... always in a state of ... inspiring to some day experience ...

 

[cmpgeek]

rhinok,
i think our posts hit at the same time... yours works for me as well, but i am curious about something...

can you explain what you mean when you say the other choices will not pass the criteria to the database for processing? i am guessing that is why the other two did not work for me, but RobbieB's formula did... i am just trying to understand what was lacking in the other formulas that would cause them to not pass the criteria on to the database. (i have been writing reports for under a year, so i am still fairly new to some of this... i understand what happens on my side of the screen, but i have very little knowledge about the background and i truly want to learn it.)

thanks again to all of you...

my vital stats: Crystal 8.5 / Oracle database / Windows XP...
... always in a state of ... inspiring to some day experience ...

 

[synapsevampire]

cmpgeek: Check out my FAQ for a description of pass SQL (use Database Show SQL Query): faq767-3825

Rhinok: You don't need to use IN for multiple selections, Crystal handles = the same way.

-k

 

[cmpgeek]

thanks synapse!

my vital stats: Crystal 8.5 / Oracle database / Windows XP...
... always in a state of ... inspiring to some day experience ...