As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.

Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.

Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.

I know this code is clunky but I couldn't thin of another way to do what I am trying to do. As you will see in the attached fex. I am pulling data from a sql pass thru, (working fine) I am building a list of months based on the parm values entered by the user. These values are going into a variable called &valuelist it contains (01,02,03) exactly what it should, Then I am trying to create a variable that will contain either a blank (space) or a -* to hide that column. I am calling these variables &ON_OFF1 - &ON_OFF12. only using the first few till I get it working. I have done something similar before and used the valuelist in a if statement in a define field and didn't have issues, however this time it always displays the -* value as if the number I am checking does not exist in the list. the code in question is around line 139. "-SET &ON_OFF1 = IF '01' IN (&VALUELIST) THEN ' ' ELSE '-*';" I have tried putting the value in quotes and without quotes. doesn't make a difference. If there is another way to dynamically show and hide cols I will happily change the way I am doing this. I am as usual up against a deadline so any suggestions would be welcome.

ENGINE SQLMSS SET DEFAULT_CONNECTION XXXXX SQL SQLMSS PREPARE SQLOUT FOR SELECT FD.LocationID ,DL.LOCATION_NAME ,DL.OPERATION_NAME ,DL.REGION_NAME ,DL.GROUP_NAME ,DD.LastDayOfMonth ,COUNT(WO_Num) AS 'CALLS' FROM [FPAnalytics].[dbo].[FPData] FD JOIN [FPAnalytics].[dbo].[DDate] DD ON CONVERT(VARCHAR(10),DD.Date,126) = CONVERT(VARCHAR(10),FD.SubmitDate,126) JOIN [FPAnalytics].[dbo].[DLot] DL ON FD.LocationID = DL.LotKey -*WHERE DD.Date = DATEADD(year,-1, '&START_DATE') OR DD.Date = '&START_DATE' WHERE DD.Date BETWEEN DATEADD(year,-1, '&START_DATE') AND DATEADD(year,-1, '&END_DATE')

GROUP BY FD.LocationID ,DL.LOCATION_NAME ,DL.OPERATION_NAME ,DL.REGION_NAME ,DL.GROUP_NAME ,DD.LastDayOfMonth order by LastDayOfMonth desc END -*?FF SQLOUT -*-RUN -*-EXIT DEFINE FILE SQLOUT

LDM/M = LastDayOfMonth; A_LDM/A2 = EDIT(LDM); END TABLE FILE SQLOUT SUM A_LDM BY LastDayOfMonth NOPRINT ON TABLE HOLD AS WORKIT END -RUN -READMORE1

-READ WORKIT &VALUE1.A2. -SET &VALUE = EDIT(&VALUE1,' '); -IF &IORETURN NE 0 THEN GOTO LOOP_DONE ; -*-SET &VALUELIST = &VALUE1 ; -SET &VALUELIST = IF &VALUELIST EQ ' ' THEN &VALUE1 ELSE &VALUELIST | ',' |&VALUE1;

