Break one parameter into three pieces for query?

I need to create a BIRT report with just one parameter. for example..
 
111 HEB PRODUCE
112 HEB FROZEN
113 HEB SEASONAL
 
This will be a list box I will create in the Report Parameters.
 
the values will be the numbers
 
so the parameter entered by the user will be 111, or, 112, or 113
 
Then I want to break the parameter entered by the user in 3 piece, like 1, 1, 3
 
and then use these three pieces in my query
 
WHERE a.customerId = ? (1)
AND a.locationId = ? (1)
AND a.dockId = ? (3)
 
Or any other solution to use only one parameter but check three fields in the query?
 
Is this possible?

Find more posts tagged with

Comments

Matthew L.

You can break up the value in the parameter by its characters using JavaScript and assign values to your query in the beforeOpen statement.
 
Modify your query as so:
<pre class="_prettyXprint _lang-sql">
WHERE a.customerId = %v1%
AND a.locationId = %v2%
AND a.dockId = %v3%
</pre>
Then in the beforeOpen statement of your Data Set, use the following to replace the %v% values with a character from the parameter value:
<div>
<pre class="_prettyXprint _lang-js">
this.queryText = this.queryText.replaceAll('%v1%',params["MyParameter"].value[0]); //First character
this.queryText = this.queryText.replaceAll('%v2%',params["MyParameter"].value[1]); //Second character
this.queryText = this.queryText.replaceAll('%v3%',params["MyParameter"].value[2]); //Third character
</pre>
</div>
<div>See attached example</div>

Eduardo Serrano

Genius! Thank you very much Matthew L.
 
 
<blockquote class="ipsBlockquote" data-author="Matthew L." data-cid="145504" data-time="1476107479">
<div>
 
You can break up the value in the parameter by its characters using JavaScript and assign values to your query in the beforeOpen statement.
 
Modify your query as so:
</div>
</blockquote>