Selecting all values in a Parameter field

[Ajith2002]

How do we get all the values in a parameter list, by selecting a value(say, '*' OR 'ALL') from a parameter list ? The parameter field is State, once when you select * or ALL, the report should take all the State values.
Thanks in advance.

 

[SmpTraining]

In the select expert set your formula to :

if {?PromptField} = "*" then true
else {DataField} = {?PromptField}

OR

if {?PromptField} = "ALL" then true
else {DataField} = {?PromptField}

Steve Phillips, Crystal Consultant

 

[Ajith2002]

Steve,
I have got four parameter fields in my report, not just the state field. So my Record Selection formula look like -

if {?Parm1} = "ALL" then true
else {Field1} = {?Parm1} and
if {?Parm2} = "ALL" then true
else {Field2} = {?Parm2} and
{Field3} = {?Parm3} and
{Field4} = {?Parm4}

Is it OK to give this way ? When I gave like this, it was keep on processing all the records, and I manually stopped it.
Am I missing something here ??

 

[ALAMM]

Give it this way.It is always recommended not to use any IF clauses in select expert.

({Table.Field1} = {?Parm1} OR {?Parm1} = "ALL&quot
AND
({Table.Field2} = {?Parm2} OR {?Parm2} = "ALL&quot
AND
({Table.Field3} = {?Parm3} OR {?Parm1} = "ALL&quot

Add "ALL" in default values of the parameter.

Good Luck !

http://www.jannah.com/forum/

The day He summons you, you will respond by praising Him, and you will then realize that you had lasted in this life but a short while.

 

[SmpTraining]

Alamm is right,

Without them, this is how your statement looks to crystal

if {?Parm1} = "ALL" then 
    true
 else 
// The rest will only be considered IF Param1 is not ALL
   {Field1} = {?Parm1} and
   if {?Parm2} = "ALL" then 
      true
   else 
// The next lines will ONLY be tested for IF param1
// is not ALL and param2 is not ALL
        {Field2} = {?Parm2} and
        {Field3} = {?Parm3} and
        {Field4} = {?Parm4}

You must be careful with logic errors whenever you use the if..then..else construct and whenever you use OR instead of AND.

Hope that helps.
Steve Phillips, Crystal Consultant

 

[Ajith2002]

Guys,
It still didnt help me. ;-(( This is my updated Record Selection formula -

if {?Parm1} = "ALL" then
true
else
{Field1} = {?Parm1} and
{Field2} = {?Parm2} and
{Field3} = {?Parm3} and
{Field4} startswith 'P' and
{Field5} >={?StartDate} and
{Field5} <= {?EndDate}

How does Crystal takes ALL the parameters by simply giving -
if {?Parm1} = &quot;ALL&quot; then
true


This report should fetch around 50-60 records, but its trying to return almost all the records.

 

[Ajith2002]

Hey guys,
I found out what's wrong. In fact, there are around 70,000 records with Field1 value = null. So when I select 'ALL' in the parm1 it's fetching all the records, which caused the delay. So I changed the formula like this -

if {?Parm1} = &quot;ALL&quot; then
true
else
{Field1} = {?Parm1} and
{Field1} <> &quot;&quot; and
{Field2} = {?Parm2} and
{Field3} = {?Parm3} and
{Field4} startswith 'P' and
{Field5} >={?StartDate} and
{Field5} <= {?EndDate}

But still, it's fetching all the records. Also, when I tried with just three parm1 values, instead of selecting &quot;ALL&quot;, it went through all the 70,000 records and returned 25 records, which is correct. But why is it going thru all the records ???

 

[Ajith2002]

Thanks guys. My problem is resolved. I changed the Record selection formula I posted before to this way -

{Field1} <> &quot;&quot; and // since there are 70,000 records with blank Field1 value.

if {?Parm1} = &quot;ALL&quot; then
true
else
{Field1} = {?Parm1} and
{Field2} = {?Parm2} and
{Field3} = {?Parm3} and
{Field4} startswith 'P' and
{Field5} >={?StartDate} and
{Field5} <= {?EndDate}