Optional Parameters

I am creating a report in Dev Studio where the user will have several parameters to enter. If they do not enter a particular parameter, I want to return all rows for that column. If a value is entered for that parameter, then return only those rows that match the value entered. It seems that this should be a simple task to accomplish, but cannot figure it out. Help would be appreciated.

Posts: 26 | Location: Little Rock, AR | Registered: October 29, 2004

debbiej

Member

posted

Hide Post

We would use Dialogue Manager commands for this.

You can code (edit source) a -IF statement that checks the value of the parameter. If the parameter is blank, then goto a statement label after the WHERE.

Here's a quick example:
TABLE FILE CAR
PRINT CAR
BY COUNTRY
-IF &CNTRY EQ '' GOTO BYP1;
WHERE (COUNTRY EQ '&CNTRY');
-BYP1
END

Hope it helps.
Deb

Posts: 29 | Location: Ravenna, OH | Registered: December 10, 2003

Ignored post by debbiej posted

Show Post

awbradley

Member

posted

Hide Post

Thanks Deb! I'll give this a try. I'm still fairly new to FOCUS/WebFOCUS and have never used statement labels before.

Annette

Posts: 26 | Location: Little Rock, AR | Registered: October 29, 2004

Ignored post by awbradley posted

Show Post

TexasStingray

Master

posted

Hide Post

awbradley,

A special keyword FOC_NONE has been added to the FOCUS language that if returned to the reporting engine will bybass the parameter and return all values for that field.

When you add the ALL option to your dynamic parameter in the Layout Painter, this is the value that is returned to the server when ALL is chosen.

To use it in a parameter that a user type do something like this. the parameter name is COUNTRY for the example below.

-SET &COUNTRY = IF &COUNTRY.EVAL EQ '' THEN 'FOC_NONE' ELSE '&COUNTRY';
-RUN
TABLE FILE CAR
PRINT COUNTRY CAR MODEL DEALER_COST 
WHERE COUNTRY EQ &COUNTRY;
END

Give this a try.

This message has been edited. Last edited by: <Maryellen>,

Scott

Posts: 865 | Registered: May 24, 2004

Ignored post by TexasStingray posted

Show Post

Piipster

Guru

posted

Hide Post

You could just make &CNTRY = '$*' if they don't select the value. '$*' is the wildcard for alpha fields. It will select COUNTRY equal to anything.

That way you don't have to add the dialog manager statements.

Posts: 346 | Location: Melbourne Australia | Registered: April 15, 2003

Ignored post by Piipster posted

Show Post

awbradley

Member

posted

Hide Post

Thanks to everyone for all the great suggestions. This has been a huge help.

Annette

Posts: 26 | Location: Little Rock, AR | Registered: October 29, 2004

Ignored post by awbradley posted

Show Post

susannah