Focal Point Banner


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.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SHARING] Semi-Dynamic Prompt Includes with Defaults

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SHARING] Semi-Dynamic Prompt Includes with Defaults
 Login/Join
 
Gold member
posted
Thought I'd share how we've been able to get our prompts through auto prompter to have dynamic defaults.

For lack of a better term calling it "Auto Update Includes"

Can sum up what the below does as it writes the prompt to separate .fex files as a manual list. Those .fex files can then be included into reports with the prompt easy.
Since it writes it all as plain text using the include fex is fast and won't update unless manually run or automate when the "Auto Update Includes" runs.

Here is a simplified version that is being used with some examples. (It is for a University so is term driven but also have included examples using the CAR and GGSALES tables with Text and Dates after the first section of code).

Can just skip to the following occurrence of "CAR" but most of the comments explaining is in the first part.


SET ASNAMES = ON
SET FIELDNAME = NOTRUNC
SET HOLDLIST = PRINTONLY
SET DATETIME = NOW
-SET &S_TIME=&TOD;
-TYPE Starting Time: &S_TIME
-RUN


-*Gets the highest term that has started to get the current/default term to base everything else off of and stores it into a variable to be used easy.
-SET &DEF_TERM='';
TABLE FILE PROD_STVTERM
SUM MAX.STVTERM_CODE
WHERE DATEDIF(STVTERM_START_DATE-7,'&YYMD','D') GE 0;
ON TABLE HOLD AS DEFA_TERM
END
-RUN
-READ DEFA_TERM &DEF_TERM.A6


-*These two are used for the Prompt Value (P_VAL) and Prompt Description (P_DESC) to be used without being a specific field name.
-SET &P_VAL='';
-SET &P_DESC='';

-*Folder for where the include files are
-SET &FILE_DEF='E:\ibi\apps\baseapp\';

-*This is a function to be able to easily count forward or backward a term. This makes the include fex easier to be dynamic
-*Example: give it a TERM of 201730 and CNT of 0 and will return 201730. 
-*         A CNT of 1 would be the next term in the future so would be 201740.
-*         A CNT of -1 would give the previous term of 201710.
-*Our terms have values that have four digit years followed by two digits for the type. Values are 10, 30, 40, and 50.
-*Side Note: Could be modified for other similar patterns just would have to figure out what each step would increment/decrease by. With four it is an easy .25
DEFINE FUNCTION CHANGETERM_2(TERM/A6, CNT/I7)
TERM_CODE/I2=EDIT(EDIT(TERM,'$$$$99'));
TERM_YEAR/I4=EDIT(EDIT(TERM,'9999'));
TERM_NUM/D6.2=DECODE TERM_CODE(10 .00 30 .25 40 .5 50 .75);
TERM_CNT/D6.2=TERM_NUM + (.25 * CNT );
NEW_YEAR/A4=FTOA(TERM_YEAR + TERM_CNT,'(F7.2)','A4');
NEW_TERM_1/D10.2=(TERM_YEAR + TERM_CNT) - EDIT(NEW_YEAR);
NEW_TERM/A2=DECODE NEW_TERM_1(.00 '10' .25 '30' .50 '40' .75 '50');
CHANGETERM_2/A6=NEW_YEAR || NEW_TERM;
END
-RUN


-*This variable and define is just to convert our terms into an academic year format (i.e. for 201630 it is '2016-2017').
-*  PTOA is used since it rounds up with a .5 value
-SET &ACA_YEAR_A9=PTOA((&DEF_TERM-100)/100,'(P4)','A4') || '-' || PTOA(&DEF_TERM/100,'(P4)','A4');
DEFINE FILE PROD_STVTERM
ACA_YEAR_A9/A9=IF STVTERM_CODE NE '999999' THEN (PTOA((EDIT(STVTERM_CODE)-100)/100,'(P4)','A4') || '-' || PTOA(EDIT(STVTERM_CODE)/100,'(P4)','A4')) ELSE '9998-9999';
END


-*&PROMPT_VAL -- This is where the list is stored into so I clear it out before each section just in case
-*&TERM_VAR -- This is the Ampersand variable name without the &
-*&TERM_VAR_DESC -- This is the description that is shown in the auto prompter
-*&TERM_MULTI -- This is for if it is a multi select or not. Empty string ('') means is a single select dropdown. Values of 'OR'/'AND' will make it a multi select with an OR/AND respectively
-*&FEX_NAME -- This is the name of the .fex file
-*&INCLU_TERM -- This becomes the default value selected in the prompt


