A BIRT exception occurred. See next exception for more information. TypeError: Cannot find function

<p>Hi folks,</p><p> </p><p>Looking for some advice on a first BIRT report. Running against an Oracle DB, and the basic SQL is fine. The issue I'm having is with a cascading multi-select parameter. For the record, I come from an Oracle Reports background, so that probably colours my way of thinking.</p><p> </p><p>My main query is</p><div><pre class="_prettyXprint">select datacenter , db_name , asm_size , database_size , apptypefrom ( select dsv.DATACENTER , d.DB_ALIAS , d.ENVIRONMENT || ' ' || d.ENVTYPE || ' ' || d.application AS DB_name , dsv.ASM_SIZE , dsv.DATABASE_SIZE , d.APPTYPE from db_size_view dsv , databases d where dsv.DB_ALIAS = d.DB_ALIAS and dsv.DATACENTER = d.DATACENTER and d.ACTIVE_YN <> 'N' )where 1 = 1/**where_DC**//**where_AT**//**where_DB**/</pre></div><div> </div><div>the idea being that each of the additional where clauses can be defined by the parameter form. For those, I have the below in the beforeOpen for this query:</div><div><div><pre class="_prettyXprint">if( params["RP_Datacenter"].value.toString() != ' ALL' ) { this.queryText=this.queryText.replace( "/**where_DC**/", " AND datacenter in ( '" +params["RP_Datacenter"].value.join("','") + "' )" ); } if( params["RP_AppType"].value.toString() != ' ALL' ) { this.queryText=this.queryText.replace( "/**where_AT**/", " AND apptype in ( '" +params["RP_AppType"].value.toString().join("','") + "' )" );}if( params["RP_DB_NAME"].value.toString() != ' ALL' ) { this.queryText=this.queryText.replace( "/**where_DB**/", " AND db_name in ( '" +params["RP_DB_Name"].value.toString().join("','") + "' )" );}</pre><p>The error I'm running into is</p><div><pre class="_prettyXprint"> BIRT exception occurred. See next exception for more information.TypeError: Cannot find function join in object ASHBURN. (/report/data-sets/oda-data-set[@id="8"]/method[@name="beforeOpen"]#2). </pre><p>ASHBURN is the value I'm trying to pass as RP_Datacenter (if I select ' ALL' there and pick a specific apptype, the error repeats except one layer further down).</p><p> </p><p>To me, it appears that the javascript is taking the selected value ("ASHBURN") and treating it as a function name rather than as the input to the function.</p><p> </p><p>Any advice/thoughts?</p><p> </p><p>Thanks!</p><p>stephan</p></div></div><div> </div></div><p> </p>

Find more posts tagged with

Comments

pricher

<p>Hi,</p><p> </p><p>To use the join function, you need to create your parameter as a multi-select list, not a simple text box.</p><p> </p><p>I have attached a sample loosely based on your example. It filters the query on the COUNTRY field using values from the pCountry list box parameter.</p><p> </p><p>Hope this helps,</p><p> </p><p>P.</p>

yoovrajkhullar

<p>Hi P</p>
<p> </p>
<p>I want to use the join function but on a Dynamic Filter as i need the options (No condition, Equal to, IN, Not IN , Not Equal to in my paramter drop down)</p>
<p>Also i want to use my Before Open Query Text modification on this dateset.</p>
<p> </p>
<p>But it gives me error of Cannot find function on Join</p>
<p> </p>
<p>I am using this Query modification</p>
<p> </p>
<div> if (params["RP_TIER_CARRIER_SCAC"].value !== "" )</div>
<div>{</div>
<div>this.queryText= this.queryText.replace("SCACPARAMETER", params["RP_TIER_CARRIER_SCAC"].value.join("','") ); // not working gives error cant find join</div>
<div>//this.queryText= this.queryText;//working</div>
<div>}</div>
<div> </div>
<div>//else</div>
<div> if (params["RP_TIER_CARRIER_SCAC"].value == "")</div>
<div>{</div>
<div>this.queryText= this.queryText.replace("IN ('SCACPARAMETER')"," = ord.or_org_name ");</div>
<div>}</div>
<div> </div>
<div>Can i use Join or what is the alternative of join to pass in multiple values in this Dynamic filter</div>
<div> </div>
<div> </div>
<div>This is My dataset where clause which i am modifying in Before Open method</div>
<div> </div>
<div>
<div>FROM </div>
<div> LW_ORDERS ORD</div>
<div> JOIN PARAMS ON (1=1)</div>
<div> WHERE 1=1</div>
<div> AND ORD.OR_CREATE_DATE BETWEEN PARAMS.D1 AND PARAMS.D2 </div>
<div> </div>
<div> AND or_org_name IN ('SCACPARAMETER')</div>
<div> </div>
</div>
<div>Yoovraj</div>
<div>yoovraj@gmail.com</div>

pricher

<p>Hi,</p>
<p> </p>
<p>If you are using a Dynamic Filter, you do not need to modify the query through script : this will be done automatically for you. The reason you get the error message is because what's returned by <span style="color:rgb(40,40,40);font-family:'Source Sans Pro', sans-serif;">params["RP_TIER_CARRIER_SCAC"].value is not an array, but a string; therefore, the join method is not available.</span></p>
<p> </p>
<p><span>P.</span></p>

yoovrajkhullar

<blockquote class="ipsBlockquote" data-author="pricher" data-cid="144332" data-time="1466193772">
<div>
<p>Hi,</p>
<p> </p>
<p>If you are using a Dynamic Filter, you do not need to modify the query through script : this will be done automatically for you. The reason you get the error message is because what's returned by <span style="color:rgb(40,40,40);font-family:'Source Sans Pro', sans-serif;">params["RP_TIER_CARRIER_SCAC"].value is not an array, but a string; therefore, the join method is not available.</span></p>
<p> </p>
<p>P.</p>
</div>
</blockquote>
<p>Hi P,</p>
<p>Thanks for your reply, but i want to have the Dynamic filter done inside my Query not at the END of the QUERY.(QUERY Below)</p>
<p>(Because its grouping and order by rank etc etc.)</p>
<p>And also i want to use the Dynamic Filter parameter which has the option for user to select Equal,Not Equal , In , not In and also No condition, which are provided by Dynamic filter parameter.</p>
<p>I hope i explained what i want.</p>
<p>So i want Dynamic Filter parameter and also use some scripting in before open method i stated in previous post so that it can do what i want.</p>
<p> </p>
<div>FROM </div>
<div> LW_ORDERS ORD</div>
<div> JOIN PARAMS ON (1=1)</div>
<div> WHERE 1=1</div>
<div> AND ORD.OR_CREATE_DATE BETWEEN PARAMS.D1 AND PARAMS.D2 </div>
<div> AND or_org_name IN ('ORGPARAMETER')</div>
<div> AND ORD.ORG_ID NOT IN (SELECT ORG_ID FROM EXCLUDED_ORG_SET WHERE SET_NAME = 'ODYSSEY_JBIT')</div>
<div> )</div>
<div> GROUP BY ORG_ID, OR_ORG_NAME </div>
<div> )</div>
<div> WHERE NVL(V2_COUNT, 0) > NVL(V1_COUNT, 0)</div>
<div>) M </div>
<div>JOIN PARAMS ON (1=1)</div>
<div>WHERE 1=1</div>
<div> AND M.RANK_ORDER <= PARAMS.TOP_N</div>
<div>ORDER BY RANK_ORDER </div>

