Adding data set parameters programmaticallty

jinowolski

I have some reports with data set queries varying for different report parameters. The simplest example could be:<br />
<br />
<pre class='_prettyXprint _lang-auto _linenums:0'>select foo from bar</pre>
<pre class='_prettyXprint _lang-auto _linenums:0'>select foo from bar where baz = ?</pre>
<br />
Of course real examples aren't that simple and I can't predict where parameters may be needed (or at least there is too many combinations to practically do something static). <br />
Currently I deal with it by building queryText with values pasted in plain text, like in the following Data Set beforeOpen script:<br />
<br />
<pre class='_prettyXprint _lang-auto _linenums:0'>
this.queryText = "select foo from bar";
if (params['baz'].value)
this.queryText += " where baz = '" + params['baz'].value + "'";
</pre>
<br />
The best would be adding parameters dynamically, so we take advantages of PreparedStatement parameter binding, like database can cache plan for repeatable queries, query is less vulnerable to sql injection... <br />
Can I add Data Set parameters during report execution, so I could write something like this:<br />
<br />
<pre class='_prettyXprint _lang-auto _linenums:0'>
this.queryText = "select foo from bar";
if (params['baz'].value)
this.queryText += " where baz = " + addDataSetParameter(this, params['baz'].value);
</pre>
resulting in "where baz = ?" in the query and parameter bind to params['baz'] value?<br />
<br />
I found way to affect DS parameters in the report beforeFactory script:<br />
<pre class='_prettyXprint _lang-auto _linenums:0'>
var ds_example = reportContext.getDesignHandle().findDataSet("Example");
var params = ds_example.getPropertyHandle('parameters');
params.removeItem(0);
</pre>
but I didn't succeed in trying to add parameter.

mwilliams

One thing you could do is to add all the dataSet parameters in your dataSet, in the order that they would be in, in your most complex query, then, remove them from the parameter list handle if you're not going to use them. Then, you could build your query string with the ?s, in your beforeOpen script, and the standard order of first ? will get first dataSet parameter in line will hold true. In the same way, you could add structure elements to the list property, but it'd probably be easier to add them in the designer and remove the unneeded ones. Maybe I'm misunderstanding the goal. Let me know.