Multi value parameter Cascading parameters

JavaNep

Hi I am trying to pass multi value parameters using cascaded parameter that is p_service and p_subtype. My goal is to get related resultset from select statement using IN clause .<br />
My SQl is <br />
select distinct DMW_WORK_ORDER.MGE_CD_FO_SUB_TYP, DMW_WORK_ORDER.MGE_CD_FO_TYP<br />
from DMW_WORK_ORDER <br />
<br />
and I have wrote this in beforeOpen script:<br />
<br />
var parmcount = params["p_service"].value.length<br />
var whereclause = "";<br />
if ( parmcount > 0 ){<br />
whereclause = " where DMW_WORK_ORDER.MGE_CD_FO_TYP in ('"<br />
}<br />
for ( i=0; i < parmcount; i++ ){<br />
if( i == 0 ){<br />
whereclause = whereclause + params["p_service"].value ;<br />
} else {<br />
whereclause = whereclause + "' , '" + params["p_service"].value; <br />
}<br />
}<br />
if ( parmcount > 0 ){<br />
this.queryText = this.queryText + whereclause + "') ";<br />
}<br />

---

<br />
If I use this:<br />
this.queryText = this.queryText + " where DMW_WORK_ORDER.MGE_CD_FO_TYP IN ('" + params["p_service"].value + "')";<br />
I can select one value and get resultset in other cascaded parameter that is p_subtype<br />
<br />
but since i need all the value related with selected service which can be more than one ,( when i select more than one service parameter), I get empty . I feel something wrong with the script but i cant figure out why it is doing this .<br />
Help will be much apreciated.<br />
thanks

linucksrox

I've found a more concise way of doing what you want to do using the beforeOpen method of the dataset. Instead of manually iterating through the parameter values like you're doing, you can use some built in javascript methods to do the work for you. For example,<br />
<pre class='_prettyXprint _lang-auto _linenums:0'>this.queryText = this.queryText.replace("yyy", params["RP_marketing"].value.join("','"));</pre>
replaces the string yyy in my SQL query with a comma separated list of values returned by the RP_marketing report parameter. I don't need any additional logic to pull out value[0] if there's only one value selected, etc. because the join method takes care of that automatically.<br />
My SQL query looks like this:<br />
<pre class='_prettyXprint _lang-auto _linenums:0'>marCOMMENT WHERE contact_marketing.marketing_type IN ('yyy')</pre>
marCOMMENT is a flag that I change in my script to either "" (empty string) or "-- " (SQL comment) which determines whether the WHERE condition is used. Then it's just a matter of inserting the previous comma separated list of values into yyy.<br />
I've found that this makes the script and SQL query a lot more flexible, and it's not too difficult to maintain once you decide on a naming convention that you're comfortable with.<br />
And please remember to use code tags so that it's easier to read your code!