Discussions
Categories
Choose a Product
THRUST SERVICES
CORE APPS
CE PRODUCTS
...
Quick Links
POPULAR
HELPFUL TIPS
Groups
My Links
FOR SIGNED IN MEMBERS:
Back to website
Home
Designing Analytics Reports
Adding data set parameters programmaticallty
Migrateduser
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.
Find more posts tagged with
Comments
There are no comments yet