-*3c2_select_term (This will get three previous terms, the current term, and one future term)
-SET &PROMPT_VAL='';
-SET &TERM_VAR='TERMKY5';
-SET &TERM_VAR_DESC='Select Term';
-SET &TERM_MULTI='';
-SET &FEX_NAME='3c2_select_term';
-SET &INCLU_TERM=CHANGETERM_2('&DEF_TERM.EVAL',0);
TABLE FILE PROD_STVTERM
BY HIGHEST STVTERM_CODE AS 'P_VAL'
BY STVTERM_DESC AS 'P_DESC'
WHERE STVTERM_CODE GE CHANGETERM_2('&INCLU_TERM',-3);
WHERE STVTERM_CODE LE CHANGETERM_2('&INCLU_TERM',1);
ON TABLE HOLD AS H_TERMS
END

-*This is an example of what it would show
-*-TYPE &FEX_NAME ::  -*3c2_select_term.fex uses &TERMKY5
-*-TYPE &FEX_NAME ::  -DEFAULT &TERMKY5='201730';
-*-TYPE &FEX_NAME ::  -PROMPT &TERMKY5.(<Summer 2017,201740>,<Spring 2017,201730>,<Fall/Spring 1617 Interim,201710>,<Fall 2016,201650>,<Summer 2016,201640>  ).Select Term.

-*This will jump to the dropdown section to write to the fex.
-IF '&PROMPT_VAL.EVAL' EQ '' THEN GOTO LBLDROPDOWN;
-*This is a label so that after it jumps down to the common/dynamic code to write it will be able to jump back up to where it was
-*   based on the value in &FEX_NAME which is used as the label name
-:L_3c2_select_term


-*This is another fex example but is a multi select
-SET &PROMPT_VAL='';
-SET &TERM_VAR='TERMKY1';
-SET &TERM_VAR_DESC='Select Term';
-SET &TERM_MULTI='OR';
-SET &FEX_NAME='multi_select_terms';
-SET &INCLU_TERM=CHANGETERM_2('&DEF_TERM.EVAL',0);
TABLE FILE PROD_STVTERM
BY HIGHEST STVTERM_CODE AS 'P_VAL'
BY STVTERM_DESC AS 'P_DESC'
WHERE STVTERM_CODE GE CHANGETERM_2('&INCLU_TERM',-11);
WHERE STVTERM_CODE LE CHANGETERM_2('&INCLU_TERM',5);
ON TABLE HOLD AS H_TERMS
END

-IF '&PROMPT_VAL.EVAL' EQ '' THEN GOTO LBLDROPDOWN;
-:L_multi_select_terms


-*This is an example for using a different hold name and where it only has the Value and doesn't need a description.
-*  Can have both but would be the same thing for both value and description so isn't needed.
-SET &PROMPT_VAL='';
-SET &TERM_VAR='AYEAR';
-SET &TERM_VAR_DESC='Select Academic Year';
-SET &TERM_MULTI='';
-SET &FEX_NAME='academic_year';
-SET &INCLU_TERM=&ACA_YEAR_A9;

TABLE FILE PROD_STVTERM
BY HIGHEST ACA_YEAR_A9 AS 'P_VAL'
-*BY ACA_YEAR_A9 AS 'P_DESC'
WHERE STVTERM_CODE GE CHANGETERM_2('&INCLU_TERM',-16);
WHERE STVTERM_CODE LE CHANGETERM_2('&INCLU_TERM',8);
ON TABLE HOLD AS H_YEAR_A9
END

-IF '&PROMPT_VAL.EVAL' EQ '' THEN GOTO LBLDROPDOWN;
-:L_academic_year


-*We also have some includes that shouldn't prompt and just sets the value to easily be used.
-*The only difference between the below and others is limit it to one row and use GOTO LBLSET instead of GOTO LBLDROPDOWN
-*Side Note: If can get the value from -SETs without a table don't need the hold file and can just use the value in &INCLU_TERM instead of reading but will show both ways
-SET &PROMPT_VAL='';
-SET &TERM_VAR='ACADEMIC_PERIOD';
-SET &TERM_VAR_DESC='Select Term';
-SET &TERM_MULTI='';
-SET &FEX_NAME='current_academic_period';
-SET &INCLU_TERM=CHANGETERM_2('&DEF_TERM.EVAL',0);
TABLE FILE PROD_STVTERM
BY HIGHEST STVTERM_CODE AS 'P_VAL'
BY STVTERM_DESC AS 'P_DESC'
WHERE STVTERM_CODE EQ '&INCLU_TERM';
ON TABLE HOLD AS H_TERMS
END

