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.
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
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')
Developer Studio 7.6.11
AS400 - V5R4
|Powered by Social Strata|