Difficult SQL-queries in the report

Hi, Everybody!

I am starting use BIRT. Actually, I have a lot of experience of the working with other reports generators.
So, I ask only about "the way", which I need to use to get my reports.

The situation is:
The report has a lot of parameters.
They influence on query texts of datasets. I build sql script for these datasets dynamically.
Each next dataset uses result of previous dataset
through temporary tables over the single transaction.
Finally, a few datasets provide data for their charts based on these temporary tables.
Sometimes, number of sequential datasets can be above 10.

The question: How I should make the similar scheme BIRT?
Thanks.

Find more posts tagged with

Comments

mwilliams

What kind of data is passed from one dataSet to the query for the next? One way you can pass values from one dataSet to the next would be by embedding elements within ones bound to the previous dataSet.

If you can describe a scenario using some sample data, that might help me understand better.

Nikola18

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="94719" data-time="1327804609" data-date="28 January 2012 - 07:36 PM">
If you can describe a scenario using some sample data, that might help me understand better. </blockquote>
Thanks for Your attention. 
 
The first query preparing a based items of the key table. 
The second query makes the calculation of the exponential values for each object identification. To do its I use analytical functions of the oracle. 
Final query aggregates data for histograms and other charts. 
 
Actually, I made the stored procedure, which makes update to the database by parametrized sql-script, 
and results empty simple table. I do not use this result, but next execution of this stored procedure updates the data. 
 
The problem is next execute of this stored procedure does not see the results of the previous. 
Maybe, it is another transaction or undefined sequence of execution datasets?

Hans_vd

Hi Nikola, 
 
Why do you execute the stored procedure twice? 
Why don't you just do the update as a last step in the stored procedure? 
 
About the reason why the data is not there when the stored procedure is executed the second time, this has probably something to do with the persistence of your temporary table. A part of <a class='bbc_url' href='http://enterprisesmartapps.wordpress.com/2011/02/24/oracle-stored-procedure-data-set-in-birt/'>this blog</a> on Oracle stored procedures in BIRT is about using temporary tables. 
 
Hope this helps 
Hans

Nikola18

<blockquote class='ipsBlockquote' data-author="'Hans_vd'" data-cid="94738" data-time="1327912378" data-date="30 January 2012 - 01:32 AM">
About the reason why the data is not there when the stored procedure is executed the second time, this has probably something to do with the persistence of your temporary table. A part of <a class='bbc_url' href='http://enterprisesmartapps.wordpress.com/2011/02/24/oracle-stored-procedure-data-set-in-birt/'>this blog</a> on Oracle stored procedures in BIRT is about using temporary tables. </blockquote>
 
Thanks, Guys! 
It helped. 
And I found how to prepare data into temporary table, which other queries could use. 
Also I built the correct dataset execution order and my report is done! 
 
Finally, I have a simple question: "How can I buy the Actuate Products for our Company in Russia?" 
Thanks, again.

mwilliams

Have you downloaded and tried out the Actuate products? If not, you can find 45 day trials on the .com side of the site. As for purchasing, you should be able to get a hold of a sales rep by choosing the "contact us" link at the top of the page. If you don't, shoot me an email and I'll hook you up with someone!