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,

Couldn't you set the default value for BATCH to _FOC_NULL?

-DEFAULTH &BATCH=_FOC_NULL;

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:

[CLOSED]Reset Calendar Control

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.

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.

Try this:

-DEFAULTH &BATCH = _FOC_NULL;

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.

RobertF,

I just checked a WebFOCUS report I created similar to what you are trying to do, and I did not select "Add 'No selection' value".

I chose "Add ignore value" instead, and I changed the Display name to "ALL".

I put:

-? &
-EXIT



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.

RE:
I just checked a WebFOCUS report I created similar to what you are trying to do, and I did not select "Add 'No selection' value".

I chose "Add ignore value" instead, and I changed the Display name to "ALL".



We are on WF 8008...I do not see the the Ignore option you describe for my html controls.

Hi RobertF,

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,

Okay, then try this in your FEX instead:

WHERE
-IF &BATCH.EXISTS EQ 0 GOTO NOGO1;
 ( TBLMSTR_DATA.TBLMSTR_ DATA.BATCH NE & BATCH .(OR(FIND TBLMSTR_ DATA.TBLMSTR_ DATA. BATCH ,TBLMSTR_DATA.TBLMSTR_ DATA. BATCH IN TBLMSTR_ DATA)). BATCH . );
-NOGO1

I like MartinY's idea better, try that.

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,

