dynamically select data set

wwilliams

How do you dynamically select a data set to use with one table? I am using a scripted data source and I have two similar queries and would like to selectively attach\detach a data set based on a parameter.

I was just wondering which is the best way, as my first thought was to have two tables and two data sets, hide the table and skip the open and fetch methods of one or the other.
(I am converting an Actuate report that uses IF controls for selecting the proper output and query\datastream based on a parameter)

AlexAD

I'm facing a similar issue, it would be great to know if there is a way to do so.

wwilliams

<blockquote class='ipsBlockquote' data-author="'wwilliams'" data-cid="100615" data-time="1337262343" data-date="17 May 2012 - 06:45 AM"><p>
How do you dynamically select a data set to use with one table? I am using a scripted data source and I have two similar queries and would like to selectively attach\detach a data set based on a parameter.<br />
<br />
I was just wondering which is the best way, as my first thought was to have two tables and two data sets, hide the table and skip the open and fetch methods of one or the other.<br />
(I am converting an Actuate report that uses IF controls for selecting the proper output and query\datastream based on a parameter)<br /></p></blockquote>

wwilliams

What I ended up doing was creating a data set for a "main" table which returns one row (table has to have a data set) and then two additional data sets and tables which I placed within the main table. In the open and fetch methods of the two data sets I put conditional statements based a a report parameter. Then in the "sub" tables I use the visibility property to hide the appropriate table section based on that parameter.

This is similar in the logic with the Actuate report and it works.

A@vi

<blockquote class='ipsBlockquote' data-author="'wwilliams'" data-cid="100615" data-time="1337262343" data-date="17 May 2012 - 06:45 AM"><p>
How do you dynamically select a data set to use with one table? I am using a scripted data source and I have two similar queries and would like to selectively attach\detach a data set based on a parameter.<br />
<br />
I was just wondering which is the best way, as my first thought was to have two tables and two data sets, hide the table and skip the open and fetch methods of one or the other.<br />
(I am converting an Actuate report that uses IF controls for selecting the proper output and query\datastream based on a parameter)<br /></p></blockquote>
<br />
<br />
There are options to do so...<br />
When you create a table, it asks you for number of rows & columns you want to create. & also it asks you about the dataset you want to select to. So you can use n number of datasets & tables which you can link up to.<br />
& there are options too to hide a column or table in Properties editor of the rptdesign.<br />
Correct me if I am wrong.

Tubal

<blockquote class='ipsBlockquote' data-author="'AlexAD'" data-cid="100666" data-time="1337352146" data-date="18 May 2012 - 07:42 AM"><p>
I'm facing a similar issue, it would be great to know if there is a way to do so.<br /></p></blockquote>
<br />
If the table structures are identical, you can modify your query in the dataset's beforeOpen event script.<br />
<br />
<pre class='_prettyXprint _lang-auto _linenums:0'>if(myVariable==x){
this.queryText = myNewQuery
} else {
this.queryText = myOtherNewQuery
}</pre>
<br />
If your table structures are different, it would probably be easiest to create two tables, and hide/drop the one you don't want to use. <br />
<br />
If your query times are insignificant, just set the visibility flag on the tables.<br />
<br />
If your queries are long and you don't want them to run, it's better to drop the table your query is bound to in the beforeFactory event of your report, so it doesn't execute your query:<br />
<br />
<pre class='_prettyXprint _lang-auto _linenums:0'>if(myVariable==x) {
var firstTable = reportContext.getDesignHandle().findElement("myFirstTable");
firstTable.drop();
} else {
var secondTable = reportContext.getDesignHandle().findElement("mySecondTable");
secondTable.drop();
}</pre>

wwilliams

<blockquote class='ipsBlockquote' data-author="'Tubal'" data-cid="101171" data-time="1337700697" data-date="22 May 2012 - 08:31 AM"><p>
If the table structures are identical, you can modify your query in the dataset's beforeOpen event script.<br />
<br />
<pre class='_prettyXprint _lang-auto _linenums:0'>if(myVariable==x){
this.queryText = myNewQuery
} else {
this.queryText = myOtherNewQuery
}</pre>
<br />
If your table structures are different, it would probably be easiest to create two tables, and hide/drop the one you don't want to use. <br />
<br />
If your query times are insignificant, just set the visibility flag on the tables.<br />
<br />
If your queries are long and you don't want them to run, it's better to drop the table your query is bound to in the beforeFactory event of your report, so it doesn't execute your query:<br />
<br />
<pre class='_prettyXprint _lang-auto _linenums:0'>if(myVariable==x) {
var firstTable = reportContext.getDesignHandle().findElement("myFirstTable");
firstTable.drop();
} else {
var secondTable = reportContext.getDesignHandle().findElement("mySecondTable");
secondTable.drop();
}</pre></p></blockquote>

wwilliams

Thanks,<br />
I ended up following your advice<br />
<pre class='_prettyXprint _lang-auto _linenums:0'>
if(params["site"] == "mySite"){
reportContext.getReportRunnable().designHandle.getDesignHandle().findElement("other").drop();

} else {
reportContext.getReportRunnable().designHandle.getDesignHandle().findElement("main").drop();

}
</pre>