-IF '&PROMPT_VAL.EVAL' EQ '' THEN GOTO LBLSET;
-:L_current_academic_period



-*This is needed to stop an infinite loop. Could also put the code after LBLDROPDOWN at the top of the report and skip over it instead.
-GOTO LBLEND



-*This is the section that prepares and writes to the .fex files for dropdown includes
-LBLDROPDOWN
-RUN

-*Same as above these are used for the Prompt Value (P_VAL) and Prompt Description (P_DESC) to be used without being a specific field name. This also clears it before each run.
-SET &P_VAL=' ';
-SET &P_DESC='';

-*For use if don't want to use the same hold name for different types of values
-SET &P_TABLE=IF &FEX_NAME EQ 'academic_year' THEN 'H_YEAR_A9' ELSE 'H_TERMS';


-SET &N=1;
-REPEAT :L_DDL_TERMS FOR &N FROM 1 TO &LINES
-READFILE &P_TABLE 

-*This appends each row of the hold file at the end within the <,> as needed.
-*  If don't need a description do not need the P_DESC field in the hold file and it will just do the value in <>
-*  DECODE &N(1 '' ELSE ',') is to only put a comma after the first row/item.
-SET &PROMPT_VAL=&PROMPT_VAL || DECODE &N(1 '' ELSE ',') || '<' || DECODE &P_DESC('' '' ELSE '&P_DESC.EVAL') || DECODE &P_DESC('' '' ELSE ',') || &P_VAL || '>';

-:L_DDL_TERMS
-CLOSE &P_TABLE

-*This truncates any extra spaces from above and if it should be a multi select adds the text before and after
-SET &PROMPT_VAL=DECODE &TERM_MULTI('' '' 'OR' 'OR(' 'AND' 'AND(') || TRUNCATE(&PROMPT_VAL) || DECODE &TERM_MULTI('' '' ELSE ')');

-*Commented out so if try to run this won't accidently be making files in random places. Uncomment once know where you want them and have update the value in &FILE_DEF.

-*This is the code that writes to the fex. &FILE_DEF is from before to give the location of where to write the fex.
-*Adds a line on the first line with the fex name and the parameter name so can easily tell in message viewer (with &ECHO ON/ALL)
-*   Could change to a -TYPE to instead type it to always see it in message viewer when used in a report
-*FILEDEF INCL_UPDATE DISK &FILE_DEF..&FEX_NAME...FEX
-*-RUN
-*-WRITE INCL_UPDATE -*&FEX_NAME...fex uses &|&TERM_VAR
-*-WRITE INCL_UPDATE -DEFAULT &|&TERM_VAR='&INCLU_TERM.EVAL';
-*-WRITE INCL_UPDATE -PROMPT &|&TERM_VAR...(&PROMPT_VAL).&TERM_VAR_DESC...
-*-CLOSE INCL_UPDATE
-*-RUN

-*This is so that when running the fex to update the includes can easily see in the message viewer what it attempted to write
-TYPE &FEX_NAME ::  -*&FEX_NAME...fex uses &|&TERM_VAR
-TYPE &FEX_NAME ::  -DEFAULT &|&TERM_VAR='&INCLU_TERM.EVAL';
-TYPE &FEX_NAME ::  -PROMPT &|&TERM_VAR...(&PROMPT_VAL).&TERM_VAR_DESC...
-TYPE :L_&FEX_NAME.EVAL

-*This goes back to where it was
-GOTO :L_&FEX_NAME.EVAL



-*This is the section the prepares and writes to the fex for SET includes
-LBLSET
-RUN

-SET &P_VAL=' ';
-SET &P_DESC='';

-*For use if don't want to use the same hold name for different types of values
-SET &P_TABLE=IF &FEX_NAME EQ 'academic_year' THEN 'H_YEAR_A9' ELSE 'H_TERMS';

-*Since is only one row could just set it like this and not need to read from the table if can get it from a calculation/formula -SET
-SET &PROMPT_VAL='-SET &|&TERM_VAR.EVAL=''&INCLU_TERM.EVAL'';';

-*Else would read like before but don't need to loop
-READFILE &P_TABLE 
-SET &PROMPT_VAL='-SET &|&TERM_VAR.EVAL=''&P_VAL.EVAL'';';

