Multiple optional parameter.

Good morning!
 
I have a report with multiple parameter and I am using
<pre class="_prettyXprint">
AND (HR_APPOINTMENT.C_DEPARTMENT IN (****) OR HR_APPOINTMENT.C_SUBMISSION1 IN (****)
OR HR_APPOINTMENT.C_SUBMISSION2 IN (****))</pre>
in my DataSet and
<pre class="_prettyXprint">
this.queryText = this.queryText.replace("****", params["STR_C_DEPART"]);</pre>
in the BeforeOpen script.
 
I need this parameter to be optional at the same time. How can I script it??? Usually I am using something like 
<pre class="_prettyXprint">
var where="";
if ( params["DATE_BEGIN"].value != null) {
if (where=="") {
where = "DOC.DOC_DATE >= '" + params["DATE_BEGIN"].value + "'";
}
else {where = where + " AND DOC.DOC_DATE >= '" + params["DATE_BEGIN"].value + "'"}
}</pre>
with "1=1" in the DataSet.
But I am not sure how can I integrate multiple â€œxxxâ€ in this condition?

Find more posts tagged with

Comments

alecsia

I have figured this out:
<div>
<pre class="_prettyXprint">
if(params["STR_C_DEPART"].value == null){
this.queryText = this.queryText.replace("****", "0");
} else{
this.queryText = this.queryText.replace("****", params["STR_C_DEPART"]);
}</pre>
</div>

Matthew L.

Glad you were able to figure it out.
Here is the solution I was working on:
 
Change your query to the following:
<pre class="_prettyXprint _lang-sql">
AND (1=1)
</pre>
Then in your Data Set's beforeOpen statement, use the following code:
<pre class="_prettyXprint _lang-js">
var whereClause = "";
if(params["STR_C_DEPART"].value != null && params["STR_C_DEPART"].value != ""){
whereClause += "HR_APPOINTMENT.C_DEPARTMENT IN ('" + params["STR_C_DEPART"].value +"')";
}

if(params["STR_C_SUB1"].value != null && params["STR_C_SUB1"].value != ""){
if(whereClause != ""){whereClause+=" OR ";}
whereClause += "HR_APPOINTMENT.C_SUBMISSION1 IN ('" + params["STR_C_SUB1"].value +"')";
}

if(params["STR_C_SUB2"].value != null && params["STR_C_SUB2"].value != ""){
if(whereClause != ""){whereClause+=" OR ";}
whereClause += "HR_APPOINTMENT.C_SUBMISSION2 IN ('" + params["STR_C_SUB2"].value +"')";
}

if(whereClause != ""){
this.queryText = this.queryText.replaceAll('1=1',whereClause)
}
</pre>

alecsia

Thank you a lot! I will save this anyway!