Using the output of one dataset as parameters to another dataset in a chain

kwoitke · August 6, 2014

In BIRT, how can we use the result set of one dataset as parameters to another dataset? I'm not refering to dynamic report parameters (where the user selects between results from a query). I want to chain together datasets from different data sources. How can I use the output of a MySQL dataset to parameterize a query for a PostgreSQL dataset without resorting to creating a BIRT joint dataset? (see below)
 
<div>For example:</div>
<div> </div>
<div>Suppose that I have a PostgreSQL table with a very large number of rows but I know that I will only be interested in a few rows per report, depending on the outcome of a MySQL query. How can I use the output of my MySQL dataset to parameterize a query for a PostgreSQL dataset without resorting to creating a BIRT joint dataset (which would involve loading many unnecessary rows from the PostgreSQL datasource)? What if I want to use the result from my second dataset in a query for a third dataset (and so on)?</div>

Clement Wong · August 6, 2014

Hi,
 
The attached design illustrates one possible solution where you can store the result of your first query in the onFetch event of the first Data Set in a report variable.
 
Then, in the beforeOpen event of the second Data Set, you can use that report variable and update the SQL accordingly.
 
Please note that the example only has one Data Source, but this technique can be applied to multiple (and different) Data Sources.

kwoitke · August 10, 2014

The report design that you sent worked. I noticed that the example rptdesign uses a report variable to share information between datasets. This page (<a data-ipb='nomediaparse' href='http://www.eclipse.org/birt/documentation/integrating/scripting.php'>http://www.eclipse.org/birt/documentation/integrating/scripting.php</a>) seems to advocate storing global variables in the reportContext. I've gotten both strategies to work but is there a reason to prefer one way over another? When should I use a report variable over reportContext.setPersistentGlobalVariable()? When would the reportContext.setPersistentGlobalVariable() be preferable?

pricher · August 11, 2014

Hi,
 
Typically, use a Persistent Global Variable when you need to share values between the Generation and Presentation phases. In your case, the two data sets are executed during the Generation phase; therefore, using a Report Variable is fine. However, if you wanted to store a value obtained during the Generation phase, say the average sales price of all your orders, and use it during the Presentation phase to create a dynamic marker line in a chart, you would need to store it in the Report Context. The rule of thumb is that if you script something in an onRender method, you will need to use a Report Context variable; otherwise, you are good to go with a Report Variable.
 
Hope this helps,
 
P.

New_Bee · March 21, 2016

<blockquote class="ipsBlockquote" data-author="pricher" data-cid="130095" data-time="1407743391">
<div>
Hi,
 
Typically, use a Persistent Global Variable when you need to share values between the Generation and Presentation phases. In your case, the two data sets are executed during the Generation phase; therefore, using a Report Variable is fine. However, if you wanted to store a value obtained during the Generation phase, say the average sales price of all your orders, and use it during the Presentation phase to create a dynamic marker line in a chart, you would need to store it in the Report Context. The rule of thumb is that if you script something in an onRender method, you will need to use a Report Context variable; otherwise, you are good to go with a Report Variable.
 
Hope this helps,
 
P.
</div>
</blockquote>
Could you please share an example on this. 
I'm looking to execute a dataset during generation and use its output as the input parameter of the second dataset's query.

pricher · March 21, 2016

Hi,
 
The sample report design provided by Clement in this thread is a good example.
 
P.

RK SHARMA · December 12, 2017

I could not find any attachment rpt design file provided by Clement .

Lenchik Ageeva · April 2, 2018

Clement Wong, good day)
please, can you provide your example one more time?

Mitch Somes · January 17, 2023

Use result of one dataset for another dataset.rptdesign

Attaching the example here.

MAX072012 · August 1

I'm trying to achieve the same and followed the attached report however it does not work. I have 2 datasets where the output of the first data set should be passed as a parameter to the second dataset. Hence in the first data set in OnFetch I passed the var data = row["column"]. In the second data set in beforeOpen I have mentioned like this.queryText = this.queryText.replace("whereid",vars["data"]); There is no error however I don't see the whereid being replace. Can someone help me with this.

Using the output of one dataset as parameters to another dataset in a chain

Comments

Categories