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

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>

Find more posts tagged with

Comments

Clement Wong

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

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

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

<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

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

RK SHARMA

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

Lenchik Ageeva

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

Mitch Somes

Use result of one dataset for another dataset.rptdesign

Attaching the example here.

MAX072012

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.