Focal Point
[SOLVED] LT, GT or ALL in one parameter

This topic can be found at:
http://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/420106155

October 14, 2010, 10:42 AM
Arif
[SOLVED] LT, GT or ALL in one parameter
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,


WebFOCUS 7.6.10
Windows
HTML
October 14, 2010, 11:01 AM
Prarie
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

 

October 14, 2010, 11:14 AM
Arif
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;  



WebFOCUS 7.6.10
Windows
HTML
October 14, 2010, 11:31 AM
GinnyJakes
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.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
October 14, 2010, 12:05 PM
Arif
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';



WebFOCUS 7.6.10
Windows
HTML
October 14, 2010, 12:24 PM
Hua
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


Developer Studio 7.6.11
AS400 - V5R4
HTML,PDF,XLS