LY_DEC/D12 = IF MTH EQ '12' AND YR EQ '&LST_YR' THEN CALLS ; LY_NOV/D12 = IF MTH EQ '11' AND YR EQ '&LST_YR' THEN CALLS ; LY_OCT/D12 = IF MTH EQ '10' AND YR EQ '&LST_YR' THEN CALLS ; LY_SEP/D12 = IF MTH EQ '09' AND YR EQ '&LST_YR' THEN CALLS ; LY_AUG/D12 = IF MTH EQ '08' AND YR EQ '&LST_YR' THEN CALLS ; LY_JUL/D12 = IF MTH EQ '07' AND YR EQ '&LST_YR' THEN CALLS ; LY_JUN/D12 = IF MTH EQ '06' AND YR EQ '&LST_YR' THEN CALLS ; LY_MAY/D12 = IF MTH EQ '05' AND YR EQ '&LST_YR' THEN CALLS ; LY_APR/D12 = IF MTH EQ '04' AND YR EQ '&LST_YR' THEN CALLS ; LY_MAR/D12 = IF MTH EQ '03' AND YR EQ '&LST_YR' THEN CALLS ; LY_FEB/D12 = IF MTH EQ '02' AND YR EQ '&LST_YR' THEN CALLS ; LY_JAN/D12 = IF MTH EQ '01' AND YR EQ '&LST_YR' THEN CALLS ; -*CY CALCULATIONS BY MONTH -*MOM CALCULATIONS CURREN YEAR -*YOY CALCULATIONS BY MONTH CURR YEAR VS LY TY_JAN/D12 = IF MTH EQ '01' AND YR EQ '&CUR_YR' THEN CALLS ; MOM_JAN/D12 = IF MTH EQ '01' THEN (TY_JAN - LY_DEC) ELSE 0 ; YOY_JAN/D12 = TY_JAN - LY_JAN; TY_FEB/D12 = IF MTH EQ '02' AND YR EQ '&CUR_YR' THEN CALLS ; MOM_FEB/D12 = IF MTH EQ '02' THEN (TY_FEB - LY_FEB) ELSE 0 ; YOY_FEB/D12 = TY_FEB - LY_FEB; TY_MAR/D12 = IF MTH EQ '03' AND YR EQ '&CUR_YR' THEN CALLS ; MOM_MAR/D12 = IF MTH EQ '03' THEN (TY_MAR - LY_MAR) ELSE 0 ; YOY_MAR/D12 = TY_MAR - LY_MAR; TY_APR/D12 = IF MTH EQ '04' AND YR EQ '&CUR_YR' THEN CALLS ; MOM_APR/D12 = IF MTH EQ '04' THEN (TY_APR - LY_APR) ELSE 0 ; YOY_APR/D12 = TY_APR - LY_APR; TY_MAY/D12 = IF MTH EQ '05' AND YR EQ '&CUR_YR' THEN CALLS ; MOM_MAY/D12 = IF MTH EQ '05' THEN (TY_MAY - LY_MAY) ELSE 0 ; YOY_MAY/D12 = TY_MAY - LY_MAY; TY_JUN/D12 = IF MTH EQ '06' AND YR EQ '&CUR_YR' THEN CALLS ; MOM_JUN/D12 = IF MTH EQ '06' THEN (TY_JUN - LY_JUN) ELSE 0 ; YOY_JUN/D12 = TY_JUN - LY_JUN; TY_JUL/D12 = IF MTH EQ '07' AND YR EQ '&CUR_YR' THEN CALLS ; MOM_JUL/D12 = IF MTH EQ '07' THEN (TY_JUL - LY_JUL) ELSE 0 ; YOY_JUL/D12 = TY_JUL - LY_JUL; TY_AUG/D12 = IF MTH EQ '08' AND YR EQ '&CUR_YR' THEN CALLS ; MOM_AUG/D12 = IF MTH EQ '08' THEN (TY_AUG - LY_AUG) ELSE 0 ; YOY_AUG/D12 = TY_AUG - LY_AUG; TY_SEP/D12 = IF MTH EQ '09' AND YR EQ '&CUR_YR' THEN CALLS ; MOM_SEP/D12 = IF MTH EQ '09' THEN (TY_SEP- LY_SEP) ELSE 0 ; YOY_SEP/D12 = TY_SEP - LY_SEP; TY_OCT/D12 = IF MTH EQ '10' AND YR EQ '&CUR_YR' THEN CALLS ; MOM_OCT/D12 = IF MTH EQ '10' THEN (TY_OCT - LY_OCT) ELSE 0 ; YOY_OCT/D12 = TY_OCT - LY_OCT; TY_NOV/D12 = IF MTH EQ '11' AND YR EQ '&CUR_YR' THEN CALLS ; MOM_NOV/D12 = IF MTH EQ '11' THEN (TY_NOV - LY_NOV) ELSE 0 ; YOY_NOV/D12 = TY_NOV - LY_NOV; TY_DEC/D12 = IF MTH EQ '12' AND YR EQ '&CUR_YR' THEN CALLS ; MOM_NOV/D12 = IF MTH EQ '12' THEN (TY_DEC - LY_DEC) ELSE 0 ; YOY_DEC/D12 = TY_DEC - LY_DEC; END -SET &ON_OFF1 = IF '01' IN (&VALUELIST) THEN ' ' ELSE '-*'; -SET &ON_OFF2 = IF '02' IN (&VALUELIST) THEN ' ' ELSE '-*'; -SET &ON_OFF3 = IF '03' IN (&VALUELIST) THEN ' ' ELSE '-*'; -SET &ON_OFF4 = IF '04' IN (&VALUELIST) THEN ' ' ELSE '-*'; -*-SET &ON_OFF5 = IF &VALUE1 EQ '05' THEN ' ' ELSE '-*'; -*-SET &ON_OFF6 = IF &VALUE1 EQ '06' THEN ' ' ELSE '-*'; -*-SET &ON_OFF7 = IF &VALUE1 EQ '07' THEN ' ' ELSE '-*'; -*-SET &ON_OFF8 = IF &VALUE1 EQ '08' THEN ' ' ELSE '-*'; -*-SET &ON_OFF9 = IF &VALUE1 EQ '09' THEN ' ' ELSE '-*'; -*-SET &ON_OFF10 = IF &VALUE1 EQ '10' THEN ' ' ELSE '-*'; -*-SET &ON_OFF11 = IF &VALUE1 EQ '11' THEN ' ' ELSE '-*'; -*-SET &ON_OFF12 = IF &VALUE1 EQ '12' THEN ' ' ELSE '-*';

