Best practice for creating temporary tables before execution

Hi,
 
I use Birt since a while (10 years...), mainly for personal tools. Big thanks to Actuate for that wonderful piece of software.
 
I'd like to know if it's possible to create a database table according to submitted birt parameter. I'd like to improve performances and readability of reports.
 
For instance with a parameter on year, a temporary table could be :
 
'create table year2014 as select customer, count(distinct product) nb_products from purchases where year =? group by customer'
 
and datasets may use it :
dataset 1 : 'select * from year2014 where nb_products =5'
 
Any advice ?
 
Thank you in advance,
 
Ali

Find more posts tagged with

Comments

micajblock

Why do you want to create a temporary table? In BIRT a data set will only be executed once no matter how many times it is used? Can you detail the use case?

bolderiz

I use several datasets (oracle queries) in my birt report. 
Each dataset corresponds to a different Oracle query (with analytic functions mainly sum over and lead/lags ) applied on a same sub-select that depends on the submittted parameters. 
 
My aim is to pre-compute the sub-select in a temp table, to gain in perfo and readability <strike>lisibility</strike>.

micajblock

My question still remains. Why not one data set and put the analytic functions in the report? That being said although I have not tried it, but should work, why not create a stored procedure with the create statement and return 1 dummy row. The in BIRT use the SP as a data set. As long as there is a table (which you can set the display to none) first that uses this new data set and place it at the beginning of the report, it will be executed first.

bolderiz

Thanks for the idea, i'll try. 
 
 
<blockquote class="ipsBlockquote" data-author="mblock" data-cid="146710" data-time="1485891872">
<div>
My question still remains. Why not one data set and put the analytic functions in the report? 
</div>
</blockquote>
 
In my case it's a specific usage and I don't have enough time to figure out how to implement in birt what I can easily do via sql.