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.
We have a mini dashbboard consisting of some reports an graphs. The user first selects from the various report parameters (which are MULTI SELECT OR in the fex, chained on the HTL page) then hits the run button. All is good.
I was asked if its easily possible to add a NEW prompted parameter: BATCH to each report fex and graph fex. It's purpose is to allow the user to select which BATCHES are NOT to be included in selection. As such, I added a new MULTI SELECT OR / 'does not equal' parameter to each report fex and graph fex. I also added the new parameter to my html page. Note it is NOT chained to other parameters.
All runs fine...if the user selects one or more batches to exclude.
However if the user does not want to exclude ANY batches...well..it does not like that...it **wants** something to be selected. There is a html control option for: no selection but using it solves nothing as the report fex apparently wants a value; when run the parameter gets outlined in red refusing to run until something is entered.
Essentially, if the user picks one or more batches, all is well. If they do not wish to exclude anything it can not run.
I guess one way around this is to make the BATCH fex paramter a: MultiSelect OR / equals paramter, set the html control to add an: ALL option and should the user wish to exclude *some* batches then first highlight all batch names then control click the ones they do NOT want. They will find that to be a a pain since there 100+ batch names. Likely they would jsut say to forget the enitr enhancement.
Hope this makes sense....any ideas?This message has been edited. Last edited by: RobertF,
If I use: -SET &BATCH = _FOC_NULL; then it now runs however it simply ignores and batches I select to exclude...ie it still includes everything.
If i try -DEFAULT &BATCH = _FOC_NULL; then when it goes to run th efex I added batch to, it pulls up a new prompt...even thought I already added it to the HTML page.
In either event, here is the WHERE condition:
WHERE ( TBLMSTR_DATA.TBLMSTR_ DATA.BATCH NE & BATCH .(OR(FIND TBLMSTR_ DATA.TBLMSTR_ DATA. BATCH ,TBLMSTR_DATA.TBLMSTR_ DATA. BATCH IN TBLMSTR_ DATA)). BATCH . );
I recommend you try BabakNYC's solution because it would be the simplest.
However, I recently ran into a situation where I had to use one or two date ranges in a FEX file. Either all four dates were being passed in by the user, or two of the dates were single space characters instead of actual dates. I needed more elaborate code instead of just _FOC_NULL because of a goofy IBI calendar issue... see more here:
So here is a more complicated way to possibly solve your issue. You could try to use Dialog Manager.
Put this at the top of your FEX file:
-? &
-EXIT
That will cause the FEX file to show you what parameters are being passed in by the HTML page. You should be able to see what is being passed in for your "batch" parameter when "No selection" is chosen.
Using that information you can do something like this in your FEX file's WHERE section:
WHERE
-IF &BATCH EQ '{Whatever No Selection is from my previous paragraph}' GOTO NOGO1;
-* This line would be your batch selection criteria, whatever that is... it will be skipped if there was no user selection for batch on the HTML page.
-NOGO1
It's possible that "No Selection" will result in no batch parameter being passed. If that's the case, you could set a default batch parameter early on in your FEX and test for it, like this:
-DEFAULT &BATCH = 'No selection';
There is also a way to test for the absense of a parameter:
IF &BATCH.EXISTS ...
But, again, BabakNYC's solution would be preferable if it works for you.
App Studio WebFOCUS 8.1.05M Windows, All Outputs
Posts: 594 | Location: Michigan | Registered: September 04, 2015
Remember there's a difference between -DEFAULT and -DEFAULTH. The former prompts you for the variable with a default value in the prompt and the latter defaults the parameter but doesn't prompt.
WebFOCUS 8206, Unix, Windows
Posts: 1853 | Location: New York City | Registered: December 30, 2015
I guess I am confused as to how to define the HTML paramter. If I do not click: Add 'No selction Option', I am required to make a selection I may not want. I checked the: Add "No Selection' Option but am confused as to what to type in the box?
I did also try: -DEFAULTH &BATCH = _FOC_NULL; after checking the Add No Selction Option and putting: 'none' in the box.
It ran but selectes all batches; even If i pick one to exclude.
at the top of the fex. I can see the values for all other paramters EXCEPT: BATCH...it does not appear in the list...whether I pick a value to exclude or pick the value I type into the No Selection box.
FYI, all other parameters have their controls set to Add ALL to list. It seems this setting ets the defaults passed to the paramters to be: _FOC_NULL.
I think you need to choose "Add ignore value" to your HTML control instead of "Add 'No selection' value".
That's the way I did it for a similar report and it works for me. See my previous post in this thread.
Combine that with this in your FEX:
-DEFAULTH &BATCH = _FOC_NULL;
Also, make sure you have chosen "BATCH" for your variable to use after the user makes a choice. When you click on your control and go to Settings, there is a "Selection to" edit box. You should be able to select your &BATCH variable by clicking the button to the right of the box. If you don't see it, you might need to change your -DEFAULTH back to -DEFAULT for your &BATCH variable in your FEX.
App Studio WebFOCUS 8.1.05M Windows, All Outputs
Posts: 594 | Location: Michigan | Registered: September 04, 2015
First to know it's that using NE with OR will not work since the NE need the AND condition. it must be read as "Not equal to this AND not equal to that..." So try this instead (I haven't test it):
WHERE NOT ( TBLMSTR_DATA.TBLMSTR_ DATA.BATCH NE & BATCH .(OR(FIND TBLMSTR_ DATA.TBLMSTR_ DATA. BATCH ,TBLMSTR_DATA.TBLMSTR_ DATA. BATCH IN TBLMSTR_ DATA)). BATCH . );
Second, I would add "Add 'No Selection' option" with 'No Exclusion' as the text and the "Selection required" option set to "No". All BATCH should then be selected (because 'No selection' should send a FOC_NONE or _FOC_NULL to your fex) and the control shouldn't be mandatory (no red box if not provided since 'Selection required' is set to 'No').This message has been edited. Last edited by: MartinY,
WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF In Focus since 2007
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013
Must have a -DEFAULTH &BATCH = FOC_NONE in your fex however.
Squatch, your solution will work 'till you have other conditions below the WHERE clause. The WHERE clause need at least one condition, the key word cannot stand alone... And because there is a semi-column at the and it's like it is the only condition...
Personally I prefer to code each condition on a separate WHERE. This way you can pass FOC_NONE to a parameter without impacting the whole WHERE clause but just the one it belong to.This message has been edited. Last edited by: MartinY,
WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF In Focus since 2007
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013
[quote]First to know it's that using NE with OR will not work since the NE need the AND condition. it must be read as "Not equal to this AND not equal to that..." So try this instead (I haven't test it):
WHERE NOT ( TBLMSTR_DATA.TBLMSTR_ DATA.BATCH NE & BATCH .(OR(FIND TBLMSTR_ DATA.TBLMSTR_ DATA. BATCH ,TBLMSTR_DATA.TBLMSTR_ DATA. BATCH IN TBLMSTR_ DATA)). BATCH . );
My bad...I copied the code and changed the parameter and changed EQ to NE....forgot about the OR! I fixed that in the fex:
WHERE ( TBLMSTR_DATA.TBLMSTR_DATA.BATCH NE &BATCH.(AND(FIND TBLMSTR_DATA.TBLMSTR_DATA.BATCH,TBLMSTR_DATA.TBLMSTR_DATA.BATCH IN TBLMSTR_DATA)).BATCH. );
I added this line at the top of the fex: -DEFAULTH &BATCH = _FOC_NULL;
In the html page, I finally found the Selection Required down below in the properties. I set it to NO.
I checked: Add 'No Selection' Option and entered: No Exclusion in the box.
I checked the Add 'ALL' option.
When I run the sucker, a box appears for batch. I can choose: No Selection, All or ctrl+click any combo of the batch names to exclude.
Running with: No exclusion yeilds everything. Running with: All or selecting a particular Batch....still yeilds everything. Ex: if I choose a batch to exclude...it still appears.
-DEFAULTH &CNTRY = FOC_NONE
-*SET XRETRIEVAL = OFF
SET EMPTYREPORT = OFF
TABLE FILE CAR
BY COUNTRY
WHERE NOT (COUNTRY EQ &CNTRY);
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
END
You will have to reassign the proper path
WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF In Focus since 2007
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013
In the Settings for your batch control, there should be a checkbox called "Send display value". You could try setting that, and then put these lines at the top of your FEX again:
-? &
-EXIT
Then look for your &BATCH or possibly &BATCH_TEXT parameter in the output screen. It should tell you what batches you have selected... something like "'Batch1' AND 'Batch3' AND 'Batch7'", etc. If you don't see that there is some kind of basic problem with that control.
App Studio WebFOCUS 8.1.05M Windows, All Outputs
Posts: 594 | Location: Michigan | Registered: September 04, 2015
I see the values for all my parameters EXCEPT the BATCH one I added..it's not even in the list. I delete the control off the html page and rebuilt it.
Now, its working EXCEPT if i pick two items to exclude...it throws some sort of weird error just after the hold file creation but before the report:
(FOC282) RESULT OF EXPRESSION IS NOT COMPATIBLE WITH THE FORMAT OF FIELD: WH$$$1 BYPASSING TO END OF COMMAND (FOC009) INCOMPLETE REQUEST STATEMENT 0 ERROR AT OR NEAR LINE 121 IN PROCEDURE prearsmry (FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: HOLDWQ BYPASSING TO END OF COMMAND
Semicolon is not a mandatory thing in a WHERE clause, it just mark the end if it.
Also add the following at the beginning of your fex:
SET SQLTOPTTF = ON
-* Deactivate SQL tracing
SET TRACEOFF = ALL
-* Disable the trace stamp (Date/Time etc)
SET TRACESTAMP= OFF
-* Set trace line wrapping - # of characters
SET TRACEWRAP = 72
-* Enable Trace for the SQL Translator
SET TRACEON = SQLTRANS
-* Show Optimization information in HTML page
SET TRACEON = SQLAGGR//CLIENT
-* Show SQL statements in HTML page
SET TRACEON = STMTRACE//CLIENT
-* Show SQL generated statement trace in HTML page
SET TRACEON = STMTRACE/1/CLIENT
-* Show SQL generated sub-statement trace in HTML page
SET TRACEON = STMTRACE/2/CLIENT
-* Activate SQL tracing
SET TRACEUSER = ON
SET MSG = ON
SET ALL = PASS
SET HOLDSTAT = ON
SET EMGSRV = ON
-SET &ECHO = ALL;
-? &
This will gives you more traces and info. You will also see the generated SQL.
Then put -RUN after each steps : that way you will have the related messages where it belong.
Finally, what is the format of BATCH ? Per example, if BATCH is formatted as A10 but your parameter is passed as a numeric (or vice versa), the WHERE clause will throw the error you have. Meaning:
WHERE BATCH NE 1234
when BATCH is an alpha will throw an error. You then need to have '1234' passed.
Because, I think, that you have multi-selection available, set the "Multiple: Add quotes" option to 'Yes' for your BATCH control.
WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF In Focus since 2007
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013
I think my issue is with the gui generated syntax for multiselct AND. I have it working with multiselct or; of course I need: AND. The AND throws errors...one of which is shown below using CAR data. Look at this code. Simply changing: AND to: OR...it runs fine....no errors.
TABLE FILE CAR PRINT CAR.BODY.BODYTYPE CAR.BODY.SEATS CAR.BODY.DEALER_COST CAR.SPECS.RPM WHERE CAR.BODY.BODYTYPE NE &BODYTYPE.(AND(FIND CAR.BODY.BODYTYPE,CAR.BODY.BODYTYPE IN CAR)).BODYTYPE.; ON TABLE SET PAGE-NUM NOLEAD ON TABLE NOTOTAL ON TABLE PCHOLD FORMAT HTML ON TABLE SET HTMLCSS ON ON TABLE SET STYLE * INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty, $ ENDSTYLE END
0 ERROR AT OR NEAR LINE 7 IN PROCEDURE ADHOCRQ FOCEXEC * (FOC258) FIELDNAME OR COMPUTATIONAL ELEMENT NOT RECOGNIZED: COUPE BYPASSING TO END OF COMMAND (FOC009) INCOMPLETE REQUEST STATEMENT
MartinY suggested you use "AND" with the "NE" conditional test, and I thought that was correct. But it appears "OR" is the correct choice after all.
From a programming point of view, using "AND" makes sense. But I think that "OR" has a different meaning in WebFOCUS. The "OR" seems to be combining all the user choices into a group BEFORE the "NE" is applied.
So it's more like "BATCH NE (All of these user choices)".
App Studio WebFOCUS 8.1.05M Windows, All Outputs
Posts: 594 | Location: Michigan | Registered: September 04, 2015
TABLE FILE CAR
BY CAR.ORIGIN.COUNTRY
WHERE CAR.ORIGIN.COUNTRY NE &COUNTRY.(OR(FIND CAR.ORIGIN.COUNTRY,CAR.ORIGIN.COUNTRY IN CAR)).Country.;
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
END
You'll see that it does exclude what you select even with a multi-select OR
WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF In Focus since 2007
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013
Hey all...just got home. I appreciate the attention one this one. To be honest I was more foccused on the AND vs OR not the EQ vs NE.
The intent is to let the user select BATCHES to be excluded...my thought was it should be a NE with a multiselect OR...then I was convinced AND...now I am not so sure! At this point I am just trying to rid myself of the syntax errors! I hope to get more time on this Thursady afternoon. I appreciate the help!