Loop a dataSet multiple times

Hi All,
 
I am using BIRT RCP 4.3.1 for some report automation.
 
Regarding a chart report i get data from ORACLE DB, where the query used has a where clause something as:
 
WHERE bus_date between <startDate> and <endDate>
 
startDate and endDate are report parameters passes by the user.
 
However due to the design of the table, the `BETWEEN` clause is taking lot of time and frequently timing out.
 
Instead when we are querying like :
 
WHERE bus_date = <Date>, this is far quicker.
 
Hence i was wondering as how to run this oracle query multiple times in a loop from startDate to endDate and store the result which i can eventually use it in a chart display.
 
How can we achieve this?
 
I have been working with BIRT for some time now, however never tried out the scripting option in it.
 
Can i have a brief about programming in BIRT or some info to get started?

Find more posts tagged with

Comments

Clement Wong

Ideally, you might want to work with your DBA to tune that DB to get the BETWEEN working faster. Otherwise, one suggestion would be to have your DBA create you a stored procedure that would return you the result sets with a UNION of the separate WHERE bus_date clauses.
 
However, if you don't have those options, you would be able to programmatically via the BIRT Data Engine API to run queries in the beforeFactory to populate an array you can pass to a scripted data set. Or you can use the code in the open event of a scripted data set.
 
The DevShare located @ <a data-ipb='nomediaparse' href='http://developer.actuate.com/community/forum/index.php?/files/file/822-data-engine-api-to-check-data-set-values/'>http://developer.actuate.com/community/forum/index.php?/files/file/822-data-engine-api-to-check-data-set-values/</a> is a very good starting point.
 
Please note that instead of this line:
<pre class="_prettyXprint">
odaDataSet.setQueryText( dset.getQueryText() );</pre>
You can update the query via the following, for example:
<pre class="_prettyXprint">
odaDataSet.setQueryText( "SELECT CustomerID, myValue FROM CustomersTable WHERE StateName = 'CA'" );
</pre>
Also, note for those OS and commercial BIRT readers, there is a difference in this line queryDefinition.setAutoBinding(true);
 
OS BIRT:
<pre class="_prettyXprint">
queryDefinition = new QueryDefinition();
queryDefinition.setDataSetName(dataSet.name);
queryDefinition.setAutoBinding(true);</pre>
Commercial BIRT (comment out):
<pre class="_prettyXprint">
queryDefinition = new QueryDefinition(true);
queryDefinition.setDataSetName(dataSet.name);
//queryDefinition.setAutoBinding(true);</pre>