-*Commented out so if try to run this won't accidently be making files in random places. Uncomment once know where you want them and have update the value in &FILE_DEF.
-*This writes pretty much the same as the dropdown piece. Would need to change the &INCLU_TERM to &P_VAL if reading from the hold table though
-*FILEDEF INCL_UPDATE DISK &FILE_DEF..&FEX_NAME...FEX
-*-RUN
-*-WRITE INCL_UPDATE -*&FEX_NAME...fex uses &|&TERM_VAR
-*-WRITE INCL_UPDATE -SET &|&TERM_VAR='&INCLU_TERM.EVAL';
-*-CLOSE INCL_UPDATE
-*-RUN

-TYPE &FEX_NAME ::  -*&FEX_NAME...fex uses &|&TERM_VAR
-TYPE &FEX_NAME ::  -SET &|&TERM_VAR='&INCLU_TERM.EVAL';
-GOTO :L_&FEX_NAME.EVAL


-LBLEND
-RUN
-SET &E_TIME=&TOD;
-TYPE Ending Time &E_TIME 




And here are the CAR and GGSALES examples that use pretty much the same write code as above.

SET ASNAMES = ON
SET FIELDNAME = NOTRUNC
SET HOLDLIST = PRINTONLY
SET DATETIME = NOW
-SET &S_TIME=&TOD;
-TYPE Starting Time: &S_TIME
-RUN

-SET &FILE_DEF='E:\ibi\apps\baseapp\';

TABLE FILE CAR
SUM
SALES
BY CAR
ON TABLE HOLD AS H_CAR_SALES_0
END

-SET &HIGHEST_SALES='';
TABLE FILE H_CAR_SALES_0
BY HIGHEST 1 SALES NOPRINT
BY CAR AS 'HIGHEST_SALES'
ON TABLE HOLD AS H_CAR_SALES
END
-RUN
-READFILE H_CAR_SALES
-SET &HIGHEST_SALES=TRUNCATE(&HIGHEST_SALES);

-GOTO :L_cars_by_sales

-SET &PROMPT_VAL='';
-SET &TERM_VAR='CAR';
-SET &TERM_VAR_DESC='Select Car';
-SET &TERM_MULTI='';
-SET &FEX_NAME='cars_by_sales';
-SET &INCLU_TERM=&HIGHEST_SALES;
DEFINE FILE H_CAR_SALES_0
CAR_DESC/A255=SQUEEZ(255,CAR || ( ' ' || FTOA(SALES,'(F6)','A7') || ''),'A255');
END

TABLE FILE H_CAR_SALES_0
BY CAR AS 'P_VAL'
BY CAR_DESC AS 'P_DESC'
ON TABLE HOLD AS H_P_VALUES
END

-IF '&PROMPT_VAL.EVAL' EQ '' THEN GOTO LBLDROPDOWN;
-:L_cars_by_sales


-*This is used instead of &YYMD since the values in GGSALES are older.
-DEFAULTH &DEF_DATE='19961101';

-SET &CUR_DATE_YEAR=DATECVT(&DEF_DATE,'I8YYMD','YY');
-SET &CUR_DATE_MONTH=DATECVT(&DEF_DATE,'I8YYMD','M');
-SET &CUR_DATE_DAY=DATECVT(&DEF_DATE,'I8YYMD','D');

DEFINE FILE GGSALES
DEF_DATE_YEAR_VAL/YY=DATE;
DEF_DATE_MONTH_VAL/M=DATE;
DEF_DATE_MONTH_D/MTR=DATE;
DEF_DATE_DAY_VAL/D=DATE;
DEF_DATE_YEAR/A4=EDIT(DEF_DATE_YEAR_VAL);
DEF_DATE_MONTH/A2=EDIT(DEF_DATE_MONTH_VAL);
DEF_DATE_MONTH_DESC/A20=FPRINT(DEF_DATE_MONTH_D,'Mtr','A20');
DEF_DATE_DAY/A2=EDIT(DEF_DATE_DAY_VAL);
END

-SET &PROMPT_VAL='';
-SET &TERM_VAR='GG_YEAR';
-SET &TERM_VAR_DESC='Select Year';
-SET &TERM_MULTI='';
-SET &FEX_NAME='ggsales_years';
-SET &INCLU_TERM=&CUR_DATE_YEAR;
TABLE FILE GGSALES
BY DEF_DATE_YEAR AS 'P_VAL'
ON TABLE HOLD AS H_P_VALUES
END

