I have a report I want to add a parameter in this report where user should be able to select one option from fowlloing.

SHOW ME where COL1VALUE GT 0 First Option
SHOW ME WHERE COL1VALUE LT 0 Second OPTION
SHOW ME ALL

IS there any easy way to do this? I think I dont know how to change the comparison operator on the fly depending on users value selection.

This message has been edited. Last edited by: Kerry,

See if this gives you some ideas.

 -SET &OPTION = &CTR.(ALL,GT,LT).OPTION.;
-SET &OPTION2 = IF &OPTION EQ 'ALL' THEN 'FOC_NONE' ELSE &OPTION;
TABLE FILE CAR
PRINT CAR SALES RETAIL_COST
WHERE SALES &OPTION2 RETAIL_COST;
END

 

prarie Very nice job. LT and GT works just fine but when I try ALL I get following error message:

  
ERROR AT OR NEAR LINE     39  IN PROCEDURE ADHOCRQ FOCEXEC *
 (FOC258) FIELDNAME OR COMPUTATIONAL ELEMENT NOT RECOGNIZED: ALL
 BYPASSING TO END OF COMMAND
 (FOC009) INCOMPLETE REQUEST STATEMENT

This is my CODE:

WHERE FIELDNAME &OPTION 0;  

Please note that Prarie used &OPTION2 in the WHERE clause. She had transformed ALL to FOC_NONE which causes the line with the WHERE clause not to be executed thereby giving you all the rows.

quote:

ABLE FILE CAR
PRINT CAR SALES RETAIL_COST
WHERE SALES &OPTION2 RETAIL_COST;

Thank you for pointing that out. It works. It my bad that I didnt see code carefully. I also tried the following and that worked too.Since I knew none of the value is going to be null so I used following code for all and it worked too. However, foc_none is better way to go just in case if in future we get null value in the field.

  SET &OPTION = &CTR.(<Positive,GT>,<Negitive,LT>,<ALL,IS>).OPTION.;
-*-SET &PASSVAL = IF &OPTION EQ 'GT' OR 'LT' THEN 0 ELSE 'NOT MISSING';

I usually try to avoid mixing with DM codes. This makes GUI compatible.

WHERE (COLVALUE1 GT 0 AND '&OPTION' EQ 'FIRST') OR
      (COLVALUE1 LT 0  AND '&OPTION' EQ 'SECOND') OR
      ('&OPTION' EQ 'ALL')  

Hua