How do i pass multi value parameters chosen from a html launch page to a sql stored procedure?

Example: multi select list box
COLOR

Blue
Red
Yellow
(All 3 chosen from user)


PASSES INTO .FEX
WHERE SQLPROC.ANSWER.INPUT EQ &COLOR.(OR()).COLOR.;


Need to pass into sql stored proc in conditional logic as:

SQL:
WHERE
(@Color = '' OR Color in(USER CHOSEN VALUES FROM LIST BOX)

IF there is any examples close to what i'm trying to do, please let me know

I have to pass multiple values from multiple list boxes to a drill down report that is populated by 1 sql stored proc.
Quite new to this & there is not a lot of information on this.

THank u

This message has been edited. Last edited by: <Kathryn Henning>,

WebFOCUS 7.6
Windows, All Outputs

Nico,

First, welcome to FocalPoint.
I don't know what type of launch page you wrote.
However, I assume that you have a list box with multiple values.
Let's take an example with the CAR file and a TABLE request:

  
TABLE FILE CAR
SUM
     SALES
BY  LOWEST COUNTRY
BY  LOWEST SEATS
WHERE SEATS EQ &SEATS;
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = endeflt,
$
ENDSTYLE
END

Your list box, taking the values of SEATS from the file allows you to choose 2, 4, and 5. Say you chose 2 and 4. The HTML composer will then substitute &SEATS with the chosen values:

  
TABLE FILE CAR
SUM
     SALES
BY  LOWEST COUNTRY
BY  LOWEST SEATS
WHERE SEATS EQ 2 OR 4;
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = endeflt,
$
ENDSTYLE
END

Now, what happens if you want to use IN instead of EQ:

  
TABLE FILE CAR
SUM
     SALES
BY  LOWEST COUNTRY
BY  LOWEST SEATS
WHERE SEATS IN (&SEATS);
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = endeflt,
$
ENDSTYLE
END

The composer is still going to generate 2 OR 4. So, with a bit of dialog manager:

  
-SET &SLEN=&SEATS.LENGTH;
-SET &SEATS=STRREP(&SLEN, &SEATS, 2, 'OR', 1, ',', &SLEN, 'A&SLEN.EVAL');
TABLE FILE CAR
SUM
     SALES
BY  LOWEST COUNTRY
BY  LOWEST SEATS
WHERE SEATS IN (&SEATS);
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = endeflt,
$
ENDSTYLE
END

You can apply this to an SQL WHERE clause.

Hi:

Thank you for the quick response & your example is very helpful. However when i try to implement your example using multiple options, i am not able to get it to resolve.
I AM able to choose 1 value OR All values, but not multiple values.

In my html page for my parameter i'm passing in, i have multiple options chosen in the properties window also, include quotes.

My where clause in my .fex is:

WHERE VALUE.VALUE.PARAMETER IN ('&PARAMETER);

When i implement my html page, i can choose 1 value only:
Error message is:

'Value1' OR 'Value2' OR 'Value3' OR 'Value4'
0 ERROR AT OR NEAR LINE 14 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC257) MISSING QUOTE MARKS: 'Value4'
(FOC009) INCOMPLETE REQUEST STATEMENT
BYPASSING TO END OF COMMAND


Please advise when u can
Thank you very much.

I don't think you can mix the IN selector with OR operators. The following should give you a better chance:

WHERE VALUE.VALUE.PARAMETER EQ &PARAMETER;

Please note also that you probably won't need to enclose your variable in quotes as HTML Composer would be doing that for you, but testing is your best friend.

And nico, remove the quote before the amper : ('& in your WHERE clause.

Thank you for your response. I was able to do a proof of concept & get multi value parameters to pass into a simple .fex based off a table with a simple where clause. However, I am stil not able to get them to pass into my sql stored proc.

AFter i modified my .fex logic to accept a simple
.INPUT.@PARAMTERVALUE EQ &PARAMTERVALUE .(OR()).PARAMTERVALUE .;


In my .fex code, i can see this being passed correctly in:
WHERE .INPUT.@PARAMTERVALUE EQ 'Value1' OR 'Value2';



However, i cannot get it to pass a option OR cannot get it to pass these values correctly to my sql stored proc:
sql condition syntax is:

WHERE
(@PARAMTERVALUE = 'All' OR ClaimTypeName in (@PARAMTERVALUE ))


ERROR MESSAGE @ BOTTOM OF .FEX:

(FOC1631) MORE THAN ONE IF/WHERE TEST FOR FIELD @PARAMTERVALUE

Please let me know ur thought when u get time.
thanks

WebFOCUS 7.6
Windows, All Outputs

I missed the part in your original post about having to send this information to a stored procedure.

HTML Composer was designed to produce WebFOCUS-friendly code, not SQL passthru or other code.

In the presence of multi-value parameters, Composer will nicely give you a string of OR-separated values because that's how WebFOCUS handles it:

'value1' OR 'value2' OR 'value3'

No SQL engine I know of understands "OR" in that context, as they would expect a comma instead:

WHERE field IN ('value1','value2','value3')

Some Dialogue Manager can come in handy in that case so you can convert your " OR " values into ", " ones:

...
-* Convert ' OR ' to ', ' in the parameter list
-SET &PARAMTERVALUE = STRREP (&PARAMTERVALUE.LENGTH, &PARAMTERVALUE, 4, ' OR ', 2, ', ', &PARAMTERVALUE.LENGTH, 'A&PARAMTERVALUE.LENGTH');
...
-* Pass it to stored procedure / SQL passthru
WHERE VALUE.VALUE.PARAMETER IN (&PARAMTERVALUE);
...

Hi:
Thank you for your reply. However, when i try to implement the suggestions above, i still get a error message when trying to pass multi values into my sql.

The error message i have now is:

.fex code:
TEST.INPUT.@PARAMETERVALUE EQ &PARAMETERVALUE.(OR()).PARAMETERVALUE.;

values being passed in:
WHERE INPUT.@PARAMETERVALUE EQ 'Final', 'Final-Resubmit' ;


sql:
where PARAMETERVALUE IN (@PARAMETERVALUE )


0 ERROR AT OR NEAR LINE 16 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC224) SYNTAX ERROR: ,
BYPASSING TO END OF COMMAND
(FOC009) INCOMPLETE REQUEST STATEMENT

Please let me know your thoughts when you can
thank u very much.

Nico,
Could you post your fex?
It could help to see where the problem lies.

Hi:

I'm just trying to do a simple POC that this can actually be passed into my .sql, which i have not been able to do with multiple options, only single values OR all

My sql where clause is very simple:
WHERE
(@ClaimType = '' OR ClaimTypeName in (@ClaimType))


.FEX

-? &
-SET &ECHO=ALL;

-SET &CLAIMTYPE = STRREP (&CLAIMTYPE.LENGTH, &CLAIMTYPE, 4, ' OR ', 2, ', ', &CLAIMTYPE.LENGTH, 'A&CLAIMTYPE.LENGTH');

TABLE CLAIMSTYPE
PRINT
CLAIMSTYPE.ANSWERSET1.CLAIMNUMBER
CLAIMSTYPE.ANSWERSET1.CLAIMSTATUS
CLAIMSTYPE.ANSWERSET1.TRIGGEREVENTTYPENAME
CLAIMSTYPE.ANSWERSET1.CLAIMTYPENAME
WHERE CLAIMSTYPE.INPUT.@CLAIMTYPE EQ &CLAIMTYPE .(OR()).CLAIMTYPE .;

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/BIRS05/_EDAHOME/ETC/endeflt.sty,
$
TYPE=REPORT,
COLUMN=N4,
WRAP=6.000000,
$
TYPE=REPORT,
COLUMN=N3,
WRAP=6.000000,
$
TYPE=REPORT,
COLUMN=N2,
WRAP=6.000000,
$
ENDSTYLE
END



ERROR MESSAGE ON HTML:

0 ERROR AT OR NEAR LINE 14 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC224) SYNTAX ERROR: ,
BYPASSING TO END OF COMMAND
(FOC009) INCOMPLETE REQUEST STATEMENT