-IF '&PROMPT_VAL.EVAL' EQ '' THEN GOTO LBLDROPDOWN;
-:L_ggsales_years

-SET &PROMPT_VAL='';
-SET &TERM_VAR='GG_MONTH';
-SET &TERM_VAR_DESC='Select Month';
-SET &TERM_MULTI='';
-SET &FEX_NAME='ggsales_months';
-SET &INCLU_TERM=&CUR_DATE_MONTH;
TABLE FILE GGSALES
BY DEF_DATE_MONTH AS 'P_VAL'
BY DEF_DATE_MONTH_DESC AS 'P_DESC'
ON TABLE HOLD AS H_P_VALUES
END

-IF '&PROMPT_VAL.EVAL' EQ '' THEN GOTO LBLDROPDOWN;
-:L_ggsales_months

-SET &PROMPT_VAL='';
-SET &TERM_VAR='GG_DAY';
-SET &TERM_VAR_DESC='Select Day';
-SET &TERM_MULTI='';
-SET &FEX_NAME='ggsales_days';
-SET &INCLU_TERM=&CUR_DATE_DAY;
TABLE FILE GGSALES
BY DEF_DATE_DAY AS 'P_VAL'
ON TABLE HOLD AS H_P_VALUES
END

-IF '&PROMPT_VAL.EVAL' EQ '' THEN GOTO LBLDROPDOWN;
-:L_ggsales_days

-SET &PROMPT_VAL='';
-SET &TERM_VAR='GG_DATE';
-SET &TERM_VAR_DESC='Select Date';
-SET &TERM_MULTI='';
-SET &FEX_NAME='ggsales_dates';
-SET &INCLU_TERM=&DEF_DATE;
TABLE FILE GGSALES
BY DATE AS 'P_VAL'
ON TABLE HOLD AS H_P_VALUES
END

-IF '&PROMPT_VAL.EVAL' EQ '' THEN GOTO LBLDROPDOWN;
-:L_ggsales_dates


-GOTO LBLEND

-LBLDROPDOWN
-RUN

-SET &P_VAL=' ';
-SET &P_DESC='';
-SET &P_TABLE='H_P_VALUES';
-SET &N=1;
-REPEAT :L_DDL_TERMS FOR &N FROM 1 TO &LINES
-READFILE &P_TABLE 
-SET &PROMPT_VAL=&PROMPT_VAL || DECODE &N(1 '' ELSE ',') || '<' || DECODE &P_DESC('' '' ELSE '&P_DESC.EVAL') || DECODE &P_DESC('' '' ELSE ',') || &P_VAL || '>';
-:L_DDL_TERMS
-CLOSE &P_TABLE

-SET &PROMPT_VAL=DECODE &TERM_MULTI('' '' 'OR' 'OR(' 'AND' 'AND(') || TRUNCATE(&PROMPT_VAL) || DECODE &TERM_MULTI('' '' ELSE ')');

-*Commented out so if try to run this won't accidently be making files in random places. Uncomment once know where you want them and have update the value in &FILE_DEF.
-*FILEDEF INCL_UPDATE DISK &FILE_DEF..&FEX_NAME...FEX
-*-RUN
-*-WRITE INCL_UPDATE -*&FEX_NAME...fex uses &|&TERM_VAR
-*-WRITE INCL_UPDATE -DEFAULT &|&TERM_VAR='&INCLU_TERM.EVAL';
-*-WRITE INCL_UPDATE -PROMPT &|&TERM_VAR...(&PROMPT_VAL).&TERM_VAR_DESC...
-*-CLOSE INCL_UPDATE
-*-RUN

-TYPE &FEX_NAME ::  -*&FEX_NAME...fex uses &|&TERM_VAR
-TYPE &FEX_NAME ::  -DEFAULT &|&TERM_VAR='&INCLU_TERM.EVAL';
-TYPE &FEX_NAME ::  -PROMPT &|&TERM_VAR...(&PROMPT_VAL).&TERM_VAR_DESC...
-TYPE :L_&FEX_NAME.EVAL

-GOTO :L_&FEX_NAME.EVAL

-LBLEND
-RUN
-SET &E_TIME=&TOD;
-TYPE Ending Time &E_TIME


WF: 8201, OS: Windows, Output: HTML, PDF, Excel
 
Posts: 78 | Registered: November 08, 2010Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SHARING] Semi-Dynamic Prompt Includes with Defaults

Copyright © 1996-2020 Information Builders