-*-SET &ON_OFF2 = IF &VALUE1 EQ '02' THEN ' ' ELSE '-*'; -*-SET &ON_OFF3 = IF &VALUE1 EQ '03' THEN ' ' ELSE '-*'; -*-SET &ON_OFF4 = IF &VALUE1 EQ '04' THEN ' ' ELSE '-*'; -*-SET &ON_OFF5 = IF &VALUE1 EQ '05' THEN ' ' ELSE '-*'; -*-SET &ON_OFF6 = IF &VALUE1 EQ '06' THEN ' ' ELSE '-*'; -*-SET &ON_OFF7 = IF &VALUE1 EQ '07' THEN ' ' ELSE '-*'; -*-SET &ON_OFF8 = IF &VALUE1 EQ '08' THEN ' ' ELSE '-*'; -*-SET &ON_OFF9 = IF &VALUE1 EQ '09' THEN ' ' ELSE '-*'; -*-SET &ON_OFF10 = IF &VALUE1 EQ '10' THEN ' ' ELSE '-*'; -*-SET &ON_OFF11 = IF &VALUE1 EQ '11' THEN ' ' ELSE '-*'; -*-SET &ON_OFF12 = IF &VALUE1 EQ '12' THEN ' ' ELSE '-*'; -* -TYPE FIRST &ON_OFF1 -RUN -EXIT TABLE FILE SQLOUT SUM CALLS NOPRINT &ON_OFF1 TY_JAN &ON_OFF1 LY_JAN &ON_OFF1 MOM_JAN &ON_OFF1 YOY_JAN &ON_OFF2 TY_FEB &ON_OFF2 LY_FEB &ON_OFF2 MOM_FEB &ON_OFF2 YOY_FEB &ON_OFF3 TY_MAR &ON_OFF3 LY_MAR &ON_OFF3 MOM_MAR &ON_OFF3 YOY_MAR &ON_OFF4 TY_APR &ON_OFF4 LY_APR &ON_OFF4 MOM_APR &ON_OFF4 YOY_APR -*&ON_OFF5 TY_MAY -*&ON_OFF5 LY_MAY -*&ON_OFF5 MOM_MAY -*&ON_OFF5 YOY_MAY -*&ON_OFF6 TY_JUN -*&ON_OFF6 LY_JUN -*&ON_OFF6 MOM_JUN -*&ON_OFF6 YOY_JUN -*&ON_OFF7 TY_JUL -*&ON_OFF7 LY_JUL -*&ON_OFF7 MOM_JUL -*&ON_OFF7 YOY_JUL -*&ON_OFF8 TY_AUG -*&ON_OFF8 LY_AUG -*&ON_OFF8 MOM_AUG -*&ON_OFF8 YOY_AUG -*&ON_OFF9 TY_SEP -*&ON_OFF9 LY_SEP -*&ON_OFF9 MOM_SEP -*&ON_OFF9 YOY_SEP -*&ON_OFF10 TY_OCT -*&ON_OFF10 LY_OCT -*&ON_OFF10 MOM_OCT -*&ON_OFF10 YOY_OCT -*&ON_OFF11 TY_NOV -*&ON_OFF11 LY_NOV -*&ON_OFF11 MOM_NOV -*&ON_OFF11 YOY_NOV -*&ON_OFF12 TY_DEC -*&ON_OFF12 LY_DEC -*&ON_OFF12 MOM_DEC -*&ON_OFF12 YOY_DEC

BY LocationID BY LOCATION_NAME BY OPERATION_NAME BY REGION_NAME BY GROUP_NAME -*ACROSS DISP_DATE

IF RECORDLIMIT EQ 5000 ON TABLE SET PAGE-NUM NOLEAD ON TABLE SET ASNAMES ON ON TABLE RECOMPUTE AS 'TOTAL' ON TABLE PCHOLD FORMAT HTML ON TABLE SET HTMLEMBEDIMG ON ON TABLE SET HTMLCSS ON ON TABLE SET STYLE * INCLUDE=IBFS:/WFC/Repository/OPS_Portals/OPS_Revenue/"This message has been edited. Last edited by: MG,

Chuck, Thank You. Well Duhhh, I was so sure the issue was with the value not the valuelist, I didnt even think about needing the eval on that variable.