Focal Point
[SOLVED] dynamically show or hide cols based on value of users selected start

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

February 20, 2020, 11:41 AM
MG
[SOLVED] dynamically show or hide cols based on value of users selected start
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. 

Code:
"-SET &ECHO = ALL;
-DEFAULT &START_DATE = '01/01/2020';
-DEFAULT &END_DATE = '03/31/2020';
-DEFAULTH &CUR_YR = '&DATEYY.EVAL';
-DEFAULTH &MONTH = '01';

-SET &LST_YR = '&CUR_YR.EVAL'-1;

-TYPE &LST_YR
-*-RUN
-*-EXIT



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;

-TYPE MONTH IS &VALUELIST
-GOTO READMORE1


-*
-RUN
-*
-LOOP_DONE

DEFINE FILE SQLOUT
VAR/MDYY = '&START_DATE.EVAL';
VAR_DTE/M = VAR;
DISP_DATE/MtYY = LastDayOfMonth;
YRMTH/MYY = LastDayOfMonth;
MTH/M = LastDayOfMonth;
YR/YY = LastDayOfMonth;
-*LY CACLULATION BY MONTH

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,


WebFOCUS 8.05
Windows , Excel, PDf, HTML
February 20, 2020, 01:23 PM
FP Mod Chuck
MG

Change it to
-SET &ON_OFF1 = IF '01' IN (&VALUELIST.EVAL) THEN ' ' ELSE '-*';
and it should work.


Thank you for using Focal Point!

Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats
February 20, 2020, 01:34 PM
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.

Sorry to be a bother.


WebFOCUS 8.05
Windows , Excel, PDf, HTML
February 20, 2020, 02:03 PM
FP Mod Chuck
Not a bother at all, that is what this Forum is for...


Thank you for using Focal Point!

Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats