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

kwoitke

<p>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)</p>
<p> </p>
<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

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

kwoitke

<p>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?</p>

pricher

<p>Hi,</p>
<p> </p>
<p>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.</p>
<p> </p>
<p>Hope this helps,</p>
<p> </p>
<p>P.</p>

New_Bee

<blockquote class="ipsBlockquote" data-author="pricher" data-cid="130095" data-time="1407743391">
<div>
<p>Hi,</p>
<p> </p>
<p>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.</p>
<p> </p>
<p>Hope this helps,</p>
<p> </p>
<p>P.</p>
</div>
</blockquote>
<p>Could you please share an example on this.<br><br>
I'm looking to execute a dataset  during generation and use its output as the input parameter of the second dataset's query.</p>

pricher

<p>Hi,</p>
<p> </p>
<p>The sample report design provided by Clement in this thread is a good example.</p>
<p> </p>
<p>P.</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.