pricher

<p>Hi,</p>
<p> </p>
<p>You can modify a query through script from a dynamic parameter. However, you need to know that the value returned by a dynamic parameter will change based on the condition selected. For example, for a dynamic parameter created on field COUNTRY with an IN condition, the value returned when Australia and Austria are selected is:</p>
<p> </p>
<p><span style="font-family:'courier new', courier, monospace;">IN([COUNTRY],"Australia","Austria")</span></p>
<p> </p>
<p>and for an Equal To condition it would be:</p>
<p> </p>
<p><span style="font-family:'courier new', courier, monospace;">([COUNTRY] = "Australia")</span></p>
<p> </p>
<p>It means that if you want to modify the query in the beforeOpen method, you will need to parse the value returned from the dynamic parameter and prepare the where clause accordingly.</p>
<p> </p>
<p>The attached report has an example of a query where the where clause is changed via scripting. The script in the beforeOpen looks like this: (Please note that I have coded only for 2 conditions: IN and Equal To)</p>
<pre class="_prettyXprint _lang-">
var myQuery = this.queryText
var myParam = reportContext.getParameterValue("pCountry");
var whereClause = "";

// Test of IN and Equal to conditions. Other conditions will need to be added
if (myParam.substr(0,2) == "IN") {
var pos = myParam.indexOf("]") + 2;
var len = myParam.length();
var end = len - pos - 1;
whereClause = " country IN (" + myParam.substr(pos, end) + ")";
whereClause = whereClause.replace(/"/g, "'");
}
else if(myParam.indexOf("=") > 0) {
var pos = myParam.indexOf("=") + 2;
var len = myParam.length();
var end = len - pos - 1;
whereClause = " country = " + myParam.substr(pos, end);
whereClause = whereClause.replace(/"/g, "'");

}

// If a where clause was created, replace the dummy where clause.
if (whereClause != "") {
myQuery = myQuery.replace("'dummy' = 'dummy'", whereClause)
this.queryText = myQuery
Packages.java.lang.System.out.println("query=" + this.queryText);
}
</pre>
<p>Hope this helps,</p>
<p> </p>
<p>P.</p>

yoovrajkhullar

<blockquote class="ipsBlockquote" data-author="pricher" data-cid="144345" data-time="1466454636">
<div>
<p>Hi,</p>
<p> </p>
<p>You can modify a query through script from a dynamic parameter. However, you need to know that the value returned by a dynamic parameter will change based on the condition selected. For example, for a dynamic parameter created on field COUNTRY with an IN condition, the value returned when Australia and Austria are selected is:</p>
<p> </p>
<p><span style="font-family:'courier new', courier, monospace;">IN([COUNTRY],"Australia","Austria")</span></p>
<p> </p>
<p>and for an Equal To condition it would be:</p>
<p> </p>
<p><span style="font-family:'courier new', courier, monospace;">([COUNTRY] = "Australia")</span></p>
<p> </p>
<p>It means that if you want to modify the query in the beforeOpen method, you will need to parse the value returned from the dynamic parameter and prepare the where clause accordingly.</p>
<p> </p>
<p>The attached report has an example of a query where the where clause is changed via scripting. The script in the beforeOpen looks like this: (Please note that I have coded only for 2 conditions: IN and Equal To)</p>
<pre class="_prettyXprint _lang-">
var myQuery = this.queryText
var myParam = reportContext.getParameterValue("pCountry");
var whereClause = "";

// Test of IN and Equal to conditions. Other conditions will need to be added
if (myParam.substr(0,2) == "IN") {
var pos = myParam.indexOf("]") + 2;
var len = myParam.length();
var end = len - pos - 1;
whereClause = " country IN (" + myParam.substr(pos, end) + ")";
whereClause = whereClause.replace(/"/g, "'");
}
else if(myParam.indexOf("=") > 0) {
var pos = myParam.indexOf("=") + 2;
var len = myParam.length();
var end = len - pos - 1;
whereClause = " country = " + myParam.substr(pos, end);
whereClause = whereClause.replace(/"/g, "'");

}

// If a where clause was created, replace the dummy where clause.
if (whereClause != "") {
myQuery = myQuery.replace("'dummy' = 'dummy'", whereClause)
this.queryText = myQuery
Packages.java.lang.System.out.println("query=" + this.queryText);
}
</pre>
<p>Hope this helps,</p>
<p> </p>
<p>P</p>
<p>Thanks P</p>
<p>But how do i do it for this Complex Query.</p>
<p>( i want to place the parameter Organization In this AND clause in the Query</p>
<p> -- AND or_org_name IN ('ORGPARAMETER') </p>
<p>like you have pCountry)</p>
<p> </p>
<p>I am thankfull .</p>
<p> </p>
<p> </p>
<div>WITH INPUT_PARAMS AS (</div>
<div> SELECT</div>
<div> </div>
<div> ? TOP_N,</div>
<div> ? D1,</div>
<div> ? D2</div>
<div> </div>
<div>/* </div>
<div> 15 TOP_N,</div>
<div> TO_DATE('8/4/2015', 'MM/DD/YYYY') D1,</div>
<div> TO_DATE('8/5/2015', 'MM/DD/YYYY') D2</div>
<div>*/ </div>
<div> FROM</div>
<div> DUAL</div>
<div>), PARAMS AS (</div>
<div> SELECT</div>
<div> INPUT_PARAMS.TOP_N,</div>
<div> INPUT_PARAMS.D1 D1,</div>
<div> INPUT_PARAMS.D2 D2</div>
<div> FROM</div>
<div> INPUT_PARAMS</div>
<div>)</div>
<div> </div>
<div>SELECT </div>
<div> M.OR_ORG_NAME,</div>
<div> M.V1_COUNT,</div>
<div> M.V2_COUNT,</div>
<div> NVL(M.V2_COUNT, 0) - NVL(M.V1_COUNT, 0) INCREASE,</div>
<div> CASE WHEN NVL(M.V1_COUNT, 0) > 0 AND NVL(M.V2_COUNT, 0) > 0 THEN</div>
<div> (M.V2_COUNT - M.V1_COUNT) / M.V1_COUNT</div>
<div> END INCREASE_PERCENT,</div>
<div> M.ORDERS_PER_CLIENT,</div>
<div> RANK_ORDER</div>
<div>FROM ( </div>
<div> </div>
<div> SELECT </div>
<div> OR_ORG_NAME,</div>
<div> ROW_NUMBER() OVER (ORDER BY (NVL(V2_COUNT, 0) - NVL(V1_COUNT, 0)) DESC, ORDERS_PER_CLIENT DESC) RANK_ORDER,</div>
<div> V1_COUNT,</div>
<div> V2_COUNT,</div>
<div> ORDERS_PER_CLIENT</div>
<div> FROM(</div>
<div> SELECT </div>
<div> ORG_ID,</div>
<div> OR_ORG_NAME,</div>
<div> SUM(V_1) V1_COUNT,</div>
<div> SUM(V_2) V2_COUNT,</div>
<div> SUM(V_TOTAL) ORDERS_PER_CLIENT</div>
<div> FROM ( </div>
<div> SELECT </div>
<div> ORD.ORG_ID,</div>
<div> ORD.OR_ORG_NAME,</div>
<div> CASE WHEN OR_CREATE_DATE = D1 THEN 1 END V_1,</div>
<div> CASE WHEN OR_CREATE_DATE = D2 THEN 1 END V_2,</div>
<div> 1 V_TOTAL</div>
<div> FROM </div>
<div> LW_ORDERS ORD</div>
<div> JOIN PARAMS ON (1=1)</div>
<div> WHERE 1=1</div>
<div> AND ORD.OR_CREATE_DATE BETWEEN PARAMS.D1 AND PARAMS.D2 </div>
<div> </div>
<div> -- AND or_org_name IN ('ORGPARAMETER')</div>
<div> AND ORD.ORG_ID NOT IN (SELECT ORG_ID FROM EXCLUDED_ORG_SET WHERE SET_NAME = 'ODYSSEY_JBIT')</div>
<div> )</div>
<div> GROUP BY ORG_ID, OR_ORG_NAME </div>
<div> )</div>
<div> WHERE NVL(V2_COUNT, 0) > NVL(V1_COUNT, 0)</div>
<div>) M </div>
<div>JOIN PARAMS ON (1=1)</div>
<div>WHERE 1=1</div>
<div> AND M.RANK_ORDER <= PARAMS.TOP_N</div>
<div>ORDER BY RANK_ORDER </div>
<div> </div>
<div> </div>
<div> </div>
<div>THANK YOU P , I GOT IT RIGHT. AND THANKS FOR YOUR HELP. ITS EXCELLENT EXAMPLE<br><br>
P can you please give the If statements for NOT IN and Not Equal too.</div>
<div>thanks</div>
</div>
</blockquote>

pricher

<p>Hi,</p>
<p> </p>
<p>I am a bit puzzled. On the one hand, you say you want to use a Dynamic Filter to benefit from the choice of condition (IN, Not IN, Equal to, etc); on the other hand, the where clause in your query is strictly an IN statement.</p>
<p> </p>
<p>If you keep using a Dynamic Filter, what I posted yesterday gives you essential information about the value returned by such a filter. And the sample code was built around the idea that your query could be modified to work with different conditions. You can modify the code to replace only the ORGPARAMETER substring this way (<span style="color:#ff0000;">Please note: Untested code</span>)</p>
<pre class="_prettyXprint _lang-">
var myQuery = this.queryText
var myParam = reportContext.getParameterValue("pCountry");
var whereClause = "";

// Test of IN and Equal to conditions. Other conditions will need to be added
if (myParam.substr(0,2) == "IN") {
var pos = myParam.indexOf("]") + 2;
var len = myParam.length();
var end = len - pos - 1;
whereClause = myParam.substr(pos, end);
whereClause = whereClause.replace(/"/g, "'");
}
else if(myParam.indexOf("=") > 0) {
var pos = myParam.indexOf("=") + 2;
var len = myParam.length();
var end = len - pos - 1;
whereClause = myParam.substr(pos, end);
whereClause = whereClause.replace(/"/g, "'");

}

// If a where clause was created, replace the dummy where clause.
if (whereClause != "") {
myQuery = myQuery.replace("'ORGPARAMETER'", whereClause)
this.queryText = myQuery
Packages.java.lang.System.out.println("query=" + this.queryText);
}
</pre>
<p>P.</p>
<p> </p>

yoovrajkhullar

<blockquote class="ipsBlockquote" data-author="pricher" data-cid="144364" data-time="1466520065">
<div>
<p>Hi,</p>
<p> </p>
<p>I am a bit puzzled. On the one hand, you say you want to use a Dynamic Filter to benefit from the choice of condition (IN, Not IN, Equal to, etc); on the other hand, the where clause in your query is strictly an IN statement.</p>
<p> </p>
<p>If you keep using a Dynamic Filter, what I posted yesterday gives you essential information about the value returned by such a filter. And the sample code was built around the idea that your query could be modified to work with different conditions. You can modify the code to replace only the ORGPARAMETER substring this way (<span style="color:#ff0000;">Please note: Untested code</span>)</p>
<pre class="_prettyXprint _lang-">
var myQuery = this.queryText
var myParam = reportContext.getParameterValue("pCountry");
var whereClause = "";

// Test of IN and Equal to conditions. Other conditions will need to be added
if (myParam.substr(0,2) == "IN") {
var pos = myParam.indexOf("]") + 2;
var len = myParam.length();
var end = len - pos - 1;
whereClause = myParam.substr(pos, end);
whereClause = whereClause.replace(/"/g, "'");
}
else if(myParam.indexOf("=") > 0) {
var pos = myParam.indexOf("=") + 2;
var len = myParam.length();
var end = len - pos - 1;
whereClause = myParam.substr(pos, end);
whereClause = whereClause.replace(/"/g, "'");

}

// If a where clause was created, replace the dummy where clause.
if (whereClause != "") {
myQuery = myQuery.replace("'ORGPARAMETER'", whereClause)
this.queryText = myQuery
Packages.java.lang.System.out.println("query=" + this.queryText);
}
</pre>
<p>P.</p>
</div>
</blockquote>
<p>P its working i just need the code for NOT IN and NOT Equal to . Please give it and i will be done with this Report as what i have written is not working for NOT IN and !=</p>
<p> </p>
<p>if (myParam.substr(0,6) == "NOT IN") {</p>
<div>var pos = myParam.indexOf("]") + 2;</div>
<div>var len = myParam.length();</div>
<div>var end = len - pos - 1;</div>
<div>whereClause = " OR_ORG_NAME NOT IN (" + myParam.substr(pos, end) + ")";</div>
<div>whereClause = whereClause.replace(/"/g, "'");</div>
<div>}</div>
<div> </div>
<div>if(myParam.indexOf("!=") > 0) {</div>
<div>var pos = myParam.indexOf("!=") + 2;</div>
<div>var len = myParam.length();</div>
<div>var end = len - pos - 1;</div>
<div>whereClause = " OR_ORG_NAME != " + myParam.substr(pos, end);</div>
<div>whereClause = whereClause.replace(/"/g, "'");</div>
<div> </div>
<div>}</div>

pricher

<p>Hi,</p>
<p> </p>
<p>Here's the complete code for operators IN, Not IN, Equal To and Not Equal To. Again, the original query has a placeholder where clause "AND 'dummy' = 'dummy'" that is replaced in the script with the appropriate operator:</p>
<pre class="_prettyXprint _lang-">
var myQuery = this.queryText
var myParam = reportContext.getParameterValue("pCountry");
var whereClause = "";

// Test of IN, Equal to , Not IN and Not Equal to conditions. Other conditions will need to be added
if (myParam.substr(0,2) == "IN") {
var pos = myParam.indexOf("]") + 2;
var len = myParam.length();
var end = len - pos - 1;
whereClause = " country IN (" + myParam.substr(pos, end) + ")";
whereClause = whereClause.replace(/"/g, "'");
}
else if(myParam.indexOf("=") > 0) {
var pos = myParam.indexOf("=") + 2;
var len = myParam.length();
var end = len - pos - 1;
whereClause = " country = " + myParam.substr(pos, end);
whereClause = whereClause.replace(/"/g, "'");

}
else if(myParam.indexOf("<>") > 0) {
var pos = myParam.indexOf("<>") + 3;
var len = myParam.length();
var end = len - pos - 1;
whereClause = " country != " + myParam.substr(pos, end);
whereClause = whereClause.replace(/"/g, "'");

}
else if(myParam.substr(0,6) == "NOT(IN") {
var pos = myParam.indexOf("]") + 2;
var len = myParam.length();
var end = len - pos - 2;
whereClause = " country NOT IN (" + myParam.substr(pos, end) + ")";
whereClause = whereClause.replace(/"/g, "'");
}

// If a where clause was created, replace the dummy where clause.
if (whereClause != "") {
myQuery = myQuery.replace("'dummy' = 'dummy'", whereClause)
this.queryText = myQuery
}
</pre>
<p>Hope this helps,</p>
<p> </p>
<p>P.</p>

yoovrajkhullar

<blockquote class="ipsBlockquote" data-author="pricher" data-cid="144380" data-time="1466534593">
<div>
<p>Hi,</p>
<p> </p>
<p>Here's the complete code for operators IN, Not IN, Equal To and Not Equal To. Again, the original query has a placeholder where clause "AND 'dummy' = 'dummy'" that is replaced in the script with the appropriate operator:</p>
<pre class="_prettyXprint _lang-">
var myQuery = this.queryText
var myParam = reportContext.getParameterValue("pCountry");
var whereClause = "";

// Test of IN, Equal to , Not IN and Not Equal to conditions. Other conditions will need to be added
if (myParam.substr(0,2) == "IN") {
var pos = myParam.indexOf("]") + 2;
var len = myParam.length();
var end = len - pos - 1;
whereClause = " country IN (" + myParam.substr(pos, end) + ")";
whereClause = whereClause.replace(/"/g, "'");
}
else if(myParam.indexOf("=") > 0) {
var pos = myParam.indexOf("=") + 2;
var len = myParam.length();
var end = len - pos - 1;
whereClause = " country = " + myParam.substr(pos, end);
whereClause = whereClause.replace(/"/g, "'");

}
else if(myParam.indexOf("<>") > 0) {
var pos = myParam.indexOf("<>") + 3;
var len = myParam.length();
var end = len - pos - 1;
whereClause = " country != " + myParam.substr(pos, end);
whereClause = whereClause.replace(/"/g, "'");

}
else if(myParam.substr(0,6) == "NOT(IN") {
var pos = myParam.indexOf("]") + 2;
var len = myParam.length();
var end = len - pos - 2;
whereClause = " country NOT IN (" + myParam.substr(pos, end) + ")";
whereClause = whereClause.replace(/"/g, "'");
}

// If a where clause was created, replace the dummy where clause.
if (whereClause != "") {
myQuery = myQuery.replace("'dummy' = 'dummy'", whereClause)
this.queryText = myQuery
}
</pre>
<p>Hope this helps,</p>
<p> </p>
<p>P.</p>
</div>
</blockquote>
<p>Thanks P</p>
<p> </p>
<p>One last thing i have many datasets where this thing needs to be implementd.</p>
<p>Can i externalize this javascript in Before Open method and use it for all Datasets after putting this is resource file ?</p>

pricher

<p>Yes. You can create a function in a JS file and call it from the beforeOpen.</p>
<p> </p>
<p>P.</p>

yoovrajkhullar

<blockquote class="ipsBlockquote" data-author="pricher" data-cid="144382" data-time="1466534912">
<div>
<p>Yes. You can create a function in a JS file and call it from the beforeOpen.</p>
<p> </p>
<p>P.</p>
</div>
</blockquote>
<p>thanks P for the idea, </p>
<p> </p>
<p>Got it right made the function and called in one dataset, will call in other too.</p>
<p>Thanks so much .</p>
<p>You are a friend.</p>
<p>Take care.</p>
<p>Yoovraj</p>

pricher

<p>Here's an example:</p>
<p> </p>
<p>The beforeOpen calls a function called prepareWhereClause with input parameter myParam:</p>
<pre class="_prettyXprint _lang-">
var myQuery = this.queryText
var myParam = reportContext.getParameterValue("pCountry");
var whereClause = "";

whereClause = prepareWhereClause(myParam);

// If a where clause was created, replace the dummy where clause.
if (whereClause != "") {
myQuery = myQuery.replace("'dummy' = 'dummy'", whereClause)
this.queryText = myQuery
Packages.java.lang.System.out.println("query=" + this.queryText);
}
</pre>
<p>The prepareWhereClause is in a JS file. The function returns the where clause:</p>
<pre class="_prettyXprint _lang-">
function prepareWhereClause (myParam) {
whereClause = "";
// Test of IN and Equal to conditions. Other conditions will need to be added
if (myParam.substr(0,2) == "IN") {
var pos = myParam.indexOf("]") + 2;
var len = myParam.length();
var end = len - pos - 1;
whereClause = " country IN (" + myParam.substr(pos, end) + ")";
whereClause = whereClause.replace(/"/g, "'");
}
else if(myParam.indexOf("=") > 0) {
var pos = myParam.indexOf("=") + 2;
var len = myParam.length();
var end = len - pos - 1;
whereClause = " country = " + myParam.substr(pos, end);
whereClause = whereClause.replace(/"/g, "'");

}
else if(myParam.indexOf("<>") > 0) {
var pos = myParam.indexOf("<>") + 3;
var len = myParam.length();
var end = len - pos - 1;
whereClause = " country != " + myParam.substr(pos, end);
whereClause = whereClause.replace(/"/g, "'");

}
else if(myParam.substr(0,6) == "NOT(IN") {
var pos = myParam.indexOf("]") + 2;
var len = myParam.length();
var end = len - pos - 2;
whereClause = " country NOT IN (" + myParam.substr(pos, end) + ")";
whereClause = whereClause.replace(/"/g, "'");
}
return whereClause;
}
</pre>

yoovrajkhullar

<blockquote class="ipsBlockquote" data-author="pricher" data-cid="144380" data-time="1466534593">
<div>
<p>Hi,</p>
<p> </p>
<p>Here's the complete code for operators IN, Not IN, Equal To and Not Equal To. Again, the original query has a placeholder where clause "AND 'dummy' = 'dummy'" that is replaced in the script with the appropriate operator:</p>
<pre class="_prettyXprint _lang-">
var myQuery = this.queryText
var myParam = reportContext.getParameterValue("pCountry");
var whereClause = "";

// Test of IN, Equal to , Not IN and Not Equal to conditions. Other conditions will need to be added
if (myParam.substr(0,2) == "IN") {
var pos = myParam.indexOf("]") + 2;
var len = myParam.length();
var end = len - pos - 1;
whereClause = " country IN (" + myParam.substr(pos, end) + ")";
whereClause = whereClause.replace(/"/g, "'");
}
else if(myParam.indexOf("=") > 0) {
var pos = myParam.indexOf("=") + 2;
var len = myParam.length();
var end = len - pos - 1;
whereClause = " country = " + myParam.substr(pos, end);
whereClause = whereClause.replace(/"/g, "'");

}
else if(myParam.indexOf("<>") > 0) {
var pos = myParam.indexOf("<>") + 3;
var len = myParam.length();
var end = len - pos - 1;
whereClause = " country != " + myParam.substr(pos, end);
whereClause = whereClause.replace(/"/g, "'");

}
else if(myParam.substr(0,6) == "NOT(IN") {
var pos = myParam.indexOf("]") + 2;
var len = myParam.length();
var end = len - pos - 2;
whereClause = " country NOT IN (" + myParam.substr(pos, end) + ")";
whereClause = whereClause.replace(/"/g, "'");
}

// If a where clause was created, replace the dummy where clause.
if (whereClause != "") {
myQuery = myQuery.replace("'dummy' = 'dummy'", whereClause)
this.queryText = myQuery
}
</pre>
<p>Hope this helps,</p>
<p> </p>
<p>P.</p>
</div>
</blockquote>
<p>Hi P</p>
<p>Your have given if statements for IN, NOT IN, = and <></p>
<p>What will be the if statement for "No Condition"</p>
<p>Thank you P in advance.</p>
<p>Regards</p>
<p>Raj </p>

pricher

<p>Hi,</p>
<p> </p>
<p>If "No Condition", function prepareWhereClause will return an empty string and the query will not be modified, hence all records from the query are returned.</p>
<p> </p>
<p>P.</p>

yoovrajkhullar

<blockquote class="ipsBlockquote" data-author="pricher" data-cid="144380" data-time="1466534593">
<div>
<p>Hi,</p>
<p> </p>
<p>Here's the complete code for operators IN, Not IN, Equal To and Not Equal To. Again, the original query has a placeholder where clause "AND 'dummy' = 'dummy'" that is replaced in the script with the appropriate operator:</p>
<pre class="_prettyXprint _lang-">
var myQuery = this.queryText
var myParam = reportContext.getParameterValue("pCountry");
var whereClause = "";

// Test of IN, Equal to , Not IN and Not Equal to conditions. Other conditions will need to be added
if (myParam.substr(0,2) == "IN") {
var pos = myParam.indexOf("]") + 2;
var len = myParam.length();
var end = len - pos - 1;
whereClause = " country IN (" + myParam.substr(pos, end) + ")";
whereClause = whereClause.replace(/"/g, "'");
}
else if(myParam.indexOf("=") > 0) {
var pos = myParam.indexOf("=") + 2;
var len = myParam.length();
var end = len - pos - 1;
whereClause = " country = " + myParam.substr(pos, end);
whereClause = whereClause.replace(/"/g, "'");

}
else if(myParam.indexOf("<>") > 0) {
var pos = myParam.indexOf("<>") + 3;
var len = myParam.length();
var end = len - pos - 1;
whereClause = " country != " + myParam.substr(pos, end);
whereClause = whereClause.replace(/"/g, "'");

}
else if(myParam.substr(0,6) == "NOT(IN") {
var pos = myParam.indexOf("]") + 2;
var len = myParam.length();
var end = len - pos - 2;
whereClause = " country NOT IN (" + myParam.substr(pos, end) + ")";
whereClause = whereClause.replace(/"/g, "'");
}

// If a where clause was created, replace the dummy where clause.
if (whereClause != "") {
myQuery = myQuery.replace("'dummy' = 'dummy'", whereClause)
this.queryText = myQuery
}
</pre>
<p>Hope this helps,</p>
<p> </p>
<p>P.</p>
</div>
</blockquote>
<p>Hi Pierce </p>
<p>In this Example you have done for <span style="color:rgb(136,0,0);">IN, Equal to , Not IN and Not Equal to conditions . i.e.</span></p>
<p><span style="color:rgb(136,0,0);">// Test of IN, Equal to , Not IN and Not Equal to conditions. Other conditions will need to be added</span></p>
<p><span style="color:rgb(136,0,0);">Can you do us all a favor and add Other conditions too . I mean conditions of : " </span><span style="color:rgb(31,73,125);">LIKEâ€, â€œNOT LIKEâ€, â€œIS NULLâ€, â€œNOT IS NULLâ€ operations.</span></p>
<p><span style="color:rgb(31,73,125);">Thanks in advance, It will complete this article.</span></p>
<p>Highly appreciated. You are an Angel.</p>
<p><span style="color:rgb(31,73,125);">Thanks</span></p>