WebFOCUS 7.6
Windows, All Outputs

As far as I can tell, you've built an HTML launch page using HTML Composer.

The parameter that the multi-select listbox is bound to can only have 'OR' or 'AND' as the separator property: "Options are Single select, Multiselect OR, and Multiselect AND" - documentation.

You need a comma separator. This is what I'd do - add Dialogue Manager code to replace the OR separator by comma:

-SET &COUNTRY = STRREP (&COUNTRY.LENGTH, &COUNTRY, 2, 'OR', 1, ',', &COUNTRY.LENGTH, 'A&COUNTRY.LENGTH');

This is a complete, working example - an HTML Composer generated page that has a multi-select list box that runs a SQL report using the CAR file.

<!-- Generated by Report Layout Painter -->
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<HTML>
<HEAD>
<META id=mycharsetmeta content="text/html; charset=ISO-8859-1" http-equiv=Content-Type>
<META id=Generation content="Created in release 7705, Generation 3.0">
<SCRIPT id=IBI_RelCallBack type=text/javascript>function AdjustChildrenPosition(){
}
</SCRIPT>
 
<SCRIPT id=IBI_OptionsScript type=text/javascript>
var cgipath = "cgipath";
var ibirls = "ibirls3";
 
var rltdyncalendar = "rltdyncalendar";
var gmap = "ibigmap";
var olap="olap";
var olappanebase="olappanebase";
var olapdrill="olapdrill";
 
var ibixmltree="ibixmltree";
 
var ibiOptions = new Array(cgipath,ibirls);
</SCRIPT>
 
<SCRIPT id=IBI_nls type=text/javascript src="/ibi_html/javaassist/nls.js"></SCRIPT>
 
<SCRIPT id=IBI_nlsVars type=text/javascript src="/ibi_html/javaassist/nlsvars.js"></SCRIPT>
 
<SCRIPT id=IBI_ibigbl type=text/javascript src="/ibi_html/javaassist/ibi/html/js/ibigbl.js"></SCRIPT>
 
<SCRIPT id=IBI_ibigblloadCss type=text/javascript>
ibigblloadCss(null);
addIntlTranslatedJS("composertrans.js");
</SCRIPT>
<TITLE>HtmlPage</TITLE>
<SCRIPT id=clientEventHandlersJS type=text/javascript>
//Begin function window_onload
function window_onload() {
 
UpdateData();
 
// TODO: Add your event handler code here
//add onInitialUpdate() function to make changes before initial run of the reports
}
//End function window_onload
</SCRIPT>
 
<SCRIPT for=window type=text/javascript eventname="onload">window.onload = function() { window_onload(); }</SCRIPT>
</HEAD>
<BODY style="OVERFLOW: auto" nextelementuniquenumber="1" thumbnailscale="4" elementtype="21" edaconnectionrequired="true">
<IFRAME style="Z-INDEX: 1; POSITION: absolute; WIDTH: 1250px; HEIGHT: 400px; TOP: 252px; LEFT: 10px" id=report1 tabIndex=1 elementtype="2" requests_list="0" autoExecute="false" name="report1"></IFRAME>
<SPAN style="Z-INDEX: 7; POSITION: absolute; WIDTH: 177px; HEIGHT: 230px; TOP: 10px; LEFT: 10px" id=panel1 tabIndex=6 elementtype="34">
<LABEL style="Z-INDEX: 5; POSITION: absolute; WIDTH: 77px; HEIGHT: 19px; CURSOR: default; TOP: 5px; LEFT: 5px" id=label1 tabIndex=4 for=listbox1 name="listbox1">COUNTRY</LABEL>
<SELECT style="Z-INDEX: 6; POSITION: absolute; OVERFLOW-Y: visible; WIDTH: 140px; HEIGHT: 146px; OVERFLOW: visible; TOP: 34px; LEFT: 5px" id=listbox1 tabIndex=5 multiple size=3 defaultselection="1" defaultlocation="0,0,54,120" operation="OR" boundtovariable="1" requiredfield="1" persistentuniqueid="compUid_1" name="listbox1"></SELECT>
<INPUT style="Z-INDEX: 8; POSITION: absolute; BACKGROUND-COLOR: lavender; WIDTH: 38px; BACKGROUND-REPEAT: no-repeat; HEIGHT: 22px; TOP: 185px; LEFT: 5px" id=button1 tabIndex=7 onclick=OnExecute(this); type=button requests_list="0" StyleBack_rtFileName="htmlpathsub/javaassist/ibi/html/describe/run16.gif" name="button1"></SPAN>
<INPUT style="POSITION: absolute; TOP: -100px; LEFT: -100px" id=layoutinfo type=hidden resourcectrlids="button1">
<INPUT style="POSITION: absolute; TOP: -100px; LEFT: -100px" id=ibiapp_app value=test type=hidden name="ibiapp_app">
<INPUT style="POSITION: absolute; TOP: -100px; LEFT: -100px" id=ibic_server value=EDASERVE type=hidden isdataserversarea="1" name="ibic_server">
<xml id=focus_xmlelement>
<script type="text/xml" nextelementuniquenumber="2">
<rootxmlnode focoption="_FOC_NULL">
<variables>
<variable controltype="9" parametercreatedinreslay="0" type="default" desc="COUNTRY" name="COUNTRY" default="FOC_NONE" textvarname="" accept="0" select="0" create="1">
<link linktype="default" from="compUid_1">
<condition default="1" name="Default" whattodowithcontrol="4" valuescompareoperator="0" parameterscompareoperator="0" conditionmultiselectoperator="0">
<data_info checkForDuplicateValues="0" displayfield="COUNTRY" datafield="COUNTRY" datasource="" datatype="0" selectedvalue="FOC_NONE" operation="" slider_range_from="" slider_range_to="" previewvalue="FOC_NONE" sourcetype="typeMaster">
<static_values>
<static value="_FOC_NULL" display="FOC_NONE" selected="1" noinput="0"></static></static_values></data_info></condition></link>
<requestid id="0"></requestid></variable></variables>
<input_controls>
<input_control bindcontrolid="compUid_1" elementtype="9" name="listbox1" id="listbox1" multiple="1" onetimepopulated="0">
<link linktype="default" persistentuniqueid="compUid_1">
<condition default="1" name="Default" whattodowithcontrol="0" valuescompareoperator="0" parameterscompareoperator="0" conditionmultiselectoperator="0">
<data_info checkForDuplicateValues="0" datatype="1" datasource="CAR.mas" addalloption="0" sourcetype="typeMaster" cacheruntimedata="0" accept="0" operation="NONE" modifiedrequest="0" ibiapp_app="test" displayfield="CAR.ORIGIN.COUNTRY" ibiformat="A10" dfformat="A10" datafield="CAR.ORIGIN.COUNTRY">
<![CDATA[TABLE FILE CAR
SUM FST.CAR.ORIGIN.COUNTRY
 BY CAR.ORIGIN.COUNTRY
-*insert_filters_here
ON TABLE PCHOLD FORMAT XML
END
]]></data_info></condition></link></input_control></input_controls>
<other_bound_objects></other_bound_objects>
<requests nextrequestsid="0">
<request requestid="0" targetname="report1" ibif_ex="report1" targettype="iframe" sourcetype="typeAdhocfex" activereport="0" compoundreport="0" reportcolumns="" reportrealcolumns="" ibiapp_app="">
<![CDATA[-*focexectool
-DEFAULT &COUNTRY = 'FOC_NONE';
-TYPE &COUNTRY
-SET &ECHO=ALL;
-SET &COUNTRY = STRREP (&COUNTRY.LENGTH, &COUNTRY, 2, 'OR', 1, ',', &COUNTRY.LENGTH, 'A&COUNTRY.LENGTH');
-TYPE &COUNTRY
SQL
SELECT COUNTRY, CAR, SUM(SALES)
FROM CAR
WHERE COUNTRY IN (&COUNTRY)
GROUP BY COUNTRY, CAR;
END]]></request></requests></rootxmlnode></script>
</xml></BODY>
<SCRIPT id=IBI_loader type=text/javascript>
doBeforeLoad();
</SCRIPT>
</HTML>
 

I'm not sure what the .(OR()). does here, but perhaps it sets the parameter control to "OR"...

quote:

WHERE SQLPROC.ANSWER.INPUT EQ &COLOR.(OR()).COLOR.;

quote:

In my .fex code, i can see this being passed correctly in:
WHERE .INPUT.@PARAMTERVALUE EQ 'Value1' OR 'Value2';

...
(FOC1631) MORE THAN ONE IF/WHERE TEST FOR FIELD @PARAMTERVALUE

If you are reporting using TABLE, against the synonym of a stored procedure, the procedure's parameters must be specified by a simple equality condition:


Syntax: How to Report Against a Stored Procedure Using the TABLE Command

To execute a stored procedure using the TABLE command, use the following syntax
TABLE FILE synonym
PRINT [parameter [parameter] ... | *]
[IF in-parameter EQ value]
.
.
.
END

That rules out use of
WHERE parameter IN( ... )
or
IF parameter EQ value OR value ...

Hence the FOC1631.

This message has been edited. Last edited by: j.gross,

Thank you very much for all your help, i was able to resolve

You ought to tell us how you resolved this. Was it based on j.gross's information?

No, i was not able to use the information provided by j.gross.

I was able to pass 2 values in using:
-? &
-SET &ECHO=ALL;

-* Convert ' OR ' to ', ' in the parameter list
-SET &Parameter = STRREP (&Parameter .LENGTH, &Parameter , 4, ' OR ', 2, ', ', &Parameter .LENGTH, 'A&Parameter .LENGTH');


I am able to use a IN clasue in my sql

in my .fex i am able to use:
@Parameter IN ('@Parameter ));

However, i have not been able to pass more then 2 values @ one time.

I keep gettin the message of:
FOC1400 SQLCODE IS 201 (HEX: 00000C9) XOPEN:S0004
L (FOC1406) SQL OPEN CURRER ERROR.

IS this a bug?
thanks

Which control you are using for the multiple values. If you are using TextArea in Composer page then there is a properties called "Multiple - set that to Multiple" and "Multiple:Add Quotes - set that to Yes"

That will allow you to send all the values to the report.

I've tried to use both list box & check box with multiple values & quotes selected

Thanks

What does passing more than two values look like? If you have
SET MSG=ON
in your code, you should be able to see what your SQL statement looks like with the value of &Parameter embedded...

WebFOCUS error 1406:

(FOC1406) SQL OPEN CURSOR ERROR. %1%2%3%4
An error condition occurred while attempting to open a cursor in theRDBMS. An additional error message specifying the appropriate RDBMS error code will usually accompany this message. Consult the appropriate RDBMS error message reference for proper corrective measures.

SQL error 201:

Procedure or function '' expects parameter '', which was not supplied.

If you are using sql stored procedure then try "|" symbol instead of OR.

That will work.

Hi:

Can u see what's wrong with my code from this simple example?
I can see there are multiple values trying to be passed in, but nothing happens.

Thank u

--------------------------------------------------------------------------------

Your request did not return any output to display.
Possible causes:
- No data rows matched the specified selection criteria.
- Output was directed to a destination such as a file or printer.
- An error occurred during the parsing or running of the request.


--------------------------------------------------------------------------------


CURRENTLY DEFINED & VARIABLES:
&&CONNECTION = Con
&&IBIBIPMS =
&&MAS_FULLINFO= Y
&&OPSYS = UNIX
&ACCEPTS = 0
&APPPRIV = /binfs00/app/ibi/apps
&APPROOT = /binfs00/app/ibi/apps
&AROUTPUT = SET AROUTPUT=EXTJS
&AUTOINDEX = NO
&BASEIO = 0
&CHNGD = 0
&DATE = 03/31/14
&DBMSERR = 0
&DELTD = 0
&DMY = 310314
&DMYY = 31032014
&DUPLS = 0
&ECHO = OFF
&EXCELSERVURL = SET EXCELSERVURL=http://vmbiapps06.dcsr.site:9080/ibi_apps
&FOCEXURL = /ibi_apps/WFServlet.ibfs?IBIF_webapp=/ibi_apps&IBIC_server=BI
&FOCFEXNAME = ADHOCRQ
&FOCFOCEXEC = _ADHOCRQ
&FOCGRAPHCNT = 0
&FOCHTMLURL = /ibi_apps/ibi_html/S56_13911305501F
&FOCINCLUDE = ADHOCRQ
&FOCNEXTPAGE = 0
&FOCREL = M728007D
&FORMAT = 0
&GOOGLEMAPSAP>=
&INDEXIO = 0
&INPUT = 0
&INVALID = 0
&LINES = 0
&MDY = 033114
&MDYY = 03312014
&NOMATCH = 0
&READS = 0
&RECORDS = 0
&REJECTS = 0
&SETFILE =
&SORTIO = 0
&TOD = 15.03.06
&TRANS = 0
&VALUES>= 'Fore' OR 'Foreclos'
&WFDESCRIBE = XMLPROMPT
&WF_TITLE = AdhocRequest
&YMD = 140331
&YYMD = 20140331
SET MSG=ON
-* Convert ' OR ' to ', ' in the parameter list
-SET &VALUE3= STRREP (50, 'Fore' OR 'Foreclos', 4, ' OR ', 2, ', ', 50, 'A50');
TABLE FILE DETAIL_TESTING
PRINT
DETAIL_TESTING.ANSWERSET1.VALUE1
DETAIL_TESTING.ANSWERSET1.VALUE2
DETAIL_TESTING.ANSWERSET1.VALUE3
DETAIL_TESTING.ANSWERSET1.VALUE4
DETAIL_TESTING.ANSWERSET1.VALUE5
WHERE ( DETAIL_TESTING.INPUT.@TIMEPERIOD EQ '09' )
AND ( DETAIL_TESTING.INPUT.@VALUE1 EQ '' )
AND ( DETAIL_TESTING.INPUT.@VALUE2 EQ 'ABC' );

WHERE (DETAIL_TESTING.INPUT.@VALUE3 IN ('@VALUE3));

ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
-INCLUDE _EDAHOME/ETC/endeflt.sty

WebFOCUS 7.6
Windows, All Outputs

You should turn SQL traces on to see what gets generated by the TABLE FILE DETAIL_TESTING command, assuming DETAIL_TESTING is your stored procedure.

-*** Set up SQL tracing
 
-*** Deactivate SQL tracing
SET TRACEOFF = ALL
 
-*** Show Commands and data exchange between the physical and
-*** the logical layers of the data adapter
-*SET TRACEON = SQLCALL
 
-*** Enable Trace for the SQL Translator
SET TRACEON = SQLTRANS
 
-*** Show SQL statements
SET TRACEON = STMTRACE//CLIENT
 
-*** Show Optimization information
SET TRACEON = SQLAGGR//CLIENT
 
-*** Show SQL generated statement trace
-*SET TRACEON = STMTRACE/1/CLIENT
 
-*** Show SQL generated sub-statement trace
-*SET TRACEON = STMTRACE/2/CLIENT
 
-*** Disable the trace stamp (Date/Time etc)
SET TRACESTAMP = OFF
 
-*** Set trace line wrapping - # of characters
SET TRACEWRAP = 132
 
-*** Set trace to output file
-*SET TRACEUSER=C:\ibi\apps\baseapp\my_sql_trace.txt
 
-*** Activate SQL tracing
SET TRACEUSER = ON
 
-RUN

quote:

Can u see what's wrong with my code

Yes, unbalanced quotes:
WHERE (DETAIL_TESTING.INPUT.@VALUE3 IN ('@VALUE3'));

Hi,

i missed the quote when i was trying to get my actual values out. There r quotes are in my .fex & I still cannot get this to work. When i was @ my focus user forum last week, they said this could be a bug & i'm starting to believe it could b. I can ONLY get multiple values to pass in when i use (IN LITERAL LIST) in painter, when i hard code the values, which i do not want to do. I need to get this to fuction using dynmaic .fex files that will grow once values are added to the dbase.


My sql where clause is this:
WHERE (TriggerTypeName in (@TriggerTypeName))


My .fex with sql tracing is this:

Your request did not return any output to display.
Possible causes:
- No data rows matched the specified selection criteria.
- Output was directed to a destination such as a file or printer.
- An error occurred during the parsing or running of the request.


--------------------------------------------------------------------------------


CURRENTLY DEFINED & VARIABLES:
&&IBIBIPMS =
&&MAS_FULLINFO= Y
&ACCEPTS = 0
&APPPRIV = /binfs00/app/ibi/apps
&APPROOT = /binfs00/app/ibi/apps
&AROUTPUT = SET AROUTPUT=EXTJS
&AUTOINDEX = NO
&BASEIO = 0
&CHNGD = 0
&DATE = 04/01/14
&DBMSERR = 0
&DELTD = 0
&DMY = 010414
&DMYY = 01042014
&DUPLS = 0
&ECHO = OFF
&EXCELSERVURL = SET EXCELSERVURL=http://vmbiapps06.dcsr.site:9080/ibi_apps
&FOCEXURL = /ibi_apps/WFServlet.ibfs?IBIF_webapp=/ibi_apps&IBIC_server=BI
&FOCFEXNAME = ADHOCRQ
&FOCFOCEXEC = _ADHOCRQ
&FOCGRAPHCNT = 0
&FOCHTMLURL = /ibi_apps/ibi_html/S56_13911305501F
&FOCINCLUDE = ADHOCRQ
&FOCNEXTPAGE = 0
&FOCREL = M728007D
&FORMAT = 0
&GOOGLEMAPSAP>=
&INDEXIO = 0
&INPUT = 0
&INVALID = 0
&LINES = 0
&MDY = 040114
&MDYY = 04012014
&NOMATCH = 0
&READS = 0
&RECORDS = 0
&REJECTS = 0
&SETFILE =
&SORTIO = 0
&TOD = 07.50.57
&TRANS = 0
&TRIGGERTYPEN>= 'AcceleratedClaim' OR 'DeedInLieu'
&WFDESCRIBE = XMLPROMPT
&WF_TITLE = AdhocRequest
&YMD = 140401
&YYMD = 20140401
SET MSG=ON
-*** Set up SQL tracing
-*** Deactivate SQL tracing
SET TRACEOFF = ALL
-*** Show Commands and data exchange between the physical and
-*** the logical layers of the data adapter
-*SET TRACEON = SQLCALL
-*** Enable Trace for the SQL Translator
SET TRACEON = SQLTRANS
-*** Show SQL statements
SET TRACEON = STMTRACE//CLIENT
-*** Show Optimization information
SET TRACEON = SQLAGGR//CLIENT
-*** Show SQL generated statement trace
-*SET TRACEON = STMTRACE/1/CLIENT
-*** Show SQL generated sub-statement trace
-*SET TRACEON = STMTRACE/2/CLIENT
-*** Disable the trace stamp (Date/Time etc)
SET TRACESTAMP = OFF
-*** Set trace line wrapping - # of characters
SET TRACEWRAP = 132
-*** Set trace to output file
-*SET TRACEUSER=C:\ibi\apps\baseapp\my_sql_trace.txt
-*** Activate SQL tracing
SET TRACEUSER = ON
-RUN
-* Convert ' OR ' to ', ' in the parameter list
-SET &TRIGGERTYPENAME = STRREP (34, 'AcceleratedClaim' OR 'DeedInLieu', 4, ' OR ', 2, ', ', 34, 'A34');
TABLE FILE CWS3001L_TESTING
PRINT
CWS3001L_TESTING.ANSWERSET1.INVESTOR
CWS3001L_TESTING.ANSWERSET1.TRIGGERTYPENAME
CWS3001L_TESTING.ANSWERSET1.CLAIMID
WHERE ( CWS3001L_TESTING.INPUT.@TRIGGERTYPENAME IN ('@TRIGGERTYPENAME') );
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
-INCLUDE _EDAHOME/ETC/endeflt.sty
type=report,
color=rgb(66 70 73),
font='Trebuchet MS',
size=9,
squeeze=on,
grid=off,
$
type=pagenum,
style=italic,
size=6,
justify=right,
$
type=tabheading,
style=bold+italic,
size=14,
justify=center,
$
type=tabfooting,
style=bold+italic,
size=12,
justify=center,
border-top=heavy,
border-top-color=rgb(52 55 58),
$
type=heading,
style=bold,
size=12,
justify=center,
$
type=footing,
style=bold,
size=10,
justify=center,
$
type=title,
backcolor=rgb(102 102 102),
color=rgb(255 255 255),
style=-underline+bold,
$
type=data,
backcolor=(rgb(255 255 255) rgb(235 235 240)),
$
type=acrosstitle,
border-top=light,
border-bottom=heavy,
border-top-color=rgb(52 55 58),
border-bottom-color=rgb(52 55 58),
size=9,
style=-underline+bold,
$
type=acrossvalue,
backcolor=rgb(218 225 232),
style=bold,
$
type=subhead,
size=10,
style=bold,border-top=heavy,
border-bottom=light,
border-top-color=rgb(52 55 58),
border-bottom-color=rgb(52 55 58),
$
type=subfoot,
size=10,
style=bold,
border-top=light,
border-bottom=medium,
border-top-color=rgb(52 55 58),
border-bottom-color=rgb(52 55 58),
$
type=subtotal,
backcolor=RGB(200 200 200),
style=bold,
border-top=light,
border-top-color=rgb(66 70 73),
$
type=grandtotal,
backcolor=rgb(66 70 73),
color=rgb(255 255 255),
style=bold,
border-top=light,
border-bottom=heavy,
border-top-color=rgb(66 70 73),
border-top-style=solid,
border-bottom-style=double,
$
TYPE=REPORT,
OBJECT=STATUS-AREA,
COLOR=white,
BACKCOLOR=rgb(102 102 102),
$
TYPE=REPORT,
OBJECT=CURRENT-ROW,
BACKCOLOR=RGB(200 200 200),
HOVER-BACKCOLOR=rgb(218 225 232),
$
TYPE=REPORT,
GRAPHCOLOR=green,
GRAPHCOLORNEG=red,
$
TYPE=REPORT,
OBJECT=CALC-AREA,
COLOR=white,
BACKCOLOR=rgb(102 102 102),
$
TYPE=REPORT,
OBJECT=MENU,
COLOR=white,
BACKCOLOR=rgb(102 102 102),
BORDER-COLOR=white,
HOVER-COLOR=rgb(66 70 73),
hover-backcolor=rgb(218 225 232),
$
*GRAPH_SCRIPT
setFillColor(getSeries(0),new Color(48,94,213));
setFillColor(getSeries(1),new Color(237,195,27));
setFillColor(getSeries(2),new Color(136,144,151));
setFillColor(getSeries(3),new Color(140,205,40));
setFillColor(getSeries(4),new Color(227,136,0) );
setFillColor(getSeries(5),new Color(40,190,205) );
setFillColor(getSeries(6),new Color(255,102,102) );
setFillColor(getSeries(7), new Color(71,186,143));
setFillColor(getSeries(8), new Color(187,90,71));
setFillColor(getSeries(9),new Color(190,107,211));
setFillColor(getSeries(10),new Color(16,122,22));
setFillColor(getSeries(11),new Color(108,149,200));
setFillColor(getSeries(12),new Color(169,109,205) );
setFillColor(getSeries(13),new Color(210,143,255) );
setFillColor(getSeries(14),new Color(60,49,36));
setFillColor(getSeries(15),new Color(78,43,37));
setFillColor(getSeries(16),new Color(95,38,37));
setFillColor(getSeries(17),new Color(19,23,162));
setFillColor(getSeries(18),new Color(66,70,73));
setFillColor(getSeries(19),new Color(19,23,162));
setFillColor(getSeries(20),new Color(66,70,73));
setFillColor(getSeries(21),new Color(191,218,170));
setFillColor(getSeries(22),new Color(0,0,128));
setFillColor(getSeries(23),new Color(163,211,208));
setFillColor(getSeries(24),new Color(158,213,243));
setFillColor(getSeries(25),new Color(179,151,191));
setFillColor(getSeries(26),new Color(200,158,193));
setFillColor(getSeries(27),new Color(240,171,196));
setFillColor(getSeries(28),new Color(242,171,168));
setFillColor(getSeries(29),new Color(230,121,88));
setFillColor(getSeries(30),new Color(240,159,97));
setFillColor(getSeries(31),new Color(248,191,106));
setFillColor(getSeries(32),new Color(255,239,121));
setFillColor(getSeries(33),new Color(195,218,125));
setFillColor(getSeries(34),new Color(0,0,255));
setFillColor(getSeries(35),new Color(0,255,0));
setFillColor(getSeries(36),new Color(255,0,255));
setFillColor(getSeries(37),new Color(255,255,0));
setFillColor(getSeries(38),new Color(255,0,0));
setFillColor(getSeries(39),new Color(255,200,0));
setFillColor(getSeries(40),new Color(255,175,175));
setFillColor(getSeries(41),new Color(0,255,255));
setFillColor(getSeries(42),new Color(206,0,54));
setFillColor(getSeries(43),new Color(255,239,0));
setFillColor(getSeries(44),new Color(0,152,82));
setFillColor(getSeries(45),new Color(0,174,227));
setFillColor(getSeries(46),new Color(42,19,109));
setFillColor(getSeries(47),new Color(206,0,121));
setFillColor(getSeries(48),new Color(243,169,136));
setFillColor(getSeries(49),new Color(248,190,145));
setFillColor(getSeries(50),new Color(253,211,154));
setFillColor(getSeries(51),new Color(255,243,172));
setFillColor(getSeries(52),new Color(217,229,171));
setFillColor(getSeries(53),new Color(191,218,170));
setFillColor(getSeries(54),new Color(165,208,169));
setFillColor(getSeries(55),new Color(163,211,208));
setFillColor(getSeries(56),new Color(158,213,243));
setFillColor(getSeries(57),new Color(179,151,191));
setFillColor(getSeries(58),new Color(200,158,193));
setFillColor(getSeries(59),new Color(240,171,196));
setFillColor(getSeries(60),new Color(242,171,168));
setFillColor(getSeries(61),new Color(230,121,88));
setFillColor(getSeries(62),new Color(240,159,97));
setFillColor(getSeries(63),new Color(248,191,106));
setBorderColor(getO1AxisLine(),new Color(130,130,130));
setBorderColor(getY1AxisLine(),new Color(130,130,130));
setFillColor(getY1Title(),new Color(66,70,73));
setFillColor(getY1Label(),new Color(66,70,73));
setFillColor(getY2Label(),new Color(66,70,73));
setFillColor(getY3Label(),new Color(66,70,73));
setFillColor(getY4Label(),new Color(66,70,73));
setFillColor(getY5Label(),new Color(66,70,73));
setFillColor(getO1Title(),new Color(66,70,73));
setFillColor(getX1Title(),new Color(66,70,73));
setFillColor(getO1Label(),new Color(66,70,73));
setFillColor(getO2Label(),new Color(66,70,73));
setFillColor(getX1Label(),new Color(66,70,73));
setFillColor(getLegendText(),new Color(66,70,73));
setFillColor(getFrame(),new Color(235,235,240));
setFillColor(getChartBackground(),new Color(255,255,255));
setBorderColor(getY1MajorGrid(),new Color(204,204,204));
setBorderColor(getY1MinorGrid(),new Color(204,204,204));
setBorderColor(getY2MajorGrid(),new Color(204,204,204));
setBorderColor(getY2MinorGrid(),new Color(204,204,204));
setBorderColor(getO1MajorGrid(),new Color(204,204,204));
setBorderColor(getO1MinorGrid(),new Color(204,204,204));
setBorderColor(getX1MajorGrid(),new Color(204,204,204));
setBorderColor(getX1MinorGrid(),new Color(204,204,204));
setTransparentBorderColor(getCubeFloor(),true);
setTransparentBorderColor(getCubeLeftWall(),true);
setTransparentBorderColor(getCubeRightWall(),true);
setCubeFocusFactor(95.83333182119424);
setCubePanX(41.3);
setCubePanY(54.7);
setCubeRotationMatrix(0.9527877123115605,-0.39648905742363183,1.0691265903653806,-0.012240167246073914,1.389589570659449,0.5262420232508634,-1.1402128823990627,-0.3462324595884829,0.8877371966710723);
setCubeViewerZ(93.61645050038162);
setCubeZoomFactor(55.0);
setCubeWallThickZ(0.0);
setCubeWallThickY(0.0);
setCubeWallThickX(0.0);
setTransparentBorderColor(getSeries(0),true);
setTransparentBorderColor(getSeries(1),true);
setTransparentBorderColor(getSeries(2),true);
setTransparentBorderColor(getSeries(3),true);
setTransparentBorderColor(getSeries(4),true);
setTransparentBorderColor(getSeries(5),true);
setTransparentBorderColor(getSeries(6),true);
setTransparentBorderColor(getSeries(7),true);
setTransparentBorderColor(getSeries(8),true);
setTransparentBorderColor(getSeries(9),true);
setTransparentBorderColor(getSeries(10),true);
setTransparentBorderColor(getSeries(11),true);
setTransparentBorderColor(getSeries(12),true);
setTransparentBorderColor(getSeries(13),true);
setTransparentBorderColor(getSeries(14),true);
setTransparentBorderColor(getSeries(15),true);
setTransparentBorderColor(getSeries(16),true);
setTransparentBorderColor(getSeries(17),true);
setTransparentBorderColor(getChartBackground(),true);
setY1MajorGridStyle(0);
setY2MajorGridStyle(0);
setO1MajorGridStyle(0);
setX1MajorGridStyle(0);
setLineStyleLegendMarkers(3);
setNullLegendMarkerShapesAsSquares(false);
setFrameAutoShade(true);
setColorMode(1);
setUseDefaultBubbleMarker(false);
*GRAPH_JS
"mouseOverIndicator": {
"enabled": true,
"color": "yellow",
"marker":{"size": 0}
},
"introAnimation": {
"enabled": true,
"duration": 1400
},
"riserBevel":"bevel"
*END
$
TYPE=REPORT,
COLUMN=N2,
WRAP=6.000000,
$
TYPE=REPORT,
COLUMN=N1,
WRAP=6.000000,
$
ENDSTYLE
END
0 NUMBER OF RECORDS IN TABLE= 0 LINES= 0

 
WHERE ( CWS3001L_TESTING.INPUT.@TRIGGERTYPENAME IN ('@TRIGGERTYPENAME') );

S/B

  
WHERE CWS3001L_TESTING.CWS3001L_TESTING.ANSWERSET1.TRIGGERTYPENAME IN (&TRIGGERTYPENAME);

quote:

-SET &TRIGGERTYPENAME = STRREP (34, 'AcceleratedClaim' OR 'DeedInLieu', 4, ' OR ', 2, ', ', 34, 'A34');
TABLE FILE CWS3001L_TESTING
PRINT CWS3001L_TESTING.ANSWERSET1.INVESTOR
CWS3001L_TESTING.ANSWERSET1.TRIGGERTYPENAME
CWS3001L_TESTING.ANSWERSET1.CLAIMID
WHERE ( CWS3001L_TESTING.INPUT.@TRIGGERTYPENAME IN ('@TRIGGERTYPENAME') );

There are a couple of things that could be a problem here.

The first is that the incoming variable contains single quotes so in the STRREP I would be inclined to use .QUOTEDSTRING.

The second is that you are testing for the existence of @TRIGGERTYPENAME IN ('@TRIGGERTYPENAME'). This is testing for a string value of @TRIGGERTYPENAME as opposed to the variable you have so carefully coded.

I think your code should be -

-SET &TRIGGERTYPENAME = STRREP (&TRIGGERTYPENAME.LENGTH, &TRIGGERTYPENAME.QUOTEDSTRING, 4, ' OR ', 2, ', ', &TRIGGERTYPENAME.LENGTH, 'A&TRIGGERTYPENAME.LENGTH');
TABLE FILE CWS3001L_TESTING
PRINT CWS3001L_TESTING.ANSWERSET1.INVESTOR
      CWS3001L_TESTING.ANSWERSET1.TRIGGERTYPENAME
      CWS3001L_TESTING.ANSWERSET1.CLAIMID
WHERE CWS3001L_TESTING.ANSWERSET1.@TRIGGERTYPENAME IN (&TRIGGERTYPENAME);

T

@ Tom, you just beat me to it!

T

Hi Tony,
Well, you elaborated more than I did!

Thank you very much for your help, i have implemented the suggestions, however i still cannot get this to resolve. I can see in my .fex that the values look as if they arein the correct format.
This is my output as of now

--------------------------------------------------------------------------------

Your request did not return any output to display.
Possible causes:
- No data rows matched the specified selection criteria.
- Output was directed to a destination such as a file or printer.
- An error occurred during the parsing or running of the request.


--------------------------------------------------------------------------------


CURRENTLY DEFINED & VARIABLES:
&ACCEPTS = 0
&APPPRIV = /binfs00/app/ibi/apps
&APPROOT = /binfs00/app/ibi/apps
&AROUTPUT = SET AROUTPUT=EXTJS
&AUTOINDEX = NO
&BASEIO = 0
&CHNGD = 0
&DATE = 04/01/14
&DBMSERR = 0
&DELTD = 0
&DMY = 010414
&DMYY = 01042014
&DUPLS = 0
&ECHO = OFF
&EXCELSERVURL = SET EXCELSERVURL=http://vmbiapps06.dcsr.site:9080/ibi_apps
&FOCEXURL = /ibi_apps/WFServlet.ibfs?IBIF_webapp=/ibi_apps&IBIC_server=BI
&FOCFEXNAME = ADHOCRQ
&FOCFOCEXEC = _ADHOCRQ
&FOCGRAPHCNT = 0
&FOCHTMLURL = /ibi_apps/ibi_html/S56_13911305501F
&FOCINCLUDE = ADHOCRQ
&FOCNEXTPAGE = 0
&FOCREL = M728007D
&FORMAT = 0
&GOOGLEMAPSAP>=
&INDEXIO = 0
&INPUT = 0
&INVALID = 0
&LINES = 0
&MDY = 040114
&MDYY = 04012014
&NOMATCH = 0
&READS = 0
&RECORDS = 0
&REJECTS = 0
&SETFILE =
&SORTIO = 0
&TOD = 09.27.43
&TRANS = 0
&TRIGGERTYPEN>= 'AcceleratedClaim' OR 'DeedInLieu' OR 'ForeclosureSale' OR 'F
&WFDESCRIBE = XMLPROMPT
&WF_TITLE = AdhocRequest
&YMD = 140401
&YYMD = 20140401
SET MSG=ON
-*** Set up SQL tracing
-*** Deactivate SQL tracing
SET TRACEOFF = ALL
-*** Show Commands and data exchange between the physical and
-*** the logical layers of the data adapter
-*SET TRACEON = SQLCALL
-*** Enable Trace for the SQL Translator
SET TRACEON = SQLTRANS
-*** Show SQL statements
SET TRACEON = STMTRACE//CLIENT
-*** Show Optimization information
SET TRACEON = SQLAGGR//CLIENT
-*** Show SQL generated statement trace
-*SET TRACEON = STMTRACE/1/CLIENT
-*** Show SQL generated sub-statement trace
-*SET TRACEON = STMTRACE/2/CLIENT
-*** Disable the trace stamp (Date/Time etc)
SET TRACESTAMP = OFF
-*** Set trace line wrapping - # of characters
SET TRACEWRAP = 132
-*** Set trace to output file
SET TRACEUSER=C:\ibi\apps\baseapp\my_sql_trace.txt
-*** Activate SQL tracing
SET TRACEUSER = ON
-RUN
-SET &TRIGGERTYPENAME = STRREP (121, '''AcceleratedClaim'' OR ''DeedInLieu'' OR ''ForeclosureSale'' OR ''ForeclosureSaleConfirmation'' OR ''ForeclosureSaleRatification''', 4, ' OR ', 2, ', ', 121, 'A121');
TABLE FILE 3001_TESTING
PRINT 3001_TESTING.ANSWERSET1.INVESTOR
3001_TESTING.ANSWERSET1.TRIGGERTYPENAME
3001_TESTING.ANSWERSET1.CLAIMID
WHERE 3001_TESTING.INPUT.@TRIGGERTYPENAME IN ('AcceleratedClaim', 'DeedInLieu', 'ForeclosureSale', 'ForeclosureSaleConfirmation', 'ForeclosureSaleRatification' );
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
-INCLUDE _EDAHOME/ETC/endeflt.sty
type=report,
color=rgb(66 70 73),
font='Trebuchet MS',
size=9,
squeeze=on,
grid=off,
$
type=pagenum,
style=italic,
size=6,
justify=right,
$
type=tabheading,
style=bold+italic,
size=14,
justify=center,
$
type=tabfooting,
style=bold+italic,
size=12,
justify=center,
border-top=heavy,
border-top-color=rgb(52 55 58),
$
type=heading,
style=bold,
size=12,
justify=center,
$
type=footing,
style=bold,
size=10,
justify=center,
$
type=title,
backcolor=rgb(102 102 102),
color=rgb(255 255 255),
style=-underline+bold,
$
type=data,
backcolor=(rgb(255 255 255) rgb(235 235 240)),
$
type=acrosstitle,
border-top=light,
border-bottom=heavy,
border-top-color=rgb(52 55 58),
border-bottom-color=rgb(52 55 58),
size=9,
style=-underline+bold,
$
type=acrossvalue,
backcolor=rgb(218 225 232),
style=bold,
$
type=subhead,
size=10,
style=bold,border-top=heavy,
border-bottom=light,
border-top-color=rgb(52 55 58),
border-bottom-color=rgb(52 55 58),
$
type=subfoot,
size=10,
style=bold,
border-top=light,
border-bottom=medium,
border-top-color=rgb(52 55 58),
border-bottom-color=rgb(52 55 58),
$
type=subtotal,
backcolor=RGB(200 200 200),
style=bold,
border-top=light,
border-top-color=rgb(66 70 73),
$
type=grandtotal,
backcolor=rgb(66 70 73),
color=rgb(255 255 255),
style=bold,
border-top=light,
border-bottom=heavy,
border-top-color=rgb(66 70 73),
border-top-style=solid,
border-bottom-style=double,
$
TYPE=REPORT,
OBJECT=STATUS-AREA,
COLOR=white,
BACKCOLOR=rgb(102 102 102),
$
TYPE=REPORT,
OBJECT=CURRENT-ROW,
BACKCOLOR=RGB(200 200 200),
HOVER-BACKCOLOR=rgb(218 225 232),
$
TYPE=REPORT,
GRAPHCOLOR=green,
GRAPHCOLORNEG=red,
$
TYPE=REPORT,
OBJECT=CALC-AREA,
COLOR=white,
BACKCOLOR=rgb(102 102 102),
$
TYPE=REPORT,
OBJECT=MENU,
COLOR=white,
BACKCOLOR=rgb(102 102 102),
BORDER-COLOR=white,
HOVER-COLOR=rgb(66 70 73),
hover-backcolor=rgb(218 225 232),
$
*GRAPH_SCRIPT
setFillColor(getSeries(0),new Color(48,94,213));
setFillColor(getSeries(1),new Color(237,195,27));
setFillColor(getSeries(2),new Color(136,144,151));
setFillColor(getSeries(3),new Color(140,205,40));
setFillColor(getSeries(4),new Color(227,136,0) );
setFillColor(getSeries(5),new Color(40,190,205) );
setFillColor(getSeries(6),new Color(255,102,102) );
setFillColor(getSeries(7), new Color(71,186,143));
setFillColor(getSeries(8), new Color(187,90,71));
setFillColor(getSeries(9),new Color(190,107,211));
setFillColor(getSeries(10),new Color(16,122,22));
setFillColor(getSeries(11),new Color(108,149,200));
setFillColor(getSeries(12),new Color(169,109,205) );
setFillColor(getSeries(13),new Color(210,143,255) );
setFillColor(getSeries(14),new Color(60,49,36));
setFillColor(getSeries(15),new Color(78,43,37));
setFillColor(getSeries(16),new Color(95,38,37));
setFillColor(getSeries(17),new Color(19,23,162));
setFillColor(getSeries(18),new Color(66,70,73));
setFillColor(getSeries(19),new Color(19,23,162));
setFillColor(getSeries(20),new Color(66,70,73));
setFillColor(getSeries(21),new Color(191,218,170));
setFillColor(getSeries(22),new Color(0,0,128));
setFillColor(getSeries(23),new Color(163,211,208));
setFillColor(getSeries(24),new Color(158,213,243));
setFillColor(getSeries(25),new Color(179,151,191));
setFillColor(getSeries(26),new Color(200,158,193));
setFillColor(getSeries(27),new Color(240,171,196));
setFillColor(getSeries(28),new Color(242,171,168));
setFillColor(getSeries(29),new Color(230,121,88));
setFillColor(getSeries(30),new Color(240,159,97));
setFillColor(getSeries(31),new Color(248,191,106));
setFillColor(getSeries(32),new Color(255,239,121));
setFillColor(getSeries(33),new Color(195,218,125));
setFillColor(getSeries(34),new Color(0,0,255));
setFillColor(getSeries(35),new Color(0,255,0));
setFillColor(getSeries(36),new Color(255,0,255));
setFillColor(getSeries(37),new Color(255,255,0));
setFillColor(getSeries(38),new Color(255,0,0));
setFillColor(getSeries(39),new Color(255,200,0));
setFillColor(getSeries(40),new Color(255,175,175));
setFillColor(getSeries(41),new Color(0,255,255));
setFillColor(getSeries(42),new Color(206,0,54));
setFillColor(getSeries(43),new Color(255,239,0));
setFillColor(getSeries(44),new Color(0,152,82));
setFillColor(getSeries(45),new Color(0,174,227));
setFillColor(getSeries(46),new Color(42,19,109));
setFillColor(getSeries(47),new Color(206,0,121));
setFillColor(getSeries(48),new Color(243,169,136));
setFillColor(getSeries(49),new Color(248,190,145));
setFillColor(getSeries(50),new Color(253,211,154));
setFillColor(getSeries(51),new Color(255,243,172));
setFillColor(getSeries(52),new Color(217,229,171));
setFillColor(getSeries(53),new Color(191,218,170));
setFillColor(getSeries(54),new Color(165,208,169));
setFillColor(getSeries(55),new Color(163,211,208));
setFillColor(getSeries(56),new Color(158,213,243));
setFillColor(getSeries(57),new Color(179,151,191));
setFillColor(getSeries(58),new Color(200,158,193));
setFillColor(getSeries(59),new Color(240,171,196));
setFillColor(getSeries(60),new Color(242,171,168));
setFillColor(getSeries(61),new Color(230,121,88));
setFillColor(getSeries(62),new Color(240,159,97));
setFillColor(getSeries(63),new Color(248,191,106));
setBorderColor(getO1AxisLine(),new Color(130,130,130));
setBorderColor(getY1AxisLine(),new Color(130,130,130));
setFillColor(getY1Title(),new Color(66,70,73));
setFillColor(getY1Label(),new Color(66,70,73));
setFillColor(getY2Label(),new Color(66,70,73));
setFillColor(getY3Label(),new Color(66,70,73));
setFillColor(getY4Label(),new Color(66,70,73));
setFillColor(getY5Label(),new Color(66,70,73));
setFillColor(getO1Title(),new Color(66,70,73));
setFillColor(getX1Title(),new Color(66,70,73));
setFillColor(getO1Label(),new Color(66,70,73));
setFillColor(getO2Label(),new Color(66,70,73));
setFillColor(getX1Label(),new Color(66,70,73));
setFillColor(getLegendText(),new Color(66,70,73));
setFillColor(getFrame(),new Color(235,235,240));
setFillColor(getChartBackground(),new Color(255,255,255));
setBorderColor(getY1MajorGrid(),new Color(204,204,204));
setBorderColor(getY1MinorGrid(),new Color(204,204,204));
setBorderColor(getY2MajorGrid(),new Color(204,204,204));
setBorderColor(getY2MinorGrid(),new Color(204,204,204));
setBorderColor(getO1MajorGrid(),new Color(204,204,204));
setBorderColor(getO1MinorGrid(),new Color(204,204,204));
setBorderColor(getX1MajorGrid(),new Color(204,204,204));
setBorderColor(getX1MinorGrid(),new Color(204,204,204));
setTransparentBorderColor(getCubeFloor(),true);
setTransparentBorderColor(getCubeLeftWall(),true);
setTransparentBorderColor(getCubeRightWall(),true);
setCubeFocusFactor(95.83333182119424);
setCubePanX(41.3);
setCubePanY(54.7);
setCubeRotationMatrix(0.9527877123115605,-0.39648905742363183,1.0691265903653806,-0.012240167246073914,1.389589570659449,0.5262420232508634,-1.1402128823990627,-0.3462324595884829,0.8877371966710723);
setCubeViewerZ(93.61645050038162);
setCubeZoomFactor(55.0);
setCubeWallThickZ(0.0);
setCubeWallThickY(0.0);
setCubeWallThickX(0.0);
setTransparentBorderColor(getSeries(0),true);
setTransparentBorderColor(getSeries(1),true);
setTransparentBorderColor(getSeries(2),true);
setTransparentBorderColor(getSeries(3),true);
setTransparentBorderColor(getSeries(4),true);
setTransparentBorderColor(getSeries(5),true);
setTransparentBorderColor(getSeries(6),true);
setTransparentBorderColor(getSeries(7),true);
setTransparentBorderColor(getSeries(8),true);
setTransparentBorderColor(getSeries(9),true);
setTransparentBorderColor(getSeries(10),true);
setTransparentBorderColor(getSeries(11),true);
setTransparentBorderColor(getSeries(12),true);
setTransparentBorderColor(getSeries(13),true);
setTransparentBorderColor(getSeries(14),true);
setTransparentBorderColor(getSeries(15),true);
setTransparentBorderColor(getSeries(16),true);
setTransparentBorderColor(getSeries(17),true);
setTransparentBorderColor(getChartBackground(),true);
setY1MajorGridStyle(0);
setY2MajorGridStyle(0);
setO1MajorGridStyle(0);
setX1MajorGridStyle(0);
setLineStyleLegendMarkers(3);
setNullLegendMarkerShapesAsSquares(false);
setFrameAutoShade(true);
setColorMode(1);
setUseDefaultBubbleMarker(false);
*GRAPH_JS
"mouseOverIndicator": {
"enabled": true,
"color": "yellow",
"marker":{"size": 0}
},
"introAnimation": {
"enabled": true,
"duration": 1400
},
"riserBevel":"bevel"
*END
$
TYPE=REPORT,
COLUMN=N5,
WRAP=6.000000,
$
TYPE=REPORT,
COLUMN=N4,
WRAP=6.000000,
$
TYPE=REPORT,
COLUMN=N1,
WRAP=6.000000,
$
ENDSTYLE
END
(FOC1631) MORE THAN ONE IF/WHERE TEST FOR FIELD @TRIGGERTYPENAME