[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.


I wish I could insert a print screen here!

Ok, wait....tried this too..

-DEFAULTH &BATCH = FOC_NONE;

no change...getting confused on FOC_NONE and FOC_NULL....

Hi RobertF,

Below sample HTML and FEX that does work.
HTML (generate with HTML composer WF8009):

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN"
"http://www.w3.org/TR/html4/strict.dtd">
<HTML><HEAD>
<META id=RLT_STANDARDS_MODE_META http-equiv=X-UA-Compatible content=IE=9>
<META name=mycharsetmeta http-equiv=Content-Type content="text/html; charset=ISO-8859-1">
<META name=Generation content="Created in release 8009, Generation 3">
<SCRIPT type=text/javascript>//confidential_id=IBI_OptionsScript
var szHtmlAlias="/ibi_apps/ibi_html";var szRunTimeHtmlAlias="runTimeHtmlAlias";var cgipath="cgipath";var ibirls="ibirls3";var rltdyncalendar="rltdyncalendar";var map="ibimap";var olap="olap";var olappanebase="olappanebase";var olapdrill="olapdrill";var ibiOptions = new Array(cgipath,ibirls);var nlsScript="/javaassist/nls.js";var glbScript="/javaassist/ibi/html/js/ibigbl.js";var replacePart="<replace>";
var scriptTemplate='<SCRIPT src="'+replacePart+'" type="text/javascript"><\/SCRIPT>';if(typeof(szRunTimeHtmlAlias) === 'string' && szRunTimeHtmlAlias.indexOf('/') == 0)szHtmlAlias=szRunTimeHtmlAlias;document.write(scriptTemplate.replace(replacePart, szHtmlAlias + nlsScript));document.write(scriptTemplate.replace(replacePart, szHtmlAlias + glbScript));</SCRIPT>

<SCRIPT type=text/javascript>//confidential_id=IBI_ibigblloadCss
if(typeof ibigblloadCss === 'function'){ibigblloadCss(null);addIntlTranslatedJS("composertrans.js");}else {alert("JavaScript alias '/ibi_apps/ibi_html'  is not valid");window.location("about:blank");}</SCRIPT>
<TITLE>HtmlPage</TITLE>
<SCRIPT type=text/javascript>//confidential_id=clientEventHandlersJS


//Begin function window_onload
function window_onload() {
 
UpdateData();
 
}

function button1_onclick(ctrl) {
OnExecute(ctrl)
}
</SCRIPT>

<SCRIPT for=window type=text/javascript eventname="onload">window.onload = function() { window_onload(); }</SCRIPT>
<LINK id=ITEM2 rel=stylesheet type=text/css UserSuppliedFullPath="1" rtFileName="cgipathsub/ibi_html/javaassist/ibi/html/composer/themes/nonBindows/IBI-Themes/default_theme.css"></HEAD>
<BODY style="OVERFLOW: auto" maptype="0" edaconnectionrequired="true" elementtype="21" thumbnailscale="4" nextelementuniquenumber="7"><SELECT tabIndex=1 class="IBIfield IBI_ReportControlTarget IBI_rounded_s" id=edit1 style="HEIGHT: 100px; WIDTH: 120px; POSITION: absolute; LEFT: 50px; Z-INDEX: 0; TOP: 50px" spellcheck=false size=3 multiple addquotes="yes" requiredfield="0" persistentuniqueid="compUid_1" defaultselection="1" rawvalue="E" start="fileopen" readOnly="false" hspace="0" maxLength="2147483647" loop="1" CHECKED="false" width="0" indeterminate="false" vspace="0">
</SELECT> <INPUT tabIndex=2 id=button1 language=javascript style="HEIGHT: 25px; WIDTH: 165px; POSITION: absolute; LEFT: 50px; Z-INDEX: 1; TOP: 170px" onclick=button1_onclick(this) type=button value=Button requests_list="1" autoExecute="false" name="button1">  <INPUT id=layoutinfo style="POSITION: absolute; LEFT: -100px; TOP: -100px" type=hidden resourcectrlids="ITEM2" name="inputhidden1"><INPUT id=ibiapp_app style="POSITION: absolute; LEFT: -100px; TOP: -100px" type=hidden value=std_reports ismre="1" name="ibiapp_app"><INPUT id=ibif_ex style="POSITION: absolute; LEFT: -100px; TOP: -100px" type=hidden value=/WFC/Repository/itr6g4h7/std_reports/reportsi3mkm/issue.htm name="ibif_ex"><xml id=focus_xmlelement><script type="text/xml" nextelementuniquenumber="4">
	<rootxmlnode focoption="_FOC_NULL">
		<variables>
			<variable controltype="9" parametercreatedinreslay="0" type="default" desc="Country" name="CNTRY" default=" " textvarname="" accept="0" select="1" create="1"><link linktype="default" from="compUid_1"><condition default="1" name="Default" whattodowithcontrol="4" valuescompareoperator="0" parameterscompareoperator="0" conditionmultiselectoperator="0"><data_info datatype="1" modifiedrequest="0" displayfield="COUNTRY" ibiformat="A10" datafield="COUNTRY" datasource="CAR.mas" ibif_ex="CAR" selectedvalue=" " operation="OR" slider_range_from="" slider_range_to="" previewvalue=" " sourcetype="typeMaster"><static_values><static value=" " display=" " selected="1" noinput="0">
								</static></static_values></data_info></condition></link><requestid id="1"></requestid></variable></variables>
		<input_controls>
			<input_control bindcontrolid="compUid_1" elementtype="9" name="edit1" id="edit1" multiple="1" onetimepopulated="0" inbinding="1" textvarname="CNTRY_TEXT">
				<link linktype="default" persistentuniqueid="compUid_1">
					<condition default="1" name="Default" whattodowithcontrol="0" valuescompareoperator="0" parameterscompareoperator="0" conditionmultiselectoperator="0">
						<data_info checkForDuplicateValues="0" accept="0" sourcetype="typeMaster" datatype="1" modifiedrequest="1" datafield="COUNTRY" displayfield="COUNTRY" selectedvalue="" addalloption="0" operation="OR" addnoselectionoption="1" dynnoselectiondisplayvalue="No Exclusion" dynalldisplayvalue="No Exclusion">
							<![CDATA[TABLE FILE CAR
PRINT COUNTRY
BY COUNTRY
ON TABLE PCHOLD FORMAT XML
END]]>
</data_info></condition></link></input_control>
		</input_controls>
		<other_bound_objects></other_bound_objects>
		<requests nextrequestsid="2">
			<request requestid="1" sourcetype="typeFex" targettype="window" targetname="_blank" ibif_ex="/WFC/Repository/itr6g4h7/std_reports/reportsi3mkm/zzzzz.fex" activereport="0" reportcolumns="" reportrealcolumns="" ibiapp_app="std_reports"></request></requests></rootxmlnode></script>
</xml></BODY>
<SCRIPT type=text/javascript>//confidential_id=IBI_loader
if(typeof doBeforeLoad === 'function'){doBeforeLoad();}function AdjustChildrenPosition(){
}
</SCRIPT>
</HTML>
<!-- cc lfpp -->

FEX (called by the button):

-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

RobertF,

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.

Thanks All.....I got called away butI put in the:

-? &
-EXIT

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



..perhaps something with the WHERE statement...

So if you only pick one batch to exclude it works? What is being sent to your FEX's &BATCH parameter when you pick only one versus two?

Missing a semicolon somewhere in your WHERE clause?

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.

I will try this...I am in training today but will try to get back into this soon...Ifeel I am close...

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


Again...make the AND an OR...works fine...

I can reproduce your error.

For some reason, when "AND" is used here is what is passed to the fex file:

&BODYTYPE     = CONVERTIBLE AND COUPE

Here is what happens when "OR" is used instead:

&BODYTYPE     = 'CONVERTIBLE' OR 'COUPE'

The selected values are not quoted when "AND" is used... strange. I don't know why, but that seems to be the cause of the error.

Try putting &BODYTYPE in single quotes.

WHERE CAR.BODY.BODYTYPE NE '&BODYTYPE.(AND(FIND CAR.BODY.BODYTYPE,CAR.BODY.BODYTYPE IN CAR)).BODYTYPE.';

I'm not sure if a Multiselect AND is the right way to go though.

Okay, that gets rid of the error, but "AND" does not exclude records that contain the user-selected values.

Aren't you trying to exclude records? Contrary to common sense, using "OR" does seem to exclude the correct records.

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)".

Robert,

